Count text cells based on two criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet with many columns. I would like to count how many
occurances there are of two criteria matching. Example, If column A contains
names of girls (amy, betty, susie, karen, betty) and column B contains names
of boys (michael, robert, andrew, james, joseph). how many times does column
A = betty AND column b = robert on the same row? In this example, the answer
would be one.
 
guys,
this works well, but If there's a wild card in the formula it doesn't work.

For example:
=Sumproduct(--(A1:A100="betty"),--(B1:B100="*robert*"))
Click to expand...

thanks,
 
SUMPRODUCT doesn't work with wild cards.

Try it like this:

=SUMPRODUCT(--(A1:A100="betty"),--(ISNUMBER(SEARCH("robert",B1:B100))))
 
IT WORKED!!!!
THANK YOU!


T. Valko said:
SUMPRODUCT doesn't work with wild cards.

Try it like this:

=SUMPRODUCT(--(A1:A100="betty"),--(ISNUMBER(SEARCH("robert",B1:B100))))
Click to expand...
 
Be advised that one of the pitfalls of this type of string matching is the
possibility of "false positives".

--(ISNUMBER(SEARCH("robert",B1:B100)))

That will find:

robert
roberta
roberts
robertson

Basically *anything* that contains the substring "robert".
 
You can concatenate spaces:

--(ISNUMBER(SEARCH(" robert "," "&B1:B100&" ")))

But this still isn't bulletproof:

robert?
robert, jim
,robert,


--
Biff
Microsoft Excel MVP


But there might not be a space before or after robert in the data.

Pete
 
Back
Top