Sumproduct with added days
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. |
Sumproduct with added days
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. |
Sumproduct with added days
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. |
All times are GMT +1. The time now is 05:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com