CountIfs based on dates

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,347
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top