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
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.

Thanks!

Zhana

ย 

ย ABC
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"
2
Collumn B has conditional formatting to turn green when I check a box in column A
1
31
41
51
61
71
81
91
101
111

Comments

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

  • Zhana Morris
    Zhana Morris โœญโœญ

    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.

    Thanks

    Zhana

  • 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 โœญโœญโœญโœญโœญโœญ

    Brad,

    ย 

    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.

    ย 

    -Brett

  • 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,

    ย 

    https://app.smartsheet.com/b/publish?EQBCT=2a863bc4af8b407fb7a030dc64bd1b41

    ย 

    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.