# sum lookup formula

#### SSPoulin23

##### New Member
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

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
maybe..

Excel 2013
ABCDEF
1Summary
2AmountSID
37010020
42635555
55012569
6
7TravelSIDAmt1Amt2Amt3Amt4
8100205101520
9100201234
10355555678
11125695101520
12100201234

<tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
A3=SUMPRODUCT((\$B\$8:\$B\$12=B3)*(\$C\$8:\$F\$12))

<tbody>
</tbody>

<tbody>
</tbody>

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

you're very welcome

Replies
7
Views
591
Replies
1
Views
239
Replies
1
Views
276
Replies
2
Views
272
Replies
6
Views
279

1,196,328
Messages
6,014,674
Members
441,835
Latest member
rthomas268

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back