mykal66
12-11-2014, 08:20 AM
Hi
I created a spreadsheet to track reviews dates and show when they have been completed. I used a COUNTIF to sum overdue dates then subtracted another COUNTIF those that were marked as complete: =COUNTIF(P2:CR2,"<"&TODAY())-COUNTIF(P2:CR2,"ü")
This worked fine until i was asked to insert additional columns alongside planned review dates to add the actual reviews date so all the actual dates are now being included in the formula used above.
There are 27 groups of three columns (Planned Review, Actual Review, Received) so i can used and additional COUNTIF to subtract the actual review dates. I have an idea for a very dirty method to get around this by copying the 27 actual dates to another hidden worksheet then use a use use another COUNTIF to get a figure then incorporate into the formula used above.
I have googled and found several methods that i thought would only count data in every third cell but none seem to work so can anyone help please with a formula to save the dirty method i've come up with?
I've attached an example to try and make my muddy explanation clearer. In the example i would only need to count for data in the blue columns.
Thanks as always for any help you guys offer
Mykal
I created a spreadsheet to track reviews dates and show when they have been completed. I used a COUNTIF to sum overdue dates then subtracted another COUNTIF those that were marked as complete: =COUNTIF(P2:CR2,"<"&TODAY())-COUNTIF(P2:CR2,"ü")
This worked fine until i was asked to insert additional columns alongside planned review dates to add the actual reviews date so all the actual dates are now being included in the formula used above.
There are 27 groups of three columns (Planned Review, Actual Review, Received) so i can used and additional COUNTIF to subtract the actual review dates. I have an idea for a very dirty method to get around this by copying the 27 actual dates to another hidden worksheet then use a use use another COUNTIF to get a figure then incorporate into the formula used above.
I have googled and found several methods that i thought would only count data in every third cell but none seem to work so can anyone help please with a formula to save the dirty method i've come up with?
I've attached an example to try and make my muddy explanation clearer. In the example i would only need to count for data in the blue columns.
Thanks as always for any help you guys offer
Mykal