CountIfs based on dates

[gheyman]

I have a bunch of dates down column E (Due Dates). And I have a bunch of dates down column F (Completion Dates)

I am trying to create a CountIfs formulas that would show How many were completed on time and how many were completed late between two date (example 10/1/2018 and 10/31/2018)

Or maybe CountIfs isn't the right approach?

Thanks

 

[gheyman]

PS There are Blanks in the columns

 

[tyija1995]

Hey,

I like using SUMPRODUCT for this sort of problem.

Try:
=SUMPRODUCT((E2:E20>=F2:F20)*(F2:F20<>""))

Where the Due dates are E2:E20 and the Completion dates are F2:F20

The answer will show how many dates were completed by or before the due date.

I assume the blanks are in the completion dates and not the due dates?

 

[gheyman]

Thanks, but that only counts completed on time and does not show me a count for a specific date range. I want to see how many were on time in, for example the month of October.

 

[tyija1995]

Okay so you want to see how many were completed before/on the due date where the due date is in October?

Then you would want something like this: (Change depending on your range)

=SUMPRODUCT((E2:E20>=H1)*(E2:E20<=I1)*(F2:F20<>"")*(E2:E20>=F2:F20))

Where E2:E20 is the range of Due Dates, F2:F20 is the range of Completed Dates, H1 is 1st October 2019, I1 is 31st October 2019

Change as appropriate for your scenario (2018/2017 etc etc)

Hope this helps!