I have a file that includes several sheets. On the first sheet, I am trying
to pull in total requirement quantities for part numbers based on the date
which are located on another sheet.

This is the formula that I am using
=SUMIFS('Material needed for open orders'!$D$3:$D$401,'Material needed for
open orders'!$A$3:$A$401,'Raw Material Planning'!$B2,'Material needed for
open orders'!$C$3:$C$401,"'Material needed for open orders'!$O$1",'Material
needed for open orders'!$C$3:$C$401,"<='Material needed for open
orders'!$P$1")

My first sheet looks like this


"Value
Stream" PART # Material KanBan Inv. Level Pcs OH Mat Req Wk R

Slice Rings 20330 10018 66000 24600 41400 207.0 0.0
20756 10214 45000 0 45000 43.2 0.0
20843 10043 50000 14701 35299 15.2 0.0

My second sheet is this

(a) (c) (d) (e) (f)
(g)
LPROD Sch Sh Dt LQORD LQSHP LCLAS Remaining Qty
1-1924 01/30/09 10000 0 JA 10000
1-40 12/04/08 5113 0 JA 5113
1-40 12/04/08 104000 0 JA 104000
1-40 12/17/08 104000 0 JA 104000
1-40 12/17/08 104000 0 JA 104000
9-40 11/10/08 100 42 JG 58


Columns O & P referenced in the formula are date fields to show weekly
buckets. I can get the formula to work properly without using the dates, but
when I add the date criteria, all of my totals are 0.

Your formula is incorrect and SUMIFS is correct, when using a criteria like
a cell you need to use the ampersand

,""&O1

not

,"O1"


where it will look for the literal string O1

the ampersand tells it to look for the contents of the cell

--


Regards,


Peo Sjoblom

"Kathy" wrote in message
...
I have a file that includes several sheets. On the first sheet, I am
trying
to pull in total requirement quantities for part numbers based on the date
which are located on another sheet.

This is the formula that I am using
=SUMIFS('Material needed for open orders'!$D$3:$D$401,'Material needed for
open orders'!$A$3:$A$401,'Raw Material Planning'!$B2,'Material needed for
open orders'!$C$3:$C$401,"'Material needed for open
orders'!$O$1",'Material
needed for open orders'!$C$3:$C$401,"<='Material needed for open
orders'!$P$1")

My first sheet looks like this


"Value
Stream" PART # Material KanBan Inv. Level Pcs OH Mat Req Wk R

Slice Rings 20330 10018 66000 24600 41400 207.0 0.0
20756 10214 45000 0 45000 43.2 0.0
20843 10043 50000 14701 35299 15.2 0.0

My second sheet is this

(a) (c) (d) (e) (f)
(g)
LPROD Sch Sh Dt LQORD LQSHP LCLAS Remaining Qty
1-1924 01/30/09 10000 0 JA 10000
1-40 12/04/08 5113 0 JA 5113
1-40 12/04/08 104000 0 JA 104000
1-40 12/17/08 104000 0 JA 104000
1-40 12/17/08 104000 0 JA 104000
9-40 11/10/08 100 42 JG 58


Columns O & P referenced in the formula are date fields to show weekly
buckets. I can get the formula to work properly without using the dates,
but
when I add the date criteria, all of my totals are 0.

Thank you for your quick response. Where do I put the ampersand when I am
referencing a cell on a different sheet?

Kathy

"Peo Sjoblom" wrote:

Your formula is incorrect and SUMIFS is correct, when using a criteria like
a cell you need to use the ampersand

,""&O1

not

,"O1"


where it will look for the literal string O1

the ampersand tells it to look for the contents of the cell

--


Regards,


Peo Sjoblom

"Kathy" wrote in message
...
I have a file that includes several sheets. On the first sheet, I am
trying
to pull in total requirement quantities for part numbers based on the date
which are located on another sheet.

This is the formula that I am using
=SUMIFS('Material needed for open orders'!$D$3:$D$401,'Material needed for
open orders'!$A$3:$A$401,'Raw Material Planning'!$B2,'Material needed for
open orders'!$C$3:$C$401,"'Material needed for open
orders'!$O$1",'Material
needed for open orders'!$C$3:$C$401,"<='Material needed for open
orders'!$P$1")

My first sheet looks like this


"Value
Stream" PART # Material KanBan Inv. Level Pcs OH Mat Req Wk R

Slice Rings 20330 10018 66000 24600 41400 207.0 0.0
20756 10214 45000 0 45000 43.2 0.0
20843 10043 50000 14701 35299 15.2 0.0

My second sheet is this

(a) (c) (d) (e) (f)
(g)
LPROD Sch Sh Dt LQORD LQSHP LCLAS Remaining Qty
1-1924 01/30/09 10000 0 JA 10000
1-40 12/04/08 5113 0 JA 5113
1-40 12/04/08 104000 0 JA 104000
1-40 12/17/08 104000 0 JA 104000
1-40 12/17/08 104000 0 JA 104000
9-40 11/10/08 100 42 JG 58


Columns O & P referenced in the formula are date fields to show weekly
buckets. I can get the formula to work properly without using the dates,
but
when I add the date criteria, all of my totals are 0.

Peo,

Your solution works, thank you so much!!!!

"Peo Sjoblom" wrote:

Your formula is incorrect and SUMIFS is correct, when using a criteria like
a cell you need to use the ampersand

,""&O1

not

,"O1"


where it will look for the literal string O1

the ampersand tells it to look for the contents of the cell

--


Regards,


Peo Sjoblom

"Kathy" wrote in message
...
I have a file that includes several sheets. On the first sheet, I am
trying
to pull in total requirement quantities for part numbers based on the date
which are located on another sheet.

This is the formula that I am using
=SUMIFS('Material needed for open orders'!$D$3:$D$401,'Material needed for
open orders'!$A$3:$A$401,'Raw Material Planning'!$B2,'Material needed for
open orders'!$C$3:$C$401,"'Material needed for open
orders'!$O$1",'Material
needed for open orders'!$C$3:$C$401,"<='Material needed for open
orders'!$P$1")

My first sheet looks like this


"Value
Stream" PART # Material KanBan Inv. Level Pcs OH Mat Req Wk R

Slice Rings 20330 10018 66000 24600 41400 207.0 0.0
20756 10214 45000 0 45000 43.2 0.0
20843 10043 50000 14701 35299 15.2 0.0

My second sheet is this

(a) (c) (d) (e) (f)
(g)
LPROD Sch Sh Dt LQORD LQSHP LCLAS Remaining Qty
1-1924 01/30/09 10000 0 JA 10000
1-40 12/04/08 5113 0 JA 5113
1-40 12/04/08 104000 0 JA 104000
1-40 12/17/08 104000 0 JA 104000
1-40 12/17/08 104000 0 JA 104000
9-40 11/10/08 100 42 JG 58


Columns O & P referenced in the formula are date fields to show weekly
buckets. I can get the formula to work properly without using the dates,
but
when I add the date criteria, all of my totals are 0.