Count Conditionally Formatted Colored Cells in Named Range

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
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.
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
Got the counting to work finally. Will it still work if I replace L6:L100 with a named range where the named range contains non-contigous references? E.g. L6:L68, L93:L96, L101:L140 etc. Or can I just say L6:L780... there are merged cells inbetween that do not contain data... they are just merged for cosmetic purposes.

Now struggling just to get the CF to work. When I use the counting formula in the CF it formats everything with the top color... will play around a bit and guve feedback...
@Joe4

Update: I changed L6:L100 to L6:L700 and the counting is still working. Thanks so got it to work after playing around with the SUMPRODUCT Examples you gave me. Formulas used is below (for anyone having similar issue):

Green
Excel Formula:
=SUMPRODUCT(--(L6:L700>=TODAY()+30),--(L6:L700<>""))
Orange
Excel Formula:
=SUMPRODUCT(--(L6:L700<TODAY()+30),--(L6:L700>TODAY()+7),--(L6:L700<>""))
Yellow
Excel Formula:
=SUMPRODUCT(--(L6:L700<TODAY()+7),--(L6:L700>TODAY()+1),--(L6:L700<>""))
Red
Excel Formula:
=SUMPRODUCT(--(L6:L700<TODAY()),--(L6:L700>TODAY()-365),--(L6:L700<>""))

But now when I use the same formulas in my CF Rules it changes the entire reference of CF to the color that appears first in list. Have tried moving rules up and down in various ways but it always formats whatever color appears first in CF Rules. Have also tried ticking Stop if true on all of them with same result.

Rule (applied in order shown)FormatApplies to
=SUMPRODUCT(--(L6:L700>=TODAY()+30),--(L6:L700<>""))Green (RGB(0,255,0)=$O$6:$O$68
=SUMPRODUCT(--(L6:L700<TODAY()+30),--(L6:L700>TODAY()+7),--(L6:L700<>""))Orange (RGB(255,192,0)=$O$6:$O$68
=SUMPRODUCT(--(L6:L700<TODAY()+7),--(L6:L700>TODAY()+1),--(L6:L700<>""))Yellow (RGB(255,255,0)=$O$6:$O$68
=SUMPRODUCT(--(L6:L700<TODAY()),--(L6:L700>TODAY()-365),--(L6:L700<>""))Red (RGB(255,0,0)=$O$6:$O$68

Basically in plain words: The result of my date formula working out how many days is left or how many days are overdue gives a result in Column O whether it be 35 days to overdue (Green) Less than 30 days to overdue(Orange), Less than 7 days to Overdue and if it is overdue then it needs to show red.
Screenshot 2021-04-22 111307.png
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
Got the counting to work finally. Will it still work if I replace L6:L100 with a named range where the named range contains non-contigous references? E.g. L6:L68, L93:L96, L101:L140 etc. Or can I just say L6:L780... there are merged cells inbetween that do not contain data... they are just merged for cosmetic purposes.
If there is no data in there, it shouldn't be an issue as we are telling it to skip the blank cells with the L6:L100<>"" part.
So instead of messing around with non-contiguous ranges, I would just apply it to the whole range, L6:6780 at once.

Now struggling just to get the CF to work. When I use the counting formula in the CF it formats everything with the top color... will play around a bit and guve feedback...
Note that when applying a CF formula to a whole range of cells, first select the entire range that you want to apply it to.
Then, write the formula as it pertains to the first cell in the range you selected. Excel is smart enough to adjust it for the other cells.

So, for the green condition
=SUMPRODUCT(--(L6:L100>=TODAY()+30),--(L6:L100<>""))
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
Sorry, I a had mistake in my last post. The formula I posted in my last post is the formula for the total count of green entries, it is not the formula that you would use for Conditional Formatting (I meant to edit it, but got distracted and forgot to do that).

With Conditional Formatting, you are applying the formatting for one cell/row at a time, not all in aggregate. As I was describing, you select the range you want to apply it to, then write the formula as it applies to the first cell only. So that formula should look like:
Excel Formula:
=AND($L6>=TODAY()+30,$L6<>"")
So we apply the same conditions, but just on the first cell of our selection, not in the aggregate SUMPRODUCT function.
If we write the formula correctly, Excel will automatically adjust it for all the other cells in our selection (just like when you copy a formula down, how Excel updates the cell references for you).
 
Solution

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
Thanks and will have a look... busy with something else so will probably be tomorrow... the bosses think that Google Forms can do miracles so first need to finish that🙈🙈🤣🤣🤣
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123

ADVERTISEMENT

Sorry, I a had mistake in my last post. The formula I posted in my last post is the formula for the total count of green entries, it is not the formula that you would use for Conditional Formatting (I meant to edit it, but got distracted and forgot to do that).

With Conditional Formatting, you are applying the formatting for one cell/row at a time, not all in aggregate. As I was describing, you select the range you want to apply it to, then write the formula as it applies to the first cell only. So that formula should look like:
Excel Formula:
=AND($L6>=TODAY()+30,$L6<>"")
So we apply the same conditions, but just on the first cell of our selection, not in the aggregate SUMPRODUCT function.
If we write the formula correctly, Excel will automatically adjust it for all the other cells in our selection (just like when you copy a formula down, how Excel updates the cell references for you).
Hi @Joe4

Update. Got everything working as it should now. Had to tweak here and there, but looks like it is working just fine. Will post update on how exactly got it to work. :cool:
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
So here is what I did (for any one else looking for similar code to adapt)

Counting

Green Count
Excel Formula:
=SUMPRODUCT(--(L6:L700>=TODAY()+30),--(L6:L700<>""))
Orange Count
Excel Formula:
=SUMPRODUCT(--(L6:L700<TODAY()+30),--(L6:L700>TODAY()+7),--(L6:L700<>""))
Yellow Count
Excel Formula:
=SUMPRODUCT(--(L6:L700<=TODAY()+7),--(L6:L700>TODAY()+1),--(L6:L700<>""))
Red Count
Excel Formula:
=SUMPRODUCT(--(L6:L700<=TODAY()+1),--(L6:L700>TODAY()-365),--(L6:L700<>""))

CF Formulas

Green CF Formula
Excel Formula:
=AND($L6>=TODAY()+30,$L6<>"")
Orange CF Formula
Excel Formula:
=AND($L6<TODAY()+30,$L6>TODAY()+7,$L6<>"")
Yellow CF Formula
Excel Formula:
=AND($L6<=TODAY()+7,$L6>TODAY()+1,$L6<>"")
Red CF Formula
Excel Formula:
=AND($L6<=TODAY()+1,$L6>TODAY()-365,$L6<>"")
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123

ADVERTISEMENT

Hi @Joe4
Hope all is still well and safe on your side.

As I said above everything is working for the one sheet as it should as per above suggestions and updates.

However when I want to do exactly the same as above on another sheet the CF does not work. The Counting works perfect just not the CF? I selected the range where I want to apply the CF to and wrote the formula as it applies to the first cell only, however it still does not change the the way it should. Makes everything the first color on CF Rules.
Untitled.png





Untitled1.png
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
Can you post what the rule for the green formatting is, since that is the one that seems to be applied to every row?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,530
Messages
5,636,861
Members
416,946
Latest member
mniceguy81

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