ExcelBanter - count cells that have *text1* and don't have *text2*

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - count cells that have *text1* and don't have *text2* (https://www.excelbanter.com/excel-discussion-misc-queries/20756-count-cells-have-%2Atext1%2A-dont-have-%2Atext2%2A.html)

amntre

count cells that have *text1* and don't have *text2*

I have a column of text cells (each with different number of words),
And need to count those cells which meets the following conditions:
They have the text €œbox€ and dont have the text €œnot€ in them.

For example: €œWhite boxes not present €œ- should not be counted
€œBoxes cover€ - should be counted.

Thanks

Domenic

Try...

=SUMPRODUCT(--(ISNUMBER(SEARCH("box",A1:A10))),--(1-ISNUMBER(SEARCH("not"
,A1:A10))))

Hope this helps!

In article ,
amntre wrote:

I have a column of text cells (each with different number of words),
And need to count those cells which meets the following conditions:
They have the text €œbox€ and dont have the text €œnot€ in them.

For example: €œWhite boxes not present €œ- should not be counted
€œBoxes cover€ - should be counted.

Thanks

All times are GMT +1. The time now is 03:35 AM.