Count Conditionally Formatted Colored Cells in Named Range

[Jimmypop]

Good day I have tried numerous examples but not working with my data set.

I have a named Range "Days_Overdue_Format". This range is made up of different sections in sheet and does not always follow each other e.g. =Questions!$O$6:$O$68,Questions!$O$73:$O$92 etc. (too long to post here)

I want to count the colored cells in Range and then return this value to a cell. e.g. there are for instance 25 red, 42 orange cells in range. The cells are colored using conditional formatting.

So result should look something like: (must also update if cell changes color)

Green (Colored using CF - RGB(0,255,0)	9
Orange (Colored using CF - RGB(255,192,0)	42
Yellow (Colored using CF - RGB(255,255,0)	18
Red (Colored using CF - RGB(255,0,0)	25

Thank you.

 

[Joe4]

=AND($A$2>=TODAY()+30,$A$2<>"")

You locked down the column and the row, so every single cell is locking at cell A2.
You only want to lock down the column reference, but let the row reference "float" so row 3 will lock at A3, row 4 will look at A4, etc.

So your formula should be:

=AND($A2>=TODAY()+30,$A2<>"")

and you will need to follow the same logic for all the other rules you created.

For a full explanation on these different type of cell references, see here: Absolute, Relative, and Mixed Cell References in Excel

 

[Jimmypop]

You locked down the column and the row, so every single cell is locking at cell A2.
You only want to lock down the column reference, but let the row reference "float" so row 3 will lock at A3, row 4 will look at A4, etc.

So your formula should be:

=AND($A2>=TODAY()+30,$A2<>"")

and you will need to follow the same logic for all the other rules you created.

For a full explanation on these different type of cell references, see here: Absolute, Relative, and Mixed Cell References in Excel

??? That one simple $ sign can mess me around like that. Thanks @Joe4... think this office getting to me sometimes... should have tried that.

 

[Joe4]

That one simple $ sign can mess me around like that. Thanks @Joe4... think this office getting to me sometimes... should have tried that.

You are welcome!

Yes, that "$" locks down the range reference that it precedes (column or row).
If you want to allow it float, you do not want it there.

The Conditional Formatting of a multi-celled range works the same way as if you copy a formula to multiple cells.
Anything with the "$" in front of it will be locked-down and not change as you copy down or across, while if you do not have it there, it can change.
So if you are unsure how the formula would react in Conditional Formatting, just put the formula in some empty cell on that row, and copy it down, and check out how it changes from row-to-row, to make sure it is changing appropriately.