In Excel, to calculate sum and average and find the maximum and minimum value based on the selection of the drop-down list, I will use data validation, the SUM, AVERAGE, MAX and MIN functions and the INDIRECT function that returns a valid cell reference from the text string.
The following dataset contains the number of products by four companies:
Now I want the program to calculate the sum and average and find the maximum and minimum value instantly when the user makes a selection of the company from the drop-down list, like shown below:
To do so, I use data validation using the shortcut [Alt A V Enter] in sequence to create a drop-down list in B11, listing company names, like shown below:
Then, I create named range for each company, like so:
After this, I apply the SUM, AVERAGE, MAX and MIN functions and nest INDIRECT function in each one of them. Since I created named range for each company, INDIRECT will extract cell references from the selected dropdown value and will hand over those references to SUM, AVERAGE, MAX and MIN.
Note that each formula returns the REF! error as B11 is empty at this time. To replace this error with an empty string (""), I nest each formula in the IFERROR function, like shown below:
Your dropdown SUM, AVERAGE, MAX and MIN calculator is ready! I hope this post helped you.
