Sumproduct with variation

  • Thread starter Thread starter Qikslvr
  • Start date Start date
Q

Qikslvr

I am trying to compare two dates, but we allow a five day grace period from
the due date, so as long as the part is delivered within 5 days of the due
date its considered on time.

How do I tell Sumproduct that I want to compare the scheduled date + 5 days
to the delivered date?
Here is the formul
=SUMPRODUCT(--(DATA!$AE$2:$AE$7500=R6),--(DATA!$X$2:$X$7500<DATA!$Y$2:$Y$7500))

DATA!AE:AE is just the vendor code
DATA!X:X is the scheduled delivery date (I need this date +5)
DATA!Y:Y is the actual delivery date

When I try
=SUMPRODUCT(--(DATA!$AE$2:$AE$7500=R6),--((DATA!$X$2:$X$7500)+5<DATA!$Y$2:$Y$7500)) in any way, I just get a #VALUE error.


Sample data - Notice the 3rd one down was late but within the 5 day grace
period. This one should be counted as on time instead of late.
X Y
05/04/09 06/05/09
04/22/09 05/05/09
05/04/09 05/05/09
04/22/09 04/16/09
04/22/09 05/27/09
05/29/09 05/27/09
04/22/09 05/05/09
04/13/09 04/03/09
04/22/09 05/19/09
06/10/09 05/22/09
04/17/09 05/20/09


Thanks,
 
Not real sure what result you're looking for but your formula works for me.
Based on the posted sample data and assuming those dates are all for the
same vendor the formula returns 6.

X = being counted:

05/04/09 06/05/09 X
04/22/09 05/05/09 X
05/04/09 05/05/09
04/22/09 04/16/09
04/22/09 05/27/09 X
05/29/09 05/27/09
04/22/09 05/05/09 X
04/13/09 04/03/09
04/22/09 05/19/09 X
06/10/09 05/22/09
04/17/09 05/20/09 X

What result do you expect?
 
Back
Top