In Excel, to sum values based on the selection of drop-down list with the All option, I use data validation, the SUMIF function that sums up the numbers based on the criteria with the IF function.
The following dataset contains a list of 4 repeating chocolate brands in column B and their quantity in column C:
![Dataset Dataset](https://kunalexceltip.com/wp-content/uploads/2024/03/Dataset-2.png)
Now I want the program to sum the entire quantity when the user selects the All option from the drop-down list. Similarly, when the user selects a specific chocolate brand, it should sum the entire quantity of only that selected brand, like shown below:
![SUM with dropdown list with All option using SUMIF SUM with dropdown list with All option using SUMIF](https://kunalexceltip.com/wp-content/uploads/2024/03/SUM-with-dropdown-list-with-All-option-using-SUMIF.gif)
To accomplish the task, I use data validation using the shortcut [Alt A V Enter] in sequence to create a drop-down list in E2. In the list, I mention the All option including the unique chocolate brand names, like so:
![Creating dropdown list using data validation Creating dropdown list using data validation](https://kunalexceltip.com/wp-content/uploads/2024/03/Creating-dropdown-list-using-data-validation.gif)
In the adjacent cell F2, I enter the following SUMIF formula and nest it in the IF function that will sum up the values in column C based on the selection of the dropdown list, like shown below:
![Applying SUMIF formula and nesting it in the IF function Applying SUMIF formula and nesting it in the IF function](https://kunalexceltip.com/wp-content/uploads/2024/03/Applying-SUMIF-formula-and-nesting-it-in-the-IF-function.gif)
Your calculator is ready! I hope this post helped you.