In Excel, to calculate the sum of numeric values across multiple columns based on the selection of the dropdown list, I will use data validation to create dropdown list and use the SUM and VLOOKUP functions together.
VLOOKUP looks for the value in the vertical data and returns a value from the given column number.
The following dataset contains the number of cold drinks consumed:
Now I want the program to instantly calculate the sum of the cold drink selected from the dropdown list, like shown below:
To accomplish the task, I perform the following steps:
I use data validation using the shortcut [Alt A V Enter] in sequence to create a dropdown list in H5, listing all the drink names in column A, like so:
After creating the dropdown, I construct my SUM and VLOOKUP formula in H6, like shown below:
To replace the N/A error with an empty string "", I nest my entire formula in the IFNA function.
Your dropdown VLOOKUP and SUM formula is ready! I hope this post helped you.
