SUMPRODUCT with latest Date

**matt4003** · 03-21-2005, 10:25 PM

Hi All,

So I have a new project which I make weekly updates to...what I would like to do is write a SUMPRODUCT statement with three validations and one sum...should look something like this:

=sumproduct((Tab1!A1:A100=Tab2!A1)*(Tab1!B1:B100=Tab2!A2)*(Tab1!C1:C100"largest value---this is where I need help--")*(TAB1!D1:D100))

Basically I download a forecast from my customer which is rolling for 4 weeks, looks like a step ladder, here is an example:

Forecast Week 1: Week 1 FCST (100) Week 2 FCST (150) Week 3 FCST (100) Week 4 FCST (200) - -
Forecast Week 2: Week 1 (complete) Week 2 FCST(200) Week 3 FCST (150) Week 4 FCST (150) Week 5 FCST (300)
Forecast Week 3: Week 1 (complete) Week 2 (complete) Week 3 FCST (200) Week 4 FCST (100) Week 5 FCST (450) Week 6 FCST (500)

See how it is stepping down, each week becomes the most recent. I want a formula which will gather the latest Forecast based on the Part (Tab1!A1:A100), Work Week(Tab1!B1:B100), and the latest Forecast Week(Tab1!C1:C100), then sumproduct the quantities forecasted(Tab1!D1:D100).

Does this make sense...?? There should be some kind of logic I can put in to look for the latest forecast week...or maybe someone has a better suggestion...

Thanks,
Matt

**Barrie Davidson** · 03-21-2005, 10:39 PM

How about this (untested):

=sumproduct((Tab1!A1:A100=Tab2!A1)+0,(Tab1!B1:B100=T ab2!A2)+0,(Tab1!C1:C100=max(tab1!c1:c100))+0,TAB1!D1:D100)

Hope this helps

**matt4003** · 03-22-2005, 12:27 AM

Hello Barrie,

Thank you for the help, unfortunatly it does not work.

Any other ideas....anyone??

Thanks,
Matt

**mangesh_yadav** · 03-22-2005, 01:58 AM

The solution given by Barrie is correct. Maybe, you could give a small numerical example and the expected answer, then we could help. Give your ranges in each of the column with say 5 to 6 rows.

Mangesh