Help with formula comparing dates

[MattGault]

I'm trying to compare two dates in a Table to count the number of on time delivery of External requests. To accomplish this, my idea was to simply compare each record and count the number of times the Due Date > Date Sent. In theory, this should tell me if the deliverable was sent before the due date thus establishing on-time delivery.

I will attach a sample spreadsheet to explain further, but the formula I am trying to use is:

=COUNTIFS(Table1[Int VS Ext],"External",Table1[Date Sent],"<"&Table1[Due Date])

However, this formula is not giving expected results. I'm guessing I am just missing something simple.

Any advice or help is much appreciated.

Thanks.

--
Matt

 

[Somendra Misra]

@MattGault
Hi,

Is the answer is 3?
Regards!

 

[SirJB7]

Hi, MattGault!
Posted formula at A14 it'd be an array formula, so remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter. Then you'll get the 3 that Somendra Misra is asking for your confirmation.
Regards!

 

[Somendra Misra]

Hi MattGault,

Use below formula to avoid Ctrl+Shift+Enter, other wise go with SirJB7 advise.

=SUMPRODUCT((Table1[Int VS Ext]="External")*(Table1[Date Sent]>Table1[Due Date]))

Regards!

 

[MattGault]

@MattGault
Hi,

Is the answer is 3?
Regards!

Sorry I had to look at the data I typed in the sample sheet to see what the correct answer should be!
Yes correct - in the example I sent the answer should be 3.

 

[MattGault]

Hi MattGault,

Use below formula to avoid Ctrl+Shift+Enter, other wise go with SirJB7 advise.

=SUMPRODUCT((Table1[Int VS Ext]="External")*(Table1[Date Sent]>Table1[Due Date]))

Regards!

Thanks - this works. I wasn't thinking about using SUMPRODUCT vs COUNTIFS

Hi, MattGault!
Posted formula at A14 it'd be an array formula, so remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter. Then you'll get the 3 that Somendra Misra is asking for your confirmation.
Regards!

BTW - Thanks SirJB7 as always. I spaced Ctrl-Shift-Enter. This obviously also works. Appreciate your quick feedback.

 

[Somendra Misra]

@MattGault

Glad you solved it.

Regards,

 

[SirJB7]

Hi, MattGault!
Glad you solved it. I kept your original formula, but I rather prefer non array formulas so if I were you I'd go with Somendra Misra's SUMPRODUCT normal formula.
Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!

 

[MattGault]

Hi SirJB7!

Yes I couldn't agree more - I try to stay away from array formulas as well since they are always so sensitive to the variables. I just don't use SUMPRODUCT as much as I should so it always slips my mind until I come across Chandoo's excellent post. I need to just become more familiar with using it. Thanks again to both you and Somendra Misra for your quick reply.