Creating a formula that relies on conditional formatting

Zhana Morris

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

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

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

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

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

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

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

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