sum lookup formula

[SSPoulin23]

hi there,

I have a spreadsheet for calculating travel reimbursements on a monthly basis. On each line, the user enters the amounts (up to four predetermined categories) and then chooses the SID (where it will be paid from).

I have a summary line in which the user can select an option (an SID) from a drop down box. I would like to have a formula in the amount section of the summary that adds all the amounts for each SID chosen once the user selects the SID option from the drop down box. (i have a hidden column that totals the four amounts if necessary). I've tried a variety of sum(lookup( options and can't seem to get it right. I don't know if it's partly due to the fact that the SID is from a dropdown list on a different page? anyway, here's my data (it's not laid out this neatly on the sheet - there are other columns in between the amounts columns and the SID column is located in the middle ...

Summary
Amount	SID
(formula)	10020
(formula)	35555
(formula)	12569
Travel	SID	Amt1	Amt2	Amt3	Amt4
	10020	5.00	10.00	15.00	20.00
	10020	1.00	2.00	3.00	4.00
	35555	5.00	6.00	7.00	8.00
	12569	5.00	10.00	15.00	20.00
	10020	1.00	2.00	3.00	4.00

<tbody>
</tbody>

so 10020 should be $70.00
35555 should be $26.00
12569 should be $50.00
















thanks in advance!!!

 

[Weazel]

maybe..


Excel 2013

	A	B	C	D	E	F
1	Summary
2	Amount	SID
3	70	10020
4	26	35555
5	50	12569
6
7	Travel	SID	Amt1	Amt2	Amt3	Amt4
8		10020	5	10	15	20
9		10020	1	2	3	4
10		35555	5	6	7	8
11		12569	5	10	15	20
12		10020	1	2	3	4

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula A3 =SUMPRODUCT(($B$8:$B$12=B3)*($C$8:$F$12)) <tbody> </tbody>

<tbody>
</tbody>

 

[SSPoulin23]

is it really that simple?? yes, yes indeed it is. I'm glad I wasted ALL DAY yesterday on this (cos I couldn't find my password to post to here sooner lol). thank you sooo much Weazel!!!

 

[Weazel]

you're very welcome