Thread: SUMIFS on Multiple Work Sheets

I have master purchase order work sheet, Master, where I have entered what I plan to order for the fall season. I now need to create 4 separate worksheet purchase orders for Sept, Oct, Nov & Dec deliveries based on my total units on the Master PO worksheet (I am on allocation).

My column headings are Style(E), Description (F), Color (G), Size (H), Total units (I), see example below. My data starts on row 2.

Style Description Color Size Total Units
1873 W BAILEY BLK 5 2
1873 W BAILEY BLK 6 4
1873 W BAILEY BLK 7 5

For my PO order for Sept, let’s say I order 1/5, 2/6, 2/7, for Oct I order 1/6, for Nov 1/6 & Dec 1/5, 1/6, 3/7. I need a formula that I can use that will subtract the units placed on my Sept, Oct, Nov & Dec purchase order worksheets from the Master PO worksheet so that I know how many units are left to order on the other purchase orders as I enter the units. Let’s say I create an additional column on the master PO called Balance (J). The formula I am seeking would give me be a balance of 1/5, 2/6, 3/7 left to order after entering the units for Sept.; the balance should be 0 after entering the Dec units. If I change any units on any of the Sept to Dec work orders, the formula would update the balances of units accordingly.

Any help would be deeply appreciated. Thanks.

Hi,

If I understood your problem, I think you can use vlookup. You just need to create a unique "name" for each item. I have done that by combining "Style", "Description", "Color" & "Size" for this purpose.

Please refer to the example attached

(I have included the IF & ISERROR functions just incase the not all the items in the master list appear in the monthly list. Else, they won't be needed.

Hope this helps

Do you really need a sheet for each Month?
Why not put all the sales on one sheet, and use filters to view the sales by month(s)

This keeps life simple and given you are using 2007 the only formula needed is
In Sheet "Master" F2

=E2-SUMIFS(SalesByMonth!F:F,SalesByMonth!B:B,A2,SalesByMonth!C:C,B2,SalesByMonth!D:D,C2,SalesByMonth!E:E,D2)

Drag/Fill Down

Hope this helps