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.

• D says:

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

• Hi,

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

• D says:

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)

• 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.

• 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.