COUNTIF - Alternate to count values in every third cell rather than full range.

[mykal66]

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

[Aflatoon]

For your sample, I think you want:
=SUMPRODUCT((MOD(COLUMN(A2:K2),3)=2)*(A2:K2<TODAY())*(B2:L2<>"Yes"))

[mykal66]

Hi.

Works like a charm in my example so i will give it a go on the main one at work tomorrow. Thank you so much, i realised my solution was very messy and some of the online solutions suggested sumproduct but i couldn't get any of them to work.

Really appreciate you help - Thank you

Mykal