software

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.

=Sumproduct(--(A1:A100=quot;bettyquot;),--(B1:B100=quot;robertquot;))

--
Regards,
Tom Ogilvyquot;aet999quot; wrote:

gt; I have a spreadsheet with many columns. I would like to count how many
gt; occurances there are of two criteria matching. Example, If column A contains
gt; names of girls (amy, betty, susie, karen, betty) and column B contains names
gt; of boys (michael, robert, andrew, james, joseph). how many times does column
gt; A = betty AND column b = robert on the same row? In this example, the answer
gt; would be one.

It worked. Thanks. You rock!

quot;Tom Ogilvyquot; wrote:

gt; =Sumproduct(--(A1:A100=quot;bettyquot;),--(B1:B100=quot;robertquot;))
gt;
gt; --
gt; Regards,
gt; Tom Ogilvy
gt;
gt;
gt; quot;aet999quot; wrote:
gt;
gt; gt; I have a spreadsheet with many columns. I would like to count how many
gt; gt; occurances there are of two criteria matching. Example, If column A contains
gt; gt; names of girls (amy, betty, susie, karen, betty) and column B contains names
gt; gt; of boys (michael, robert, andrew, james, joseph). how many times does column
gt; gt; A = betty AND column b = robert on the same row? In this example, the answer
gt; gt; would be one.

guys,
this works well, but If there's a wild card in the formula it doesn't work.

For example:
gt; =Sumproduct(--(A1:A100=quot;bettyquot;),--(B1:B100=quot;*robert*quot;))

thanks,

quot;Tom Ogilvyquot; wrote:

gt; =Sumproduct(--(A1:A100=quot;bettyquot;),--(B1:B100=quot;robertquot;))
gt;
gt; --
gt; Regards,
gt; Tom Ogilvy
gt;
gt;
gt; quot;aet999quot; wrote:
gt;
gt; gt; I have a spreadsheet with many columns. I would like to count how many
gt; gt; occurances there are of two criteria matching. Example, If column A contains
gt; gt; names of girls (amy, betty, susie, karen, betty) and column B contains names
gt; gt; of boys (michael, robert, andrew, james, joseph). how many times does column
gt; gt; A = betty AND column b = robert on the same row? In this example, the answer
gt; gt; would be one.

SUMPRODUCT doesn't work with wild cards.

Try it like this:

=SUMPRODUCT(--(A1:A100=quot;bettyquot;),--(ISNUMBER(SEARCH(quot;robertquot;,B1:B100))))

--
Biff
Microsoft Excel MVPquot;Jamiequot; gt; wrote in message
...
gt; guys,
gt; this works well, but If there's a wild card in the formula it doesn't
gt; work.
gt;
gt; For example:
gt;gt; =Sumproduct(--(A1:A100=quot;bettyquot;),--(B1:B100=quot;*robert*quot;))
gt;
gt; thanks,
gt;
gt;
gt;
gt;
gt; quot;Tom Ogilvyquot; wrote:
gt;
gt;gt; =Sumproduct(--(A1:A100=quot;bettyquot;),--(B1:B100=quot;robertquot;))
gt;gt;
gt;gt; --
gt;gt; Regards,
gt;gt; Tom Ogilvy
gt;gt;
gt;gt;
gt;gt; quot;aet999quot; wrote:
gt;gt;
gt;gt; gt; I have a spreadsheet with many columns. I would like to count how many
gt;gt; gt; occurances there are of two criteria matching. Example, If column A
gt;gt; gt; contains
gt;gt; gt; names of girls (amy, betty, susie, karen, betty) and column B contains
gt;gt; gt; names
gt;gt; gt; of boys (michael, robert, andrew, james, joseph). how many times does
gt;gt; gt; column
gt;gt; gt; A = betty AND column b = robert on the same row? In this example, the
gt;gt; gt; answer
gt;gt; gt; would be one.
IT WORKED!!!!
THANK YOU!quot;T. Valkoquot; wrote:

gt; SUMPRODUCT doesn't work with wild cards.
gt;
gt; Try it like this:
gt;
gt; =SUMPRODUCT(--(A1:A100=quot;bettyquot;),--(ISNUMBER(SEARCH(quot;robertquot;,B1:B100))))
gt;
gt; --
gt; Biff
gt; Microsoft Excel MVP
gt;
gt;
gt; quot;Jamiequot; gt; wrote in message
gt; ...
gt; gt; guys,
gt; gt; this works well, but If there's a wild card in the formula it doesn't
gt; gt; work.
gt; gt;
gt; gt; For example:
gt; gt;gt; =Sumproduct(--(A1:A100=quot;bettyquot;),--(B1:B100=quot;*robert*quot;))
gt; gt;
gt; gt; thanks,
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Tom Ogilvyquot; wrote:
gt; gt;
gt; gt;gt; =Sumproduct(--(A1:A100=quot;bettyquot;),--(B1:B100=quot;robertquot;))
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; Regards,
gt; gt;gt; Tom Ogilvy
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;aet999quot; wrote:
gt; gt;gt;
gt; gt;gt; gt; I have a spreadsheet with many columns. I would like to count how many
gt; gt;gt; gt; occurances there are of two criteria matching. Example, If column A
gt; gt;gt; gt; contains
gt; gt;gt; gt; names of girls (amy, betty, susie, karen, betty) and column B contains
gt; gt;gt; gt; names
gt; gt;gt; gt; of boys (michael, robert, andrew, james, joseph). how many times does
gt; gt;gt; gt; column
gt; gt;gt; gt; A = betty AND column b = robert on the same row? In this example, the
gt; gt;gt; gt; answer
gt; gt;gt; gt; would be one.
gt;
gt;
gt;

Be advised that one of the pitfalls of this type of string matching is the
possibility of quot;false positivesquot;.

--(ISNUMBER(SEARCH(quot;robertquot;,B1:B100)))

That will find:

robert
roberta
roberts
robertson

Basically *anything* that contains the substring quot;robertquot;.

--
Biff
Microsoft Excel MVPquot;Jamiequot; gt; wrote in message
...
gt; IT WORKED!!!!
gt; THANK YOU!
gt;
gt;
gt; quot;T. Valkoquot; wrote:
gt;
gt;gt; SUMPRODUCT doesn't work with wild cards.
gt;gt;
gt;gt; Try it like this:
gt;gt;
gt;gt; =SUMPRODUCT(--(A1:A100=quot;bettyquot;),--(ISNUMBER(SEARCH(quot;robertquot;,B1:B100))))
gt;gt;
gt;gt; --
gt;gt; Biff
gt;gt; Microsoft Excel MVP
gt;gt;
gt;gt;
gt;gt; quot;Jamiequot; gt; wrote in message
gt;gt; ...
gt;gt; gt; guys,
gt;gt; gt; this works well, but If there's a wild card in the formula it doesn't
gt;gt; gt; work.
gt;gt; gt;
gt;gt; gt; For example:
gt;gt; gt;gt; =Sumproduct(--(A1:A100=quot;bettyquot;),--(B1:B100=quot;*robert*quot;))
gt;gt; gt;
gt;gt; gt; thanks,
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Tom Ogilvyquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; =Sumproduct(--(A1:A100=quot;bettyquot;),--(B1:B100=quot;robertquot;))
gt;gt; gt;gt;
gt;gt; gt;gt; --
gt;gt; gt;gt; Regards,
gt;gt; gt;gt; Tom Ogilvy
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; quot;aet999quot; wrote:
gt;gt; gt;gt;
gt;gt; gt;gt; gt; I have a spreadsheet with many columns. I would like to count how
gt;gt; gt;gt; gt; many
gt;gt; gt;gt; gt; occurances there are of two criteria matching. Example, If column A
gt;gt; gt;gt; gt; contains
gt;gt; gt;gt; gt; names of girls (amy, betty, susie, karen, betty) and column B
gt;gt; gt;gt; gt; contains
gt;gt; gt;gt; gt; names
gt;gt; gt;gt; gt; of boys (michael, robert, andrew, james, joseph). how many times
gt;gt; gt;gt; gt; does
gt;gt; gt;gt; gt; column
gt;gt; gt;gt; gt; A = betty AND column b = robert on the same row? In this example,
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; answer
gt;gt; gt;gt; gt; would be one.
gt;gt;
gt;gt;
gt;gt;
Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

quot;T. Valkoquot; gt; wrote in message
...
gt; Be advised that one of the pitfalls of this type of string matching is the
gt; possibility of quot;false positivesquot;.
gt;
gt; --(ISNUMBER(SEARCH(quot;robertquot;,B1:B100)))
gt;
gt; That will find:
gt;
gt; robert
gt; roberta
gt; roberts
gt; robertson
gt;
gt; Basically *anything* that contains the substring quot;robertquot;.
gt;
gt; --
gt; Biff
gt; Microsoft Excel MVP
gt;
gt;
gt; quot;Jamiequot; gt; wrote in message
gt; ...
gt;gt; IT WORKED!!!!
gt;gt; THANK YOU!
gt;gt;
gt;gt;
gt;gt; quot;T. Valkoquot; wrote:
gt;gt;
gt;gt;gt; SUMPRODUCT doesn't work with wild cards.
gt;gt;gt;
gt;gt;gt; Try it like this:
gt;gt;gt;
gt;gt;gt; =SUMPRODUCT(--(A1:A100=quot;bettyquot;),--(ISNUMBER(SEARCH(quot;robertquot;,B1:B100))))
gt;gt;gt;
gt;gt;gt; --
gt;gt;gt; Biff
gt;gt;gt; Microsoft Excel MVP
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; quot;Jamiequot; gt; wrote in message
gt;gt;gt; ...
gt;gt;gt; gt; guys,
gt;gt;gt; gt; this works well, but If there's a wild card in the formula it doesn't
gt;gt;gt; gt; work.
gt;gt;gt; gt;
gt;gt;gt; gt; For example:
gt;gt;gt; gt;gt; =Sumproduct(--(A1:A100=quot;bettyquot;),--(B1:B100=quot;*robert*quot;))
gt;gt;gt; gt;
gt;gt;gt; gt; thanks,
gt;gt;gt; gt;
gt;gt;gt; gt;
gt;gt;gt; gt;
gt;gt;gt; gt;
gt;gt;gt; gt; quot;Tom Ogilvyquot; wrote:
gt;gt;gt; gt;
gt;gt;gt; gt;gt; =Sumproduct(--(A1:A100=quot;bettyquot;),--(B1:B100=quot;robertquot;))
gt;gt;gt; gt;gt;
gt;gt;gt; gt;gt; --
gt;gt;gt; gt;gt; Regards,
gt;gt;gt; gt;gt; Tom Ogilvy
gt;gt;gt; gt;gt;
gt;gt;gt; gt;gt;
gt;gt;gt; gt;gt; quot;aet999quot; wrote:
gt;gt;gt; gt;gt;
gt;gt;gt; gt;gt; gt; I have a spreadsheet with many columns. I would like to count how
gt;gt;gt; gt;gt; gt; many
gt;gt;gt; gt;gt; gt; occurances there are of two criteria matching. Example, If column
gt;gt;gt; gt;gt; gt; A
gt;gt;gt; gt;gt; gt; contains
gt;gt;gt; gt;gt; gt; names of girls (amy, betty, susie, karen, betty) and column B
gt;gt;gt; gt;gt; gt; contains
gt;gt;gt; gt;gt; gt; names
gt;gt;gt; gt;gt; gt; of boys (michael, robert, andrew, james, joseph). how many times
gt;gt;gt; gt;gt; gt; does
gt;gt;gt; gt;gt; gt; column
gt;gt;gt; gt;gt; gt; A = betty AND column b = robert on the same row? In this example,
gt;gt;gt; gt;gt; gt; the
gt;gt;gt; gt;gt; gt; answer
gt;gt;gt; gt;gt; gt; would be one.
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;
gt;

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

Pete

On May 2, 12:06*am, quot;Ashish Mathurquot; gt; wrote:
gt; Hi,
gt;
gt; IN which case, one can use quot; robert quot; in the search function
gt;
gt; --
gt; Regards,
gt;
gt; Ashish Mathur
gt; Microsoft Excel MVPwww.ashishmathur.com
gt;
gt; quot;T. Valkoquot; gt; wrote in message
gt;
gt; ...
gt;
gt;
gt;
gt; gt; Be advised that one of the pitfalls of this type of string matching is the
gt; gt; possibility of quot;false positivesquot;.
gt;
gt; gt; --(ISNUMBER(SEARCH(quot;robertquot;,B1:B100)))
gt;
gt; gt; That will find:
gt;
gt; gt; robert
gt; gt; roberta
gt; gt; roberts
gt; gt; robertson
gt;
gt; gt; Basically *anything* that contains the substring quot;robertquot;.
gt;
gt; gt; --
gt; gt; Biff
gt; gt; Microsoft Excel MVP
gt;
gt; gt; quot;Jamiequot; gt; wrote in message
gt; ...
gt; gt;gt; IT WORKED!!!!
gt; gt;gt; THANK YOU!
gt;
gt; gt;gt; quot;T. Valkoquot; wrote:
gt;
gt; gt;gt;gt; SUMPRODUCT doesn't work with wild cards.
gt;
gt; gt;gt;gt; Try it like this:
gt;
gt; gt;gt;gt; =SUMPRODUCT(--(A1:A100=quot;bettyquot;),--(ISNUMBER(SEARCH(quot;robertquot;,B1:B100))))
gt;
gt; gt;gt;gt; --
gt; gt;gt;gt; Biff
gt; gt;gt;gt; Microsoft Excel MVP
gt;
gt; gt;gt;gt; quot;Jamiequot; gt; wrote in message
gt; ...
gt; gt;gt;gt; gt; guys,
gt; gt;gt;gt; gt; this works well, but If there's a wild card in the formula it doesn't
gt; gt;gt;gt; gt; work.
gt;
gt; gt;gt;gt; gt; For example:
gt; gt;gt;gt; gt;gt; =Sumproduct(--(A1:A100=quot;bettyquot;),--(B1:B100=quot;*robert*quot;))
gt;
gt; gt;gt;gt; gt; thanks,
gt;
gt; gt;gt;gt; gt; quot;Tom Ogilvyquot; wrote:
gt;
gt; gt;gt;gt; gt;gt; =Sumproduct(--(A1:A100=quot;bettyquot;),--(B1:B100=quot;robertquot;))
gt;
gt; gt;gt;gt; gt;gt; --
gt; gt;gt;gt; gt;gt; Regards,
gt; gt;gt;gt; gt;gt; Tom Ogilvy
gt;
gt; gt;gt;gt; gt;gt; quot;aet999quot; wrote:
gt;
gt; gt;gt;gt; gt;gt; gt; I have a spreadsheet with many columns. *I would like to count how
gt; gt;gt;gt; gt;gt; gt; many
gt; gt;gt;gt; gt;gt; gt; occurances there are of two criteria matching. *Example, If column
gt; gt;gt;gt; gt;gt; gt; A
gt; gt;gt;gt; gt;gt; gt; contains
gt; gt;gt;gt; gt;gt; gt; names of girls (amy, betty, susie, karen, betty) and column B
gt; gt;gt;gt; gt;gt; gt; contains
gt; gt;gt;gt; gt;gt; gt; names
gt; gt;gt;gt; gt;gt; gt; of boys (michael, robert, andrew, james, joseph). *how many times
gt; gt;gt;gt; gt;gt; gt; does
gt; gt;gt;gt; gt;gt; gt; column
gt; gt;gt;gt; gt;gt; gt; A = betty AND column b = robert on the same row? *In this example,
gt; gt;gt;gt; gt;gt; gt; the
gt; gt;gt;gt; gt;gt; gt; answer
gt; gt;gt;gt; gt;gt; gt; would be one.- Hide quoted text -
gt;
gt; - Show quoted text -You can concatenate spaces:

--(ISNUMBER(SEARCH(quot; robert quot;,quot; quot;amp;B1:B100amp;quot; quot;)))

But this still isn't bulletproof:

robert?
robert, jim
,robert,--
Biff
Microsoft Excel MVPquot;Pete_UKquot; gt; wrote in message
...
But there might not be a space before or after robert in the data.

Pete

On May 2, 12:06 am, quot;Ashish Mathurquot; gt; wrote:
gt; Hi,
gt;
gt; IN which case, one can use quot; robert quot; in the search function
gt;
gt; --
gt; Regards,
gt;
gt; Ashish Mathur
gt; Microsoft Excel MVPwww.ashishmathur.com
gt;
gt; quot;T. Valkoquot; gt; wrote in message
gt;
gt; ...
gt;
gt;
gt;
gt; gt; Be advised that one of the pitfalls of this type of string matching is
gt; gt; the
gt; gt; possibility of quot;false positivesquot;.
gt;
gt; gt; --(ISNUMBER(SEARCH(quot;robertquot;,B1:B100)))
gt;
gt; gt; That will find:
gt;
gt; gt; robert
gt; gt; roberta
gt; gt; roberts
gt; gt; robertson
gt;
gt; gt; Basically *anything* that contains the substring quot;robertquot;.
gt;
gt; gt; --
gt; gt; Biff
gt; gt; Microsoft Excel MVP
gt;
gt; gt; quot;Jamiequot; gt; wrote in message
gt; ...
gt; gt;gt; IT WORKED!!!!
gt; gt;gt; THANK YOU!
gt;
gt; gt;gt; quot;T. Valkoquot; wrote:
gt;
gt; gt;gt;gt; SUMPRODUCT doesn't work with wild cards.
gt;
gt; gt;gt;gt; Try it like this:
gt;
gt; gt;gt;gt; =SUMPRODUCT(--(A1:A100=quot;bettyquot;),--(ISNUMBER(SEARCH(quot;robertquot;,B1:B100))))
gt;
gt; gt;gt;gt; --
gt; gt;gt;gt; Biff
gt; gt;gt;gt; Microsoft Excel MVP
gt;
gt; gt;gt;gt; quot;Jamiequot; gt; wrote in message
gt; ...
gt; gt;gt;gt; gt; guys,
gt; gt;gt;gt; gt; this works well, but If there's a wild card in the formula it
gt; gt;gt;gt; gt; doesn't
gt; gt;gt;gt; gt; work.
gt;
gt; gt;gt;gt; gt; For example:
gt; gt;gt;gt; gt;gt; =Sumproduct(--(A1:A100=quot;bettyquot;),--(B1:B100=quot;*robert*quot;))
gt;
gt; gt;gt;gt; gt; thanks,
gt;
gt; gt;gt;gt; gt; quot;Tom Ogilvyquot; wrote:
gt;
gt; gt;gt;gt; gt;gt; =Sumproduct(--(A1:A100=quot;bettyquot;),--(B1:B100=quot;robertquot;))
gt;
gt; gt;gt;gt; gt;gt; --
gt; gt;gt;gt; gt;gt; Regards,
gt; gt;gt;gt; gt;gt; Tom Ogilvy
gt;
gt; gt;gt;gt; gt;gt; quot;aet999quot; wrote:
gt;
gt; gt;gt;gt; gt;gt; gt; I have a spreadsheet with many columns. I would like to count how
gt; gt;gt;gt; gt;gt; gt; many
gt; gt;gt;gt; gt;gt; gt; occurances there are of two criteria matching. Example, If column
gt; gt;gt;gt; gt;gt; gt; A
gt; gt;gt;gt; gt;gt; gt; contains
gt; gt;gt;gt; gt;gt; gt; names of girls (amy, betty, susie, karen, betty) and column B
gt; gt;gt;gt; gt;gt; gt; contains
gt; gt;gt;gt; gt;gt; gt; names
gt; gt;gt;gt; gt;gt; gt; of boys (michael, robert, andrew, james, joseph). how many times
gt; gt;gt;gt; gt;gt; gt; does
gt; gt;gt;gt; gt;gt; gt; column
gt; gt;gt;gt; gt;gt; gt; A = betty AND column b = robert on the same row? In this example,
gt; gt;gt;gt; gt;gt; gt; the
gt; gt;gt;gt; gt;gt; gt; answer
gt; gt;gt;gt; gt;gt; gt; would be one.- Hide quoted text -
gt;
gt; - Show quoted text -