I am using Sumproduct to count the number of parts delivered late to
schedule, but we have a 5 day grace period that I can't figure out how to
accommodate. What I need to do is add 5 days to the due date (X:X) then
determine if the receipt of the part (Y:Y) was later than that date.

what I have is:
=SUMPRODUCT(--(DATA!AE2:AE7500)=R6),--(DATA!X2:X7500<DATA!Y2:Y7500))

DATA! is the tab where the data is pulled into from their various legacy
systems
AE:AE is the vendor code (I am collecting the data by vendor)
X:X is the scheduled due date
Y:Y is the actual receipt date

Sample Data: (we can assume all of them to be from the same vendor)

1. X(Due) Y(Reciept)
2. 05/04/09 05/05/09
3. 06/05/09 05/27/09
4. 08/30/09 04/06/09
5. 03/31/09 04/01/09
6. 04/03/09 04/06/09

Currently I would get a result of 3 of the 5 delivered parts being late (2,
5, and 6), but since all of them were delivered within the 5 day grace period
I should show a result of 0 being late. I cannot figure out how to add 5 days
to the scheduled date to accommodate the grace period and not unduly burden
our suppliers with inaccurate metrics.

Any help is appreciated.

Try
=SUMPRODUCT(--(DATA!AE2:AE7500)=R6),--(DATA!X2:X7500+5<DATA!Y2:Y7500))


"Qikslvr" wrote:

I am using Sumproduct to count the number of parts delivered late to
schedule, but we have a 5 day grace period that I can't figure out how to
accommodate. What I need to do is add 5 days to the due date (X:X) then
determine if the receipt of the part (Y:Y) was later than that date.

what I have is:
=SUMPRODUCT(--(DATA!AE2:AE7500)=R6),--(DATA!X2:X7500<DATA!Y2:Y7500))

DATA! is the tab where the data is pulled into from their various legacy
systems
AE:AE is the vendor code (I am collecting the data by vendor)
X:X is the scheduled due date
Y:Y is the actual receipt date

Sample Data: (we can assume all of them to be from the same vendor)

1. X(Due) Y(Reciept)
2. 05/04/09 05/05/09
3. 06/05/09 05/27/09
4. 08/30/09 04/06/09
5. 03/31/09 04/01/09
6. 04/03/09 04/06/09

Currently I would get a result of 3 of the 5 delivered parts being late (2,
5, and 6), but since all of them were delivered within the 5 day grace period
I should show a result of 0 being late. I cannot figure out how to add 5 days
to the scheduled date to accommodate the grace period and not unduly burden
our suppliers with inaccurate metrics.

Any help is appreciated.

that would be the obvious solution. Unfortunately that only returns "#VALUE".
I have tried every version of X:X+5 I could think of. Inside Parens, outside
parens, extra set of parens, nothing seems to work in that direction.

BTW, I noticed an error in the formula I posted, sorry about that, it should
be
=SUMPRODUCT(--(DATA!AE2:AE7500=R6),--(DATA!X2:X7500+5<DATA!Y2:Y7500))
I had an extra Close paren,

"Robbro" wrote:

Try
=SUMPRODUCT(--(DATA!AE2:AE7500)=R6),--(DATA!X2:X7500+5<DATA!Y2:Y7500))


"Qikslvr" wrote:

I am using Sumproduct to count the number of parts delivered late to
schedule, but we have a 5 day grace period that I can't figure out how to
accommodate. What I need to do is add 5 days to the due date (X:X) then
determine if the receipt of the part (Y:Y) was later than that date.

what I have is:
=SUMPRODUCT(--(DATA!AE2:AE7500)=R6),--(DATA!X2:X7500<DATA!Y2:Y7500))

DATA! is the tab where the data is pulled into from their various legacy
systems
AE:AE is the vendor code (I am collecting the data by vendor)
X:X is the scheduled due date
Y:Y is the actual receipt date

Sample Data: (we can assume all of them to be from the same vendor)

1. X(Due) Y(Reciept)
2. 05/04/09 05/05/09
3. 06/05/09 05/27/09
4. 08/30/09 04/06/09
5. 03/31/09 04/01/09
6. 04/03/09 04/06/09

Currently I would get a result of 3 of the 5 delivered parts being late (2,
5, and 6), but since all of them were delivered within the 5 day grace period
I should show a result of 0 being late. I cannot figure out how to add 5 days
to the scheduled date to accommodate the grace period and not unduly burden
our suppliers with inaccurate metrics.

Any help is appreciated.