Tags: DCOUNTA

Count entries in a range which exclude certain user defined words

{5 Comments}

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.