You can concatenate spaces:

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

But this still isn't bulletproof:

robert?
robert, jim
,robert,


--
Biff
Microsoft Excel MVP


"Pete_UK" wrote in message
...
But there might not be a space before or after robert in the data.

Pete

On May 2, 12:06 am, "Ashish Mathur" wrote:
Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

"T. Valko" wrote in message

...



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

--
Biff
Microsoft Excel MVP

"Jamie" wrote in message
...
IT WORKED!!!!
THANK YOU!

"T. Valko" wrote:

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.- Hide quoted text -

- Show quoted text -