Excel Drop Down / Formula Help

Closed
darthchester Posts 2 Registration date Tuesday October 16, 2012 Status Member Last seen October 17, 2012 - Oct 16, 2012 at 03:32 AM
darthchester Posts 2 Registration date Tuesday October 16, 2012 Status Member Last seen October 17, 2012 - Oct 17, 2012 at 04:20 AM
Hey Everyone,

I am looking for some help with this formula.

I have a spread sheet that has 11 categories, all are sorted into a dropdown menu in Column 'D'. In Column 'C' I have numbers which are associated with those 11 categories. My objective is to be able to enter my numbers in 'C', select which category in 'D', and all of the matching numbers associated with a specific category is added up and given to me in a seperate column.

For example:

I have three entries into cat.1 (50, 25, and 10), and two entries into cat.3 (10 and 5).

In Column 'F' it tells me the added total of all cat.1 (85)
In Column 'G' it tells me the added total of all cat.2 (0)
In Column 'H' it tells me the added total of all cat.3 (15)... etc.

Is this possible with a formula, or do I need to enter a code into Visual Basic?
Any help would be greatly appriciated.

1 response

Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Oct 16, 2012 at 06:45 AM
Hi darthchester,

You can use SUMIFS() worksheet function to accomplish this task. Fill column E with the list of your categories (viz., E2 - Category 01, E3 - Category 02... E12 - Category 11). Put the following formula in F2 and drag till F12:

=SUMIFS(\$C\$1:\$C\$1048575,\$D\$1:\$D\$1048575,E2)

You will get the desired result (i.e., sum of values in column C against the categories in column D). No need to do any VBA programming.

Please reply if you have any further questions.

darthchester Posts 2 Registration date Tuesday October 16, 2012 Status Member Last seen October 17, 2012
Oct 17, 2012 at 04:20 AM
Zohaib R,

Thank you so much, kind friend. This is in fact the exact thing I was looking for. My spreadsheet is complete and working wonderfully!

Thanks again.
DC