Trying to use a dropdown list as reference for a Sum function.

[Vmar002]

Hey guys,

So I've been looking all over trying to figure out how to do this but have had no luck. I am a complete novice when it comes to excel and I think this might be too tough a formula to figure out by myself.

I have a worksheet with a couple of dropdown menus. One of these is used in another cell, kind of like a summary. I want a cell in that summary table to add all the values pertaining to one of the dropdown menu options automatically. For example:

Column A has the dropdown list.
Column B has whatever amount.
Column C has the same dropdown list.
Column D is the one where, depending on the choice in Column C, it adds all the values in column B that have that belonged to that set in Column A.



Column A Column B Column C Column D

Phys Ther 30 Surgery 100
Consultation 15
Surgery 50
Phys Ther 30
Surgery 50
Phys Ther 30

 

[konew1]

Don't you just need sumif?
=SUMIF(A1:A6,C1,B1:B6)

 

[Vmar002]

I tried, but it gives me a #value message. I don't think the posts properly displays what the sheet looks like. The "Code" column and "Service" column are the ones with the dropdown menu. The "Code" one is part of the huge information in the sheet, the "service" one is part of the summary in the sheet. I want the total amount to change based on the service, by adding the chages belonging to each code. In this case I chose Consult as the service so total amount would be 30 for the 2 consults in the sheet.

Code	Charge	Service	Total Amount
Phys ther	30	Consult	30
Consult	15
Phys Ther	30
Surgery	50
Consult	15
Phys Ther	30

<tbody>
</tbody>

 

[Vmar002]

Ok, sooooo. I'm a total newb. That about sums it up. Thanks konew1. you had it basically right, the range is on the wrong side is all. So it's actually:


=SUMIF(A1:A6,B1:B6, C1)

 

[konew1]

The format of sumif is SUMIF(range, criteria, [sum_range]). The criteria in your example is in C1 (Consult), and the sum_range is in B