Forum Discussion
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
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.
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.
- 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?
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'.
