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
Tried again and came up with same formula as this morning which does not work...

=SUM(COUNTIF(INDIRECT({"$O$6:$O$68","$O$73:$O$92","$O$97:$O$113","$O$118:$O$123","$O$128:$O$138","$O$143:$O$170","$O$175:$O$197","$O$202:$O$214","$O$219:$O$226","$O$231:$O$248","$O$253:$O$269","$O$274:$O$286","$O$291:$O$336","$O$341:$O$355","$O$360:$O$400","$O$405:$O$434","$O$439:$O$521","$O$526:$O$530","$O$535:$O$541","$O$546:$O$558","$O$563:$O$574","$O$579:$O$585","$O$590:$O$596"}),">30"))
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,955
Office Version
  1. 365
Platform
  1. Windows
I am really confused about your Conditional Formating formulas.
As mentioned, you seem to be using the exact same formula for two different colors (yellow and orange).
And I am not sure why you are using the absolute value function (ABS) on the date subtraction. That would mix in the ones that are overdue with the ones that are not overdue.

EDIT: I just see your last update. Regarding that yellow should be less than 7. That could also be problematic, as there could be overlap in records that are less than 7 and less than 30 (meaning it would meet the conditions for two different rules)!

I think it would be best if maybe we forget about all these formulas (as they may seem to include errors in logic), and just explain in plain English what each color is supposed to represent/highlight.
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
I am really confused about your Conditional Formating formulas.
As mentioned, you seem to be using the exact same formula for two different colors (yellow and orange).
And I am not sure why you are using the absolute value function (ABS) on the date subtraction. That would mix in the ones that are overdue with the ones that are not overdue.
I too am confused now... got ABS formulas from another post... I think it should be simple but cannot wrap my overworked brain around it...

Al I am looking to to is if a user insert's a date manually in Column L then Column O needs to display how many days until overdue or how many days is already overdue. (Column N has todays date in always). So every time they open the sheet all will recalculate and days overdue will change.

And on Orange and Yellow I did explain in previous post that it was my mistake when copying to MrExcel. Yellow should actually be less than 7 (It is correct on my sheet)

So Column O needs to say how many days till overdue or already overdue with colors being green is more than 30 days, orange less than 30 days, yellow less than 7 days and red when going into negative days...
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,955
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So, you need to get rid of all the ABS functions in your CF formulas.

If you do the day/month calculation in another column, you can use COUNTIF or COUNTIFS. Otherwise, you will need to use SUMPRODUCT.
To get a count of the number of records meeting your "green" conditon, your SUMPRODUCT function would look like (for a range of L6:L100)
Excel Formula:
=SUMPRODUCT(--(L6:L100>=TODAY()+30),--(L6:L100<>""))

For orange, you actually want to include another condition (in both your CF rule and formula), that checks for days between 7 and 30, like this:
Excel Formula:
=SUMPRODUCT(--(L6:L100<TODAY()+30),--(L6:L100>TODAY()+7),--(L6:L100<>""))

Based on those two examples, you should be able to come up with the other rules.
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
So, you need to get rid of all the ABS functions in your CF formulas.

If you do the day/month calculation in another column, you can use COUNTIF or COUNTIFS. Otherwise, you will need to use SUMPRODUCT.
To get a count of the number of records meeting your "green" conditon, your SUMPRODUCT function would look like (for a range of L6:L100)
Excel Formula:
=SUMPRODUCT(--(L6:L100>=TODAY()+30),--(L6:L100<>""))

For orange, you actually want to include another condition (in both your CF rule and formula), that checks for days between 7 and 30, like this:
Excel Formula:
=SUMPRODUCT(--(L6:L100<TODAY()+30),--(L6:L100>TODAY()+7),--(L6:L100<>""))

Based on those two examples, you should be able to come up with the other rules.
Thanks will try... and give feedback
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123

ADVERTISEMENT

So, you need to get rid of all the ABS functions in your CF formulas.

If you do the day/month calculation in another column, you can use COUNTIF or COUNTIFS. Otherwise, you will need to use SUMPRODUCT.
To get a count of the number of records meeting your "green" conditon, your SUMPRODUCT function would look like (for a range of L6:L100)
Excel Formula:
=SUMPRODUCT(--(L6:L100>=TODAY()+30),--(L6:L100<>""))

For orange, you actually want to include another condition (in both your CF rule and formula), that checks for days between 7 and 30, like this:
Excel Formula:
=SUMPRODUCT(--(L6:L100<TODAY()+30),--(L6:L100>TODAY()+7),--(L6:L100<>""))

Based on those two examples, you should be able to come up with the other rules.
Why would this formula give me 22 days to go event though dates are different? Also not seeing it says to me the text of days overdue or days to overdue
Untitled.png
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,955
Office Version
  1. 365
Platform
  1. Windows
Please ignore previous message. I made a mistake...
Yes, the formulas I gave you were for your totals, your column "R" calculations.
Obviously, you would not copy those formulas down for each row of data, since it is already giving you a total count of all your data meeting that criteria.
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
Yes, the formulas I gave you were for your totals, your column "R" calculations.
Obviously, you would not copy those formulas down for each row of data, since it is already giving you a total count of all your data meeting that criteria.
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,129,762
Messages
5,638,207
Members
417,014
Latest member
dualwieldbacon

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