Count contents of cells (by color)

[sassriverrat]

I've been reading the forums but haven't found quite what I want.

I have a column of cells that's a mix of green, red, and white colors. I'd like a way to count the contents of all of the green cells, and preferably it needs to auto update every time a cell changes color....so if a cell suddenly becomes green, the user doesn't have to change anything or reselect anything.

Thanks!

 

[sassriverrat]

So to do this, I have the cells changing fill color via conditional formatting.

So I use a formula (=get.cell(38,sheet_reference)) that goes into the name manager under formulas. That formula spits back the color of the cell. I then tried to use SUMIFs to add up all of the cells there were green but it would appear that "get.cell" does not recognize a cell as being green if it was turned green by conditional formatting. It only recognizes cells that were color-changed by the user.

 

[Joe4]

Well that's where I've gotten stuck again. If I use name manager with get.cell(38,sheet_reference) then I can get it for cells that I fill, but not those that are filled conditionally....so I get stuck.

So I use a formula (=get.cell(38,sheet_reference))

I have never done anything like that before.
Is that the formula you use in your Conditional Formatting rule?

 

[sassriverrat]

No I use it in Formulas -> name manager and then I can use it in the cells. It just spits out the "color" of the cell, but only returns the value of a cell that I've changed the fill of, not one that conditional formatting has changed. How would you add the contents of cells that were conditionally formatted green?

 

[sassriverrat]

Like how would you say =sumif(the cell is green, add A1:A3)?

 

[Joe4]

Like how would you say =sumif(the cell is green, add A1:A3)?

You cannot. Excel formulas only work off of values, not off on formatting.

But if your cells are being colored by conditional formatting, all we have to do is use the same formulas as the criteria in a COUNTIF, COUNTIFS, SUMIF, SUMIFS, or SUMPRODUCT formula to get what you want.

Are we still talking about the same Conditional Formatting rule you posted up in post 7?
Or a different one?
If different, please post the Conditional Formatting rule.

It may also be helpful if you can post a small sample of your worksheet, along with your expected result.

 

[sassriverrat]

Yes so the conditional formatting is super simple: turn green if =$Q8="Yes"

Then I go to formulas, name manager, and input my (=get.cell(38,sheet_reference))
I then go to a new column (same row) and type in =color which just references the above formula.
If I've manually turned the cell green (which is excel #43), then my =sumifs sums all of my cells in B column that have a "43" in E column. Does that make more sense?

The issue with all of it is that the get.cell piece only works for rows that I change to green manually, not those that change via conditional formatting.

 

[Habtest]

Exactly Get.Cell doesn't pick up colors by conditional format color. I think what Joe is suggesting is you replicate the conditional format rule by COUNTIF(S)/SUMIF(S) where you need, like below. Hope it makes sense, otherwise please upload a sample of data and expected outcome.

Book1
	B															Q
8	1															Yes
9	2															Maybe
10	3															Yes
11	4															Yes
12	5															Maybe
13	6
14	7															No
15
16
17	Green															3
18	Red															1
Sheet1

Cell Formulas
Range	Formula
Q17	Q17	=COUNTIF($Q$8:$Q$14,"Yes")
Q18	Q18	=COUNTIF($Q$8:$Q$14,"No")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B17,B8:B15	Expression	=$Q8="Yes"	text	NO
B17,B8:B15	Expression	=$Q8="No"	text	NO

 

[Joe4]

Exactly Get.Cell doesn't pick up colors by conditional format color. I think what Joe is suggesting is you replicate the conditional format rule by COUNTIF(S)/SUMIF(S) where you need, like below. Hope it makes sense, otherwise please upload a sample of data and expected outcome.

Book1
	B															Q
8	1															Yes
9	2															Maybe
10	3															Yes
11	4															Yes
12	5															Maybe
13	6
14	7															No
15
16
17	Green															3
18	Red															1
Sheet1

Cell Formulas
Range	Formula
Q17	Q17	=COUNTIF($Q$8:$Q$14,"Yes")
Q18	Q18	=COUNTIF($Q$8:$Q$14,"No")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B17,B8:B15	Expression	=$Q8="Yes"	text	NO
B17,B8:B15	Expression	=$Q8="No"	text	NO

Yes, that is exactly what I am telling you to do.
Forget about that other stuff. No need to unnecessarily complicate matters.

 

[sassriverrat]

Ok this makes a lot more sense than how I was doing it! Thank you both. This cleaned up my sheet a lot! Thanks guys!