SUM OF VLOOKUPs

[mkingrey]

I want a formula that works similar to VLOOKUP, but instead of returning one value I want a sum of all values in a column that match the VLOOKUP CRITERIA.

EXP:

A B
1 Red 4
2 Blue 5
3 Green 4
4 Green 3
5 Red 4
6 Green 5
7 Blue 7

I want a sum of all column B's that equal "Green" in column A.

Seems like there should be a simple solution I just don't know it right off.

It seems like I could use SUM, OFFSET, and IF function I just can't tie it together.

 

[texasalynn]

=sumproduct((B2:B100="Green")*(a2:a100))

or

=SUMIF(b2:b100,"green",a2:a100)

HTH

 

[just_jon]

I want a formula that works similar to VLOOKUP, but instead of returning one value I want a sum of all values in a column that match the VLOOKUP CRITERIA.

EXP:

A B
1 Red 4
2 Blue 5
3 Green 4
4 Green 3
5 Red 4
6 Green 5
7 Blue 7

I want a sum of all column B's that equal "Green" in column A.

Seems like there should be a simple solution I just don't know it right off.

It seems like I could use SUM, OFFSET, and IF function I just can't tie it together.

For single criteria summing, use SUMIF

=SUMIF(A1:A10,"Green",B1:B10)

or


=SUMIF(A1:A10,C1,B1:B10)

Where C1 holds your value such as Green

 

[mkingrey]

thanks for the help but I dont know if that is what I am looking for.

Column B has no "Green" values. I want to look at column A and for every instance of "Green" move to column B and sum those correspoding numbers.

COLUMN A__________________COLUMN B
GREEN_________________________15
WHITE_________________________10
BLUE___________________________7
GREEN_________________________ 20

HERE I WANT TO KNOW THAT THERE ARE A TOTAL of 35 GREEN.

 

[mkingrey]

OK the SUMIF works..thanks for the help!