SUM and IF In the same Cell

jcoates ✭✭
edited 12/09/19 in Formulas and Functions

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.


2019-07-02 15_48_37-New Sheet - Smartsheet.com_.png


  • Mike Wilday
    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. 

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!