Counting text in two different columns

R

Robert

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
 
T

T. Valko

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))
 
J

Jacob Skaria

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

If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count Vaules in a range that appear in another range 5
Index/match across multiple columns? 16
Sum of rows in different columns 3
Macro to add Multiple rows in same sheet 1
retrieve & count text 5
Match and return functions 14
how do i sum if in two different cell references - Excel 1
Index backwards 7

Top