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:
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:
To do so, I create named range for each fee collection, like so:
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:
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),"")
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:
Your range dropdown SUM using INDIRECT calculator is ready! I hope this post helped you.
