Solved Return value for multiple lookup

Microsoft Ms sla excel lic/sa 2yr-1
August 12, 2009 at 18:34:39
Specs: Windows Vista
Hello,

I have a worksheet that have dates corresponding to $ amounts. I want to be able to match a date and sum up the amounts for that date (e.g. if cell A11 = 12Aug2009, add all $ that matches 12Aug2009. Here's my worksheet:

AB
1 Shipping
2 12-Aug-09 $10.00
3 13-Aug-09 $20.00
4 14-Aug-09 $30.00
5 12-Aug-09 $40.00
6 12-Aug-09 $50.00
7 13-Aug-09 $60.00
8 14-Aug09 $70.00
9
10
11 12-Aug-09 $280

My formula:
=SUM(INDEX($A$2:$B$8,SMALL(IF($A$2:$A$8=$A$11,0),ROW(2:2)),2,1))
result is $280, (correct answer if my formula is right is
12Aug $10 + 12Aug $40 + 12Aug $50 = $100)

problem (1) my formula adds all date matches, not those that meet the criteria (if A11 = 12aug2009)
problem (2) I actual worksheet have other columns in the same worksheet like the one below:

ABCDE
1 Shipping Supply Rm
2 12-Aug-09 $10.00 12-Aug-09 $10.00
3 13-Aug-09 $20.00 13-Aug-09 $20.00
4 14-Aug-09 $30.00 14-Aug-09 $30.00
5 12-Aug-09 $40.00 12-Aug-09 $40.00
6 12-Aug-09 $50.00 12-Aug-09 $50.00
7 13-Aug-09 $60.00 13-Aug-09 $60.00
8 12-Aug-09 $70.00 12-Aug-09 $70.00
9
10
11 12-Aug-09 $560

My formula:

=SUM(INDEX($A$2:$B$8,SMALL(IF($A$2:$A$8=$A$11,0),ROW(2:2)),2,1))+
SUM(INDEX($D$2:$E$8,SMALL(IF($D$2:$D$8=$A$11,0),ROW(2:2)),2,1))
result is 560, (correct answer if my formula is right is
SHIPPING: 12Aug $10 + 12Aug $40 + 12Aug $50 = $100 AND
SUPPLY RM: 12Aug $10 + 12Aug $40 + 12Aug $50 = $100
TOTAL $200.00

Thank you for your assitance.

Sincerely,
Lex


See More: Return value for multiple lookup

Report •


#1
August 12, 2009 at 19:55:00
✔ Best Answer
If all you want is a single column,
wouldn't something simpler work:

=SUMIF(A2:A8,A11,B2:B8)

MIKE

http://www.skeptic.com/


Report •

#2
August 12, 2009 at 20:11:14
Also, your math in the second example is off by $140.00, you failed to count row 8.

For a simple two column solution:

=SUMIF(A2:A8,A11,B2:B8)+SUMIF(C2:C8,A11,D2:D8)

MIKE

http://www.skeptic.com/


Report •

Related Solutions


Ask Question