In Excel, to sum range based on the selection of drop-down list, I use data validation, the SUM and the INDIRECT function that returns a valid cell reference from the text string.
The following dataset contains five fee collections:
![Dataset Dataset](https://kunalexceltip.com/wp-content/uploads/2024/03/Dataset.png)
Now I want to get the total sum of the fee collection range selected by the user from the drop-down list, like shown below:
![Calculating range SUM using INDIRECT Calculating range SUM using INDIRECT](https://kunalexceltip.com/wp-content/uploads/2024/03/Calculating-range-SUM-using-INDIRECT-1.gif)
To do so, I create named range for each fee collection, like so:
![Creating named ranges Creating named ranges](https://kunalexceltip.com/wp-content/uploads/2024/03/Creating-named-ranges-1.gif)
After this, I use data validation using the shortcut [Alt A V Enter] in sequence to create a drop-down list in D11, listing fee abbreviations in column B, like so:
![Creating drop-down list using data validation Creating drop-down list using data validation](https://kunalexceltip.com/wp-content/uploads/2024/03/Creating-drop-down-list-using-data-validation.gif)
To create text string of each dropdown item, I apply the VLOOKUP formula in D16 that will search for the selected dropdown value in the first column of the table array B4:F8:
=IFNA(VLOOKUP(D11,B4:F8,1,FALSE),"")
![Applying VLOOKUP formula Applying VLOOKUP formula](https://kunalexceltip.com/wp-content/uploads/2024/03/Applying-VLOOKUP-formula.gif)
Next, I nest the INDIRECT formula inside the SUM function in D18. If the user selects TF from the list, INDIRECT will return cell references from the named range TF. SUM will then add up those cell references, like shown below:
![Nesting the INDIRECT formula inside the SUM function Nesting the INDIRECT formula inside the SUM function](https://kunalexceltip.com/wp-content/uploads/2024/03/Nesting-the-INDIRECT-formula-inside-the-SUM-function-1.gif)
Your range dropdown SUM using INDIRECT calculator is ready! I hope this post helped you.