|
Sumifs - incorrect when using date in critera
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. |
Sumifs - incorrect when using date in critera
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. |
Sumifs - incorrect when using date in critera
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. |
Sumifs - incorrect when using date in critera
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. |
| All times are GMT +1. The time now is 02:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com