SUMPRODUCT doesn't work with wild cards.

Try it like this:

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

--
Biff
Microsoft Excel MVP


"Jamie" wrote in message
...
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,




"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"aet999" wrote:

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.