COUNTIF depending on cell colour

[DrunknMunky14]

Hi guys,

Firstly, excuse the difference in spelling for those in America (I am Australian)

So I've searched the net and found Count Cell Colour formulas, but I'm trying to find a way to count cells dependant on other counted cells.

For example (I don't know how to format in here so the words are the highlighted cells in excel)

	1	2	3	4
A	Green	Green	Red	Red
B	Green	Red	Green	Red

<tbody>
</tbody>

I want to do a COUNTIF(A1:A4 = Green AND B1:B4 = Green), COUNTIF(A1:A4 = Green AND B1:B4 = Red), COUNTIF(A1:A4 = Red AND B1:B4 = Green) and COUNTIF(A1:A4 = Red AND B1:B4 = Red)

Any ideas of how to do this with VBA (or even without)

 

[DrunknMunky14]

30 views and it seems that no one can help me out

 

[Eric W]

It's not so much that no one can help you, it's more that it's an ill-defined problem. When you say "Green", do you mean light green, dark green, olive green, ocean green? How will the colors be set? Via the fill color button, or a macro, or Conditional Formatting? Would you want to add other colors to the list?

In theory, it would be fairly simple to write a UDF to create the function you want, but there would be a lot of back and forth to resolve the details, and many of the contributors here want to avoid that.

For what it's worth, if the cells are highlighted via conditional formatting, you would be better served to duplicate the original formulas. It is possible to check CF colors, but it's difficult and unreliable.

About the "best" version I can imagine would be something like:

Excel 2010

	A	B	C	D	E	F	G
1							Red
2							Green
3							Purple
4	CountColorIf(G1:G4,A1:D1,"Green",A2:D2,"Red")	1					Yellow
5

<tbody>
</tbody>

Sheet24​

Where you'd have to specifically define your colors in a section of your sheet (G1:G4), and the rest of the function would be like a regular COUNTIF.

If that's workable for you, let me know.

 

[DrunknMunky14]

Hey Eric,

That's fair enough. Didn't realise I wasn't clear enough.

I'm using the conditional formatting colours, so the standard green for that.

Thanks for the advice. I'm going to try look at other avenues to try and achieve this then, maybe creating a column next to my data with a symbol to represent colour and do a countif through that.