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:
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:
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:
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:
Your calculator is ready! I hope this post helped you.
