Counting # of Cells that are overdue

**jeremy.fisk** · 10-28-2008, 07:32 AM

I have a Column (G) of dates that is the sum of Column (B) and 6 months. I have conditionally formatted Column (G) to turn RED when overdue Yellow when close and GREEN if more than a month out. At the end of Column (G) I would like for it to add all the "RED" blocks and total them.

Any suggestions would be greatly appreciated. I have already tried CountIF and don't get any results. I am really at a loss

**DonkeyOte** · 10-28-2008, 07:33 AM

You need to replicate the rules you're using to govern the conditional formatting in your formula.... most likely you will need to use a SUMPRODUCT approach to apply across a range.

Post up your conditional format rules and specify to which ranges they are applied.

**jeremy.fisk** · 10-28-2008, 07:36 AM

These are the formulas for G3:
=B3+182.5
To format Red
=(g3-Today())<15
To format Yellow
=(g3-Today())<31
To format for Green
=(g3-today())>31

thanks for the quick reply

**DonkeyOte** · 10-28-2008, 07:46 AM

OK let's first start by saying you need to be careful when it comes to adding dates etc... to add 6 months to a given day you'd be better served using

=DATE(B3,MONTH(B3)+6,DAY(B3))

or if you have the AnalysisToolPak activated (via Tools - Add-ins) you can use EDATE

=EDATE(B3,6)

Adding 182.5 days is not always going to be 100% accurate.

The next thing I would say is that you would be better served storing TODAY() in a cell in your worksheet and then referring to that cell from your conditional formats rather than using TODAY() in the format rules themselves... eg A1 =TODAY(), format rule

=(G3-$A$1)<15

etc...

Last thing... assuming you have G3 replicated say in range G4:G100 to count up your categories you would use the same rules in a formula...

so let's say I3 is to count instances where < 15

=SUMPRODUCT(--($G$3:$G$100>0),--(INT($G$3:$G$100)-$A$1<15))

etc...

Hope that helps.

**jeremy.fisk** · 10-28-2008, 07:52 AM

HOLY CRAP>....I will try doing that....I am not sure if I am fully understanding...but if it is better i am going to try to fix it all.

THANKS!!!

**arthurbr** · 10-28-2008, 07:52 AM

Which version of XL are you using?

**jeremy.fisk** · 10-28-2008, 07:59 AM

Excel 07

I have done step 1 and 2 of the change...but I am getting confused of step three...(the formatting) I am going to try it again because it didnt work last time

**jeremy.fisk** · 10-28-2008, 08:06 AM

OK...so what would be the formula for it being past due?

If <15 is Yellow
and <31 is Blue
and >31 is Green

what would be red? Say it was due yesterday or last week

(I know I know...I am changing my formulas, but I figure...While I am at it...)

**DonkeyOte** · 10-28-2008, 08:09 AM

are you talking about your conditional format rules ?

Rule 1 - Yellow
=(G3-$A$1)<15

Rule 2 - Blue
=(G3-$A$1)<31

Rule 3 - Green
=G3>0

in XL07 tick the checkbox on each rule that says "Stop if True"

EDIT: just seen your post re: Red

Create a new rule (should be #1)
=(G3-$A$1)<0

**jeremy.fisk** · 10-28-2008, 08:23 AM

ALRIGHT! I got it but there is a problem...the yellow formatting isnt working.

**jeremy.fisk** · 10-28-2008, 08:30 AM

I got it! I got it! well...you did...but it works for me now!!!! WOOO HOO....thanks for the help

**DonkeyOte** · 10-28-2008, 08:37 AM

OK great -- I believe the premise on this forum (I'm new myself) is for the OP (ie you) to mark the thread as solved such that people don't continue to provide solutions.