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]

You should be able to using the COUNTIF or COUNTIFS function, using the same criteria that you used in Conditional Formatting in these formulas to get your counts.

 

[Jimmypop]

Hi Joe

Will try that... but will it work if the result of CF value is different for a red or orange range... maybe could give me an example to work from. More or less know a bit more about VBA than actual formulas... thanks... will first try to see if can get the formula to work

 

[Joe4]

Will try that... but will it work if the result of CF value is different for a red or orange range... maybe could give me an example to work from. More or less know a bit more about VBA than actual formulas... thanks... will first try to see if can get the formula to work

You have a different CF rule for each color, right?
Similarly, you would have a different COUNTIF formula for each color.

If you have any trouble figuring it out, please post one of your Conditional Formatting formulas, along with the range it is applied to.

 

[Jimmypop]

Thanks... will do when back at work tomorrow...

 

[Joe4]

I missed this detail originally.

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)

COUNTIF will not work on non-contiguous ranges.
See this link for workarounds to that: Count Non-Contiguous Range using COUNTIF - Free Excel Tutorial.

 

[Jimmypop]

Hi Joe...

I have tried with CountIf but seem to be doing something wrong as no values are returned.

So conditional formatting is as follows:

Rule (applied in order shown)	Format	Applies to
Cell contains a blank value	Light Green (RGB(204,255,204))	Days_Overdue_Format (Named Range)
Cell Value contains "Day/s Overdue"	Red (Colored using CF - RGB(255,0,0)	Days_Overdue_Format (Named Range)
Formula:=ABS(TODAY()-L6:L25)>=30	Green (Colored using CF - RGB(0,255,0)	Days_Overdue_Format (Named Range)
Formula:=ABS(TODAY()-L6:L25)<=30	Yellow (Colored using CF - RGB(255,255,0)	Days_Overdue_Format (Named Range)
Formula:=ABS(TODAY()-L6:L25)<=30	Orange (Colored using CF - RGB(255,192,0)	Days_Overdue_Format (Named Range)

Days_Overdue_Format = =Questions!$O$6:$O$68,Questions!$O$73:$O$92,Questions!$O$97:$O$113,Questions!$O$118:$O$123,Questions!$O$128:$O$138,Questions!$O$143:$O$170,Questions!$O$175:$O$197,Questions!$O$202:$O$214,Questions!$O$219:$O$226,Questions!$O$231:$O$248,Questions!$O$253:$O$269,Questions!$O$274:$O$286,Questions!$O$291:$O$336,Questions!$O$341:$O$355,Questions!$O$360:$O$400,Questions!$O$405:$O$434,Questions!$O$439:$O$521,Questions!$O$526:$O$530,Questions!$O$535:$O$541,Questions!$O$546:$O$558,Questions!$O$563:$O$574,Questions!$O$579:$O$585,Questions!$O$590:$O$596

To give some more background.

User will manually enter a date in Column L (corresponding to Question in row), Column N has formula =Today().

Then in Column O there is a formula working out Days Overdue etc. That formula is =IF(ISBLANK(L6),"",IF(L6<N6,L6-N6&" Day/s Overdue",L6-N6&" Day/s until Overdue")).

So what I need (in image is the number of colored cells in Column R (as per image).

One thing I noticed now is the CF Formula is only for L6:L25. It is supposed to be the whole of Column L.

I do have a Count Colored Cells VBA which I use in another sheet which works perfectly, but does not in this one...

Thanks.

 

[Jimmypop]

Update...

Updated CF Formula L6:L25 to L:L ... now it makes everything green and not as it was...

 

[Joe4]

Did you see the link I posted in my previous post that says that COUNTIF will NOT work on non-contiguous cells?
They have some workarounds.

In your posting of your CF rules, I think you may have made a mistake, as the rules for both yellow and orange look exactly the same!

I don't think the manual "Count By Color" VBA code works on ranges colored by Conditional Formatting. I believe it only works on cells that are manually colored.

Also note that if you change the range of your Conditional Formatting, you will probably need to change your CF formula to reflect that change (the formula would need to reflect the first cell in the range, cell L1).

 

[Jimmypop]

Hi

Did you see the link I posted in my previous post that says that COUNTIF will NOT work on non-contiguous cells?
They have some workarounds.

In your posting of your CF rules, I think you may have made a mistake, as the rules for both yellow and orange look exactly the same!

I don't think the manual "Count By Color" VBA code works on ranges colored by Conditional Formatting. I believe it only works on cells that are manually colored.

Also note that if you change the range of your Conditional Formatting, you will probably need to change your CF formula to reflect that change (the formula would need to reflect the first cell in the range, cell L1).

Hi Joe

Yes I did read through the example of COUNTIF not working on non-contiguous cells. I tried it and it did not work...