Excel countif where criteria is on separate columns

Question

         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!

Answer 1

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 )

Answer 2

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

Answer 3

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.

Answer 4

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)