Count/Sum/Index/Match.........HELP

[TimE]

I am at a loss and need your (novice, intermediate, expert) assistance. This is probably a very simple solution, but I am not seeing it...yet. I tried using index/match and sum....but no luck.

A_________B___________C______D

Apple___RESULT=5______Plum 2
Grape___RESULT=8______Grape 3
Plum____RESULT=2______Apple 4
______________________Apple 1
______________________Grape 5

[swatsp0p]

Tim: I have read your post, but don't understand what you are trying to accomplish. Please give more details. Be as specific as possible, what is your data and what is your desired result?

Bruce

[TimE]

Sorry. Col B contains a Set criteria, Col C will have the result of finding (in Col D) all the Set criteria and summing the total in Col E that corresponds to the 'match' in Col D.


A1______B1______C1______D1______E1
A2______Apple____17______Plum____5
A3______Orange___6______Apple____4
A4______Grape____16_____Apple____9
A5______Plum_____8______Grape___2
________________________Orange__6
________________________Apple____4
________________________Plum_____1
________________________Plum_____2
________________________Grape____4
________________________Grape____6
________________________Grape____4

[swatsp0p]

OK, now we're getting somewhere. Use SUMIF to meet your needs.

In C1 enter this formula and copy down as needed. Of course adjust ranges to meet your needs.

=SUMIF($D$1:$D$11,B1,$E$1:$E$11)

this formula looks in D for the value in B and sums from all matching rows in E

Note the $ to fix the range for the lookup table. You could also names this ranges to simplify formula creation. e.g. the range in D could be named ITEMS and the range in E could be named VALUES

then: =SUMIF(ITEMS,B1,VALUES) does the same thing.

HTH

Bruce

[TimE]

Thank you for your fast response. That was exactly what I needed.