Good afternoon happy campers,
Worksheet =
Column A (range - A5:A70) - name
Column C (range C5:C70) - Date required
Column E (range E5:E70) - Date completed
Cell K2 - 01/01/2012
Cell L2 - 31/12/2012
In the above, lets say a team of people are scheduled to complete a task every week, the sheet contains dates from last year through to planned dates for next year.
Any weeks that pass without a check, a question mark (?) is placed into Column E relating to the expected date in Column C (to simply highlight "why is it not done")
Now i want my formula to calculate:
Out of all the info in Column C, only utilise 2012 (between K2 & L2 'start of year & end of year')
Calculate how many times the question mark (?) appears
AND
Out of this data, show me how many of the cells (in this year) contain a date.
I have added the formula
=IF(E4="","",IF(E4-C4=0,"On-Time",IF(E4="","",IF(ISTEXT(E4),"","OverDue"))))
to show me if the check was completed on/after its dues date - works fine.
After seeing this work i became a little 'Excel' happy, and jumped into 'IF,OR,AND' to try and get the result as mentioned above.
=IF(OR(AND(C5:C70>K2,C5:C70<L2),AND(E5:E70,"?")),COUNTIF(E5:E70,"?"))
But i only receive a #VALUE error, and am stumped on what im doing wrong.
I think it may have something to do with my COUNTIF part but im unsure, could someone please turn the light on for me lol
kind regards, galvinpaddy
Bookmarks