Counting text in two different columns
 

I'm trying to create a formula to count text in two different columns for
instance:

Samle work sheet:

A B
Jon apples
Jo apples
Jon grapes
Jon apples
Jo apples
Jon oranges
Eric apples
Jo apples

I want a formula that would show me how many "apples" Jon has. Please help!
Thanks

Counting text in two different columns
 

Hi,

=SUMPRODUCT((A1:A19="Jon")*(B1:B19="Apples"))

Mike

"Robert" wrote:

I'm trying to create a formula to count text in two different columns for
instance:

Samle work sheet:

A B
Jon apples
Jo apples
Jon grapes
Jon apples
Jo apples
Jon oranges
Eric apples
Jo apples

I want a formula that would show me how many "apples" Jon has. Please help!
Thanks

Counting text in two different columns
 

Try this:

=SUMPRODUCT(--(A1:A10="Jon"),--(B1:B10="apples"))

Better to use cells to hold the criteria:

D1 = Jon
E1 = apples

=SUMPRODUCT(--(A1:A10=D1),--(B1:B10=E1))

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
I'm trying to create a formula to count text in two different columns for
instance:

Samle work sheet:

A B
Jon apples
Jo apples
Jon grapes
Jon apples
Jo apples
Jon oranges
Eric apples
Jo apples

I want a formula that would show me how many "apples" Jon has. Please
help!
Thanks

Counting text in two different columns
 

=SUMPRODUCT(--(A1:A100="Jon")*--(B1:B100="apples"))

If this post helps click Yes
---------------
Jacob Skaria


"Robert" wrote:

I'm trying to create a formula to count text in two different columns for
instance:

Samle work sheet:

A B
Jon apples
Jo apples
Jon grapes
Jon apples
Jo apples
Jon oranges
Eric apples
Jo apples

I want a formula that would show me how many "apples" Jon has. Please help!
Thanks