Count entries in a range which exclude certain user defined words

{ 5 Comments }

By Ashish Mathur · · , · Leave a comment

Assume the following sentences in range B2:B6

B2 – This is an Apple Pie
B3 – An apple a day keeps a doctor away
B4 – These Pears, apples and mangoes are sweet
B5 – In this season, prices of mangoes have increased
B6 – This is a glass of Guava juice

In range B9:B10, type Mango and Apple.  The objective is to count the sentences in range B2:B6 which exclude the words mentioned in range B9:B10.

The array formula (Ctrl+Shift+Enter) for getting the count of exclusions would be:

=SUMPRODUCT(1*(MMULT(TRANSPOSE(1*(ISTEXT($B$9:$B$10))),1*(ISERROR(SEARCH($B$9:$B$10,TRANSPOSE($B2:$B$6)))))=COUNTA($B$9:$B$10)))

The result should be 1.

The array formula (Ctrl+Shift+Enter) for getting the count of inclusions (sentences which include these words), would be:

=COUNTA($B$2:$B$6)-SUMPRODUCT(1*(MMULT(TRANSPOSE(1*(ISTEXT($B$9:$B$10))),1*(ISERROR(SEARCH($B$9:$B$10,TRANSPOSE($B2:$B$6)))))=COUNTA($B$9:$B$10)))

The result should be 4.

One can also solve the problem with the PowerPivot tool.  You may refer to my PowerPivot solution in this workbook.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • Ashish,

    OK, this looks good. But a few questions.

    1) If I want it to exclude the word “walk,” I also want it to exclude “walks” and “walking.” Will this do the trick?

    2) I need to put conditions on my exclusions. For example, only those kids under the age of 12 who were wheezing. How can I do that?

    3) I also need to put conditions on my inclusions. I need to separate kids by age, how can I put conditions onto the inclusion formula as well?

    Thanks!
    D

    Reply

    • Hi,

      Please find herein my answers to your questions:

      1. Yes. As in my original post, you can see that Apple and Apples are both being excluded – even though the exclusion list has Apple only
      2. Please post sample data, mention your exclusion list entries and also share your expected result
      3. Please post sample data, mention your inclusion list entries and also share your expected result

      Reply

  • Example Data

    Child Age Activity
    Child A 5 walk, run
    Child B 12 walks
    Child C 7 walked
    Child D 16 runs
    Child E 5 runs
    Child F 14 run, swim
    Child G 10 swim
    Child H 4 bicycle
    Child I 6 walk, run

    Number of kids who walk and are > 6 y/o? (answer is 2)
    Number of kids who neither bike nor swim and are <= 6 y/o (answer is 3)

    Reply

    • Hi,

      Try this

      1. Suppose data is in range B2:D11. Headings are in B2:D2
      2. In range B17:B18, type bicycle and swim respectively
      3. In cell E16, enter this array formula (Ctrl+Shift=Enter) to count the number of kids who neither own a bike nor swim

      =SUMPRODUCT(1*(MMULT(TRANSPOSE(1*(ISTEXT($B$17:$B$18))),1*(ISERROR(SEARCH($B$17:$B$18,TRANSPOSE($D3:$D$11)))))=COUNTA($B$17:$B$18))*TRANSPOSE($C$3:$C$11<=6)) To count number of kids who walk and are >6, enter the following formula

      =SUMPRODUCT(ISNUMBER(SEARCH(B21,$D$3:$D$11))*($C$3:$C$11>6))

      Cell B21 has walk

      Hope this helps.

      Reply

    • Hi,

      You can also try this alternative

      1. Suppose data is in range B2:D11. Headings are in B2:D2
      2. Copy cell C2 and paste in cell B15. Cell B15 should how Age
      3. In cell C15, type Condition
      4. Copy cell D2 and paste in cell D15. Cell D15 should show Child
      5. In range F3:F4, enter Swim and Bicycle
      6. In cell B16, type <=6
      7. In cell C16, enter =SUMPRODUCT(1*ISNUMBER(SEARCH(F$3:F$4,D3)))=0
      8. In cell D16, enter =DCOUNTA($B$2:$D$11,D$15,$B$15:$C$16)

      cell D16 will give you the count of children who are <=6 years old who neither swim nor own a bicycle.

      Reply