Hello,
Hopefully somebody can help me, I am trying to creating a sumifS formula or something
I am looking to sum Qtys by date and item number from one sheet and match up to the item on a separate sheet:
Sheet1 looks the following:
A | B| C | D | E | F | G | H | I | J | K | L | M
|Parent Item| Material| Qty Per| Yield | Scrap% | Usage |Notes | Nov | Dec |Jan | Feb | Mar
Item1 1-1234 1 1.000 10% 1.100 =SumIFs
Item1 1-1234 1 0.900 10% 0.990
Item1 1-1236 1 0.050 10% 0.055
Item1 1-1237 2 0.100 10% 0.210
Item1 1-1238 1 0.120 10% 0.132
Item2 1-1243 1 0.114 10% 0.126
Item3 1-1239 10% -
Item4 1-1241 1 0.014 10% 0.015
Item5 1-1242 1 0.024 10% 0.026
Item6 1-1243 10% -
Item7 1-1244 1 1.206 10% 1.327
Item8 1-1245 1 0.283 10% 0.311
Item9 1-1243 1 0.066 10% 0.073
item10 1-1247 1 0.065 10% 0.071
item10 1-1248 1 1.000 0% 1.000
Item11 1-1249 1 0.056 10% 0.062
Item12 1-1243 1 0.039 10% 0.043
Item13 1-1251 1 0.227 10% 0.249
Sheet 2 looks like this:
A | B | C| D| E| F| G| H| I| J| K
Requested Date| Date 2 |Date 3 |Job#| Order#| Customer | Item | Raw Available | Qty Ordered | Qty Shipped | Qty Balance
11/01/19 07/08/19 07/08/19 1 Customer 1 Item1 100 0 100 2,343 23.43 2,343
11/01/19 07/12/19 05/01/19 1 Customer 1 Item1 1000 0 1000 23,940 23.94 23,940
11/02/19 07/15/19 07/15/19 1 Customer 1 Item1 100 0 100 155 1.55 155
01/29/20 07/19/19 06/07/19 1 Customer 1 Item1 4000 0 4000 82,080 20.52 82,080
12/01/19 07/24/19 07/24/19 1 Customer 1 Item1 50 16 34 11,543 230.85 7,849
10/28/19 07/26/19 05/01/19 1 Customer 1 Item1 1000 0 1000 23,940 23.94 23,940
02/07/20 07/26/19 06/19/19 1 Customer 1 Item1 3000 0 3000 71,820 23.94 71,820
10/28/19 07/30/19 07/25/19 2 Customer 2 Item2 500 0 500 5,795 11.59 5,795
10/28/19 08/05/19 08/05/19 3 Customer 3 item2 15000 0 15000 12,000 0.80 12,000
11/08/19 08/05/19 03/28/19 4 customer 5 Item3 652 0 652 27,397 42.02 27,397
10/28/19 08/05/19 08/05/19 5 Customer 10 item4 50 0 50 477 9.54 477
08/09/19 08/09/19 08/09/19 6 Customer 4 item6 500 564 -64 350 0.70 -45
08/09/19 08/09/19 08/09/19 6 Customer 4 item6 500 550 -50 350 0.70 -35
10/28/19 08/12/19 08/12/19 7 Customer 2 item7 45 0 45 236 5.25 236
10/28/19 08/12/19 08/12/19 8 Customer 10 item8 360 0 360 1,793 4.98 1,793
10/28/19 08/13/19 07/29/19 9 Customer 3 item9 4000 3,168 832 62,360 15.59 12,971
10/28/19 08/14/19 08/14/19 10 Customer 4 item10 60 0 60 881 14.68 881
12/12/19 08/14/19 08/14/19 11 Customer 10 item10 1000 0 1000 9,110 9.11 9,110
10/28/19 08/14/19 08/14/19 11 Customer 10 item10 1 0 1 0 0.00 0
10/28/19 08/14/19 08/14/19 11 Customer 10 item10 25 0 25 380 15.21 380
So I am looking for a sumifs formula that would go in sheet1 Cell H2 that look at sheet 2 and sums the values in column K Based on the Item Number matching (Sheet1 | Column A : Matches sheet 2 | Column G) and the date on Sheet2 | Column A : November |
Please Help
TroyB
Hopefully somebody can help me, I am trying to creating a sumifS formula or something
I am looking to sum Qtys by date and item number from one sheet and match up to the item on a separate sheet:
Sheet1 looks the following:
A | B| C | D | E | F | G | H | I | J | K | L | M
|Parent Item| Material| Qty Per| Yield | Scrap% | Usage |Notes | Nov | Dec |Jan | Feb | Mar
Item1 1-1234 1 1.000 10% 1.100 =SumIFs
Item1 1-1234 1 0.900 10% 0.990
Item1 1-1236 1 0.050 10% 0.055
Item1 1-1237 2 0.100 10% 0.210
Item1 1-1238 1 0.120 10% 0.132
Item2 1-1243 1 0.114 10% 0.126
Item3 1-1239 10% -
Item4 1-1241 1 0.014 10% 0.015
Item5 1-1242 1 0.024 10% 0.026
Item6 1-1243 10% -
Item7 1-1244 1 1.206 10% 1.327
Item8 1-1245 1 0.283 10% 0.311
Item9 1-1243 1 0.066 10% 0.073
item10 1-1247 1 0.065 10% 0.071
item10 1-1248 1 1.000 0% 1.000
Item11 1-1249 1 0.056 10% 0.062
Item12 1-1243 1 0.039 10% 0.043
Item13 1-1251 1 0.227 10% 0.249
Sheet 2 looks like this:
A | B | C| D| E| F| G| H| I| J| K
Requested Date| Date 2 |Date 3 |Job#| Order#| Customer | Item | Raw Available | Qty Ordered | Qty Shipped | Qty Balance
11/01/19 07/08/19 07/08/19 1 Customer 1 Item1 100 0 100 2,343 23.43 2,343
11/01/19 07/12/19 05/01/19 1 Customer 1 Item1 1000 0 1000 23,940 23.94 23,940
11/02/19 07/15/19 07/15/19 1 Customer 1 Item1 100 0 100 155 1.55 155
01/29/20 07/19/19 06/07/19 1 Customer 1 Item1 4000 0 4000 82,080 20.52 82,080
12/01/19 07/24/19 07/24/19 1 Customer 1 Item1 50 16 34 11,543 230.85 7,849
10/28/19 07/26/19 05/01/19 1 Customer 1 Item1 1000 0 1000 23,940 23.94 23,940
02/07/20 07/26/19 06/19/19 1 Customer 1 Item1 3000 0 3000 71,820 23.94 71,820
10/28/19 07/30/19 07/25/19 2 Customer 2 Item2 500 0 500 5,795 11.59 5,795
10/28/19 08/05/19 08/05/19 3 Customer 3 item2 15000 0 15000 12,000 0.80 12,000
11/08/19 08/05/19 03/28/19 4 customer 5 Item3 652 0 652 27,397 42.02 27,397
10/28/19 08/05/19 08/05/19 5 Customer 10 item4 50 0 50 477 9.54 477
08/09/19 08/09/19 08/09/19 6 Customer 4 item6 500 564 -64 350 0.70 -45
08/09/19 08/09/19 08/09/19 6 Customer 4 item6 500 550 -50 350 0.70 -35
10/28/19 08/12/19 08/12/19 7 Customer 2 item7 45 0 45 236 5.25 236
10/28/19 08/12/19 08/12/19 8 Customer 10 item8 360 0 360 1,793 4.98 1,793
10/28/19 08/13/19 07/29/19 9 Customer 3 item9 4000 3,168 832 62,360 15.59 12,971
10/28/19 08/14/19 08/14/19 10 Customer 4 item10 60 0 60 881 14.68 881
12/12/19 08/14/19 08/14/19 11 Customer 10 item10 1000 0 1000 9,110 9.11 9,110
10/28/19 08/14/19 08/14/19 11 Customer 10 item10 1 0 1 0 0.00 0
10/28/19 08/14/19 08/14/19 11 Customer 10 item10 25 0 25 380 15.21 380
So I am looking for a sumifs formula that would go in sheet1 Cell H2 that look at sheet 2 and sums the values in column K Based on the Item Number matching (Sheet1 | Column A : Matches sheet 2 | Column G) and the date on Sheet2 | Column A : November |
Please Help
TroyB
