# COUNTIF depending on cell colour

#### DrunknMunky14

##### New Member
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)

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
30 views and it seems that no one can help me out

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
ABCDEFG
1Red
2Green
3Purple
4CountColorIf(G1:G4,A1:D1,"Green",A2:D2,"Red")1Yellow
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.

Last edited:
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.

Replies
3
Views
693
Replies
7
Views
305
Replies
1
Views
600
Replies
1
Views
261
Replies
7
Views
427

1,221,127
Messages
6,158,099
Members
451,464
Latest member
Holden3

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back