Count text cells based on two criteria

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

 

[Guest]

=Sumproduct(--(A1:A100="betty"),--(B1:B100="robert"))

 

[Guest]

It worked. Thanks. You rock!

=Sumproduct(--(A1:A100="betty"),--(B1:B100="robert"))

 

[Jamie]

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*"))

thanks,

 

[T. Valko]

SUMPRODUCT doesn't work with wild cards.

Try it like this:

=SUMPRODUCT(--(A1:A100="betty"),--(ISNUMBER(SEARCH("robert",B1:B100))))

 

[Jamie]

IT WORKED!!!!
THANK YOU!

SUMPRODUCT doesn't work with wild cards.

Try it like this:

=SUMPRODUCT(--(A1:A100="betty"),--(ISNUMBER(SEARCH("robert",B1:B100))))

 

[T. Valko]

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

 

[Ashish Mathur]

Hi,

IN which case, one can use " robert " in the search function

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Pete_UK]

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

Pete

 

[T. Valko]

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