Count text cells based on two criteria
 

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.

Count text cells based on two criteria
 

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

Count text cells based on two criteria
 

It worked. Thanks. You rock!

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

Count text cells based on two criteria
 

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.

Count text cells based on two criteria
 

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.

Count text cells based on two criteria
 

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.

Count text cells based on two criteria
 

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.

Count text cells based on two criteria
 

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.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.

Count text cells based on two criteria
 

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 -

Count text cells based on two criteria
 

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 -