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

Pierre,

=SUMPRODUCT((Sheet1!$A$2:$A$1000="ABC")*(LEFT(Shee t1!$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" wrote in message
...
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

On Feb 11, 4:05*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Pierre,

=SUMPRODUCT((Sheet1!$A$2:$A$1000="ABC")*(LEFT(Shee t1!$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

Thank you!!!!!

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

Bernie