CODEWITHSUNDEEP
excelformula
James
James

Reputation: 13

Excel countif where criteria is on separate columns

         A          B        C
1        Fruit   0.34     Pear
2    Vegetable   0.62  Lettuce
3        Fruit   0.80    Apple
4        Fruit   0.86   Banana

Given the table above, I need to know the cost of all items above by type (fruits vs vegetables). In the above example, I'm looking for a formula that gives me Fruits=2.0 and Vegetables=0.62 and can apply to any number of rows.

Any help would be appreciated!

Upvotes: 1

Views: 7774

Answers (4)

Jaime
Jaime

Reputation: 6814

This may not be the correct forum for that question, but the solution is using "Array Formulas" - Ctrl+Shift+Enter on something like =sum(if(A1:A4="Fruit",B1:B4,0))

Upvotes: 0

David
David

Reputation: 2038

A slight generalisation on palswim's answer will work for any number of rows:

=SUMIF(A:A,"Fruit",B:B)
=SUMIF(A:A,"Vegetable",B:B)

or a formula you could put in column D:

=SUMIF(A:A,A1,B:B)

Upvotes: 0

Lunatik
Lunatik

Reputation: 3948

SUMPRODUCT will do what you need, here is an example:

=SUMPRODUCT(--(A1:A4="Fruit"),--(B1:B4))

This link has more on the syntax and how can be used as Excel's help file is lacking when it comes to SUMPRODUCT.

Upvotes: 0

palswim
palswim

Reputation: 12140

This might be a SuperUser question, but if I understand correctly, this should do the trick:

=SUMIF( A1:A99, "=Fruit", B1:B99 )

and

=SUMIF( A1:A99, "=Vegetable", B1:B99 )

Upvotes: 2

Related Questions