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.

 

[Jimmypop]

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

=SUMPRODUCT(--(L6:L700>=TODAY()+30),--(L6:L700<>""))

Orange

=SUMPRODUCT(--(L6:L700<TODAY()+30),--(L6:L700>TODAY()+7),--(L6:L700<>""))

Yellow

=SUMPRODUCT(--(L6:L700<TODAY()+7),--(L6:L700>TODAY()+1),--(L6:L700<>""))

Red

=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)	Format	Applies 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.

 

[Joe4]

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]

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:

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

 

[Jimmypop]

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]

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:

=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.

 

[Jimmypop]

So here is what I did (for any one else looking for similar code to adapt)

Counting

Green Count

=SUMPRODUCT(--(L6:L700>=TODAY()+30),--(L6:L700<>""))

Orange Count

=SUMPRODUCT(--(L6:L700<TODAY()+30),--(L6:L700>TODAY()+7),--(L6:L700<>""))

Yellow Count

=SUMPRODUCT(--(L6:L700<=TODAY()+7),--(L6:L700>TODAY()+1),--(L6:L700<>""))

Red Count

=SUMPRODUCT(--(L6:L700<=TODAY()+1),--(L6:L700>TODAY()-365),--(L6:L700<>""))

CF Formulas

Green CF Formula

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

Orange CF Formula

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

Yellow CF Formula

=AND($L6<=TODAY()+7,$L6>TODAY()+1,$L6<>"")

Red CF Formula

=AND($L6<=TODAY()+1,$L6>TODAY()-365,$L6<>"")

 

[Jimmypop]

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.

 

[Joe4]

Can you post what the rule for the green formatting is, since that is the one that seems to be applied to every row?

 

[Jimmypop]

Can you post what the rule for the green formatting is, since that is the one that seems to be applied to every row?

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

 

[Jimmypop]

Can you post what the rule for the green formatting is, since that is the one that seems to be applied to every row?

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

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