Count Conditionally Formatted Colored Cells in Named Range

Jimmypop

Well-known Member
Joined
Sep 12, 2013
Messages
753
Office Version
  1. 365
Platform
  1. Windows
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.
 
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"))
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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.
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Please ignore previous message. I made a mistake...
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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
Back
Top