COUNTIF depending on cell colour

DrunknMunky14

New Member
Joined
Jun 4, 2014
Messages
15
Hi guys,

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

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)

1234
AGreenGreenRedRed
BGreenRedGreenRed

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

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,744
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:

DrunknMunky14

New Member
Joined
Jun 4, 2014
Messages
15
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,100
Messages
5,622,717
Members
415,922
Latest member
gemmatay88

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top