Need projected shipment quantities for 2008

[Pierre]

Maybe the heavy hitters know; no steroids please. . .
Use sumif to add fields based upon year

On sheet1 col A have numerous part numbers for items. Many of these
items repeat.
Also on sheet1 col E have open shipment quantities for that row, and
in Col F have the expected shipdate "yyyy-ww"

Need to do a sumif from another worksheet to total the expected
shipment quantities of that item for the year 2008.

Ideas?

IOW:

SOURCE DATA
ITEM ON ORDER YEAR-WEEK
ABC 10 2008-10
ABC 20 2009-10
IEI 5 2008-06
EOE 1 2009-01
IEI 66 2009-25

RESULT PAGE

ITEM: 2008 SHIPMNTS

ABC 10
EOE 0
IEI 5


TIA for all your thoughts.

Pierre

 

[Bernie Deitrick]

Pierre,

=SUMPRODUCT((Sheet1!$A$2:$A$1000="ABC")*(LEFT(Sheet1!$F$2:$F$1000,4)="2008")*Sheet1!$E$2:$E$1000)


You can also use cell References for the year and item nems/part numbers

=SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(LEFT(Sheet1!$F$2:$F$1000,4)=B$1)*Sheet1!$E$2:$E$1000)

Which will allow you to create a table by listing the part numbers down
column A starting in row 2, with years in row 1 starting in column B. (Copy
and paste to create a rectangular table...)

HTH,
Bernie
MS Excel MVP

 

[Pierre]

Pierre,

=SUMPRODUCT((Sheet1!$A$2:$A$1000="ABC")*(LEFT(Sheet1!$F$2:$F$1000,4)="2008"­)*Sheet1!$E$2:$E$1000)

You can also use cell References for the year and item nems/part numbers

=SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(LEFT(Sheet1!$F$2:$F$1000,4)=B$1)*She­et1!$E$2:$E$1000)

Which will allow you to create a table by listing the part numbers down
column A starting in row 2, with years in row 1 starting in column B.  (Copy
and paste to create a rectangular table...)

HTH,
Bernie
MS Excel MVP

Bernie:

Thank you!!!!!

Pierre

 

[Bernie Deitrick]

Thank you!!!!!

You're quite welcome. Thanks for letting me know that my solution worked...

Bernie