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

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
ย
ย | A | B | C |
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 | |
3 | 1 | ||
4 | 1 | ||
5 | 1 | ||
6 | 1 | ||
7 | 1 | ||
8 | 1 | ||
9 | 1 | ||
10 | 1 | ||
11 | 1 |
Comments
-
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.
Thanks
Zhana
-
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.
ย
-
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
-
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.
-
I think it's doable with one hidden column. Publish and I'll give it a try.ย
-
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).
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives