How to not duplicate countif text within cells?

Hi - I've been looking for this for a while and with no luck. Basically, I want to use a countif (or a sumproduct if necessary) because I have multiple criteria. And I want to count how many cells have at least one person's name in it (and if there's multiple names, I only want to count it once). The problem is that when there's more than one name in the cell, using my current countif statement will get duplicates.

A B
Yes This is John
Yes I'm Bob
Yes I'm not sure if I'm Bob or Charlie
No I'm neither Bob nor John


My current equation: =sum(countifs(A:A,"Yes",B:B,{"*John*","*Bob*","*Charlie"})

I am getting a result of 4 as it is counting B1, B2, and B3 twice, however, I only want the third row (where it says both Bob and Charlie) to be counted once.

Any thoughts?

I get an error. Not 100% sure why but I don't think you can have an OR operator within a countif. Thanks though

Originally Posted by oeldere

Try this (not tested).

Please Login or Register  to view this content.

Any ideas team? How can you count text but limit it to the number of cells (i.e. if text is written multiple times in the same cell, it's only counted once). Any help would be great!

Thanks Marcol - it's pretty close (see attached sheet). It's definitely only counting once per cell as cell B3, which contains all 3 names is only counted once.

However, there's a bit of a glitch in that every "Yes" in column A will be counted, regardless of what's in columnB. I'm very new to sumproduct so I'm not sure if that's a glitch in the equation or what...

Thank you!

Try this:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH({"John","Bob","Charlie"},B1:B6))),{1;1;1})>0),--(A1:A6="Yes"))

I had a quick trigger finger! Thanks Marcol - your corrected formula works. Now I just have to figure out why...appreciate your help!

Originally Posted by Marcol

You've missed my corrected formula in post #5

SUMPRODUCT() works with whole columns with post 2003 versions, but it's slow.
Best to keep the ranges to a minimum and remember they all must be the same size.

Note
Your first sample had the data in row 2 down, I built the formula based on that.