Sumproduct with added days

Q

Qikslvr

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.
 
Q

Qikslvr

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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top