Forum Discussion

Friulano's avatar
Friulano
Copper Contributor
Apr 26, 2023
Solved

How to perform a calculation based on drop down menu selections

  Columns H - J are drop down lists I would like Column K to sum based on the selection of the drop down lists
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Apr 26, 2023

    Friulano 

    In K2:

    =SUM(--LEFT(H2:J2,SEARCH(" ",H2:J2)-1))

    If you do not have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.

Friulano's avatar
Friulano
Copper Contributor
Apr 26, 2023
Hans you saved me a tonne of time. Thank you so much. The formula works perfect to calculate when drop down items are selected but shows #VALUE! before anything has been selected in the drop down menus. How can I have it display nothing until something is selected? and add cell colour? Like ranges 1-2 are green, 4-6 are yellow and 7-9 are red? Should I created new posts for each of those additional questions?
HansVogelaar's avatar
HansVogelaar
MVP
Apr 26, 2023

Friulano 

If you want to see a result if 1 or 2 cells have been filled, change the formula to

=SUM(--IFERROR(LEFT(H2:J2,SEARCH(" ",H2:J2)-1),0))

If you only want a result if all three cells have been filled, use

=IFERROR(SUM(--LEFT(H2:J2,SEARCH(" ",H2:J2)-1)),"")

 

Select the cell or cells in column K with the formula.

Set the fill color to red. This will be the default.

 

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Leave the first drop down set to 'Cell Value'.

Select 'less than or equal to' from the second drop down.

Enter 6 in the box next to it.

Click Format...

Activate the Fill tab.

Select yellow.

Click OK, then click OK again.

 

Repeat these steps, but with 3 as value and green.

 

Finally, repeat them again, with 0 as value and 'No Color'.

Resources