SUM and IF In the same Cell

jcoates

Is it possible to have a cell include both a SUM and IF statement to do the following.  (I do not want use a separate status column.)  I want to be able to add up several columns (Actual Admin Labour + Actual Engineering Labour + Actual Mechanical Labour) into a Total Actual Labour column and then add an IF statement that says if the total in the cell is between 0-80% of the Budget Labour column make the cell green, if between 80-100% make the cell yellow, if between >100% make the cell red.

Any help would be greatly appreciated.

 

Mike Wilday

The best way would be to use a combination of a helper column with the calculation to determine the percentage and then use conditional formatting set up the coloring of the cell. 

In a new column calculate the percentage you're looking for. 

Then click on the conditional formatting icon in the toolbar and set up a rule that if the percentage column is less than or equal to .8 --- format it red. 

Then set one up that looks at whether the percentage column Greater than .8 and less than 100 - format the column to be yellow. 

Then create a final rule that budget column is greater than or equal to 1 

Percentages in Smartsheet are calculated with decimals as 10ths of a whole.