How to sum values based on selection of drop-down list in Excel?
Supposing you have a table contains two columns (Category and Amount), and you have created a data validation drop-down list which contains all the categories. When selecting a category from the drop-down list, you want to sum up all corresponding values in Amount column. As the below screenshot shown, if you select AA from the drop down, you will get the result 10+30+80=120. How to achieve it? Methods in this article can do you a favor.
Easily sum values based on drop down item in Excel:
The LOOKUP and Sum utility of Kutools for Excel can help you easily sum values based on drop down item without applying formula.
Download the full feature 30-day free trail of Kutools for Excel now!
1. Select a blank cell to output the result,
2. Enter the below formula into it, and then press the Enter key.
From now on, the sum will be adjusted automatically based on the selection of the drop-down list.
- 1. You can also apply the below formula to sum values based on selection of drop-down list.
- 2. In the formula, A2:A10 is the category range, D2 is the drop-down list cell, and B2:B10 is the value range you need to sum based on the drop-down list selection. Please change them as you need.
1. Click Kutools > Super LOOKUP > LOOKUP and Sum. See screeshot:
2. Then a LOOKUP and Sum dialog box pops up, you need to finish the below settings:
- 2.1) In the Lookup and Sum Type section, select Lookup and sum matched values(s) in row(s) option;
- 2.2) In the Select Range section, specify the Lookup Value, Output Range as well as the Data table range;
- 2.3) In the Options section, choose the Return the sum of all matched values option;
- 2.4) Click OK.
Then a formula is created automatically in the Output cell. From now on, the sum will be adjusted automatically based on the drop-down item.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 6 months agoThe Sum Values Based On Selection Of Drop-Down List With Formula worked perfectly for what I needed. Thanks so much for posting this on here. You saved me hours of headaches!
- To post as a guest, your comment is unpublished.· 1 years agoI am trying to use sumifs with set of conditions. There is a project column with sprint numbers and test cases, etc.. I created a drop down list of the project names above the cell with the formula and referred that in the Sumif condition. Now based on my dropdown selection, the total of test cases created are resulted. This works absolutely fine. Now, if I have to select the dropdown to select all projects, how do I do it. I tried creating a list item as "<>" but that is not working. Any suggestions?
- To post as a guest, your comment is unpublished.· 8 months agoTrying using the wild card "*" so the last or first item in your list is just the *. I am working through a similar problem and that has worked most of the time.
- To post as a guest, your comment is unpublished.· 1 years agoDid you find a way to make this work?
- To post as a guest, your comment is unpublished.· 1 years agoHi,
What do you mean of "to select the dropdown to select all projects"?
Would be nice if you could provide screenshot of what you are trying to do.
Thanks for your comment.
- To post as a guest, your comment is unpublished.· 2 years agoformula for 2 different categories
- To post as a guest, your comment is unpublished.· 2 years agohow to sum value all selected cell in sum total value ?