Compare dates in two columns across several rows and count based on criteria.

[ccalkins]

Good Evening,
I have a list of tasks that need to be done either semi-annually or annually and need to calculate the percentage of tasks completed (71 total) in compliance (Cell C3). Currently I have conditional formatting in column C to turn the cells Green (more than 60 days remaining), amber (60 days until due), and red (past due) and this all works. I am using the following formula (COUNTIF(C4,"<="&$B4)+COUNTIF(C5,"<="&$B5)+.../total tasks. This seems to work fine up until the 33rd line item and then it stops counting. Is this a mechanical limitation of the number of operations in my formula? Should I be going about this in a different way? Given restrictions on our work computers, I would need to try and accomplish this using a formula instead of VBA.
Thank you,
Craig

 

[awoohaw]

what are your formula(s) that are in C3, and the rest of column C?
Are you using TODAY() as your comparison date?

 

[ccalkins]

Hello,
In Column B the formulas are TODAY()+365 and TODAY()+(365/2). The dates in Column C are just static dates (no formulas). The formula in C3 is =(COUNTIF(C4,"<="&$B4)+COUNTIF(C5,"<="&$B5)+...+COUNTIF(C55,"<="&$B56)/53
Thanks,
Craig