Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Creating a formula that relies on conditional formatting

Zhana Morris
edited 12/09/19 in Archived 2015 Posts

Hello all. This may be out there, but I am not really sure what search words to use, so haven't had much luck in seeing if anyone else has come up with a solution.


I am trying to create a formula that will calculate the sum of all cells in a column that meet a specific format, in this case, shading.  I do not want to have to go in and update the formula each time a cell changes to meet the formatting requirement.  Here is a visual that I hope will help explain what I am trying to do.  I'm guessing there might be some sort of imbedded formatting involving a variation of an IF statement, but I'm just not sure.




1 "formula"
I want B1 to add up all numbers in the column, but only once the shading has turned to green.

So currently B1 would report "5" (without quotes), and if I checked the box in A8, causing B8 to turn green, B1 would automatically change to "6"
Collumn B has conditional formatting to turn green when I check a box in column A


  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

    you can do this by changing the logic a little.


    you wouldn't have your formula rely on the shading, but rather it would reference the same cell that is driving the conditional formatting.


    If you publish your sheet and include a link, I could give more specific tips.

  • Thanks Brett. The sheet has some proprietary data in it so I can't publish it directly, but let me put together a mock version that has all of the relevant info. May not happen today, but figuring this out would be of great help so I will definitely take you up on the offer of suggestions asap.



  • Brad Jones
    Brad Jones ✭✭✭✭✭✭

    I want this too.  To be able to count cells based upon their formatting or color shading.  Since I cannot do a countif to see how many cells in a column actually 'contain' a search string.


  • Brett Evans
    Brett Evans ✭✭✭✭✭✭



    create a hidden column ("Conditional") that matches your conditional formatting.  Then have it evalute to "Blue", "Bold" or what ever settings are in your conditional formatting.


    To mimics "Contains", use =Find().  If a positive number was returned then the cell being evaluated "contains" your search string.


    Then you can do a =COUNTIF(Conditional:Conditional,"Bold") to figure out how many are in that category.



  • Brad Jones
    Brad Jones ✭✭✭✭✭✭
    edited 03/09/17

    Thanks for the help Brett.  Unfortunately, I want to count all the cells in a column that may be one of 7 or 8 different criteria.  So R1C1 could be blue, black, red, green, yellow, purple, white, or orange.  


    If I understand your proposal, then I would have to create 7 "Conditional" columns and then count those up.  No good.  Every hidden column becomes painfully obtrusive when the notifications go out daily.

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

    I think it's doable with one hidden column. Publish and I'll give it a try. 

  • Brad Jones
    Brad Jones ✭✭✭✭✭✭

    OK Brett, give it a whack,




    This is the blanked out template.  At the bottom, you can see the conditions I am counting for.  On the right, it is also counting.  


    What we want is for any cell that contains the word "morn" (typically on the left side of the cell), to be counted.  Horizontally and vertically.  Repeat that, but for all of the other conditions as well.   Just need to see the proof of concept, and I can fill in the rest. We want to be able to see that people don't work too many day/night/afternoon shifts (proportionately).

This discussion has been closed.