SOLVED

Setting up a table to pull totals from PO# and SKU#

%3CLINGO-SUB%20id%3D%22lingo-sub-2977746%22%20slang%3D%22en-US%22%3ESetting%20up%20a%20table%20to%20pull%20totals%20from%20PO%23%20and%20SKU%23%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2977746%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20different%20charts%20that%20I%20need%20to%20pull%20information%20from%20for%20my%20boss%20and%20I%20can%20not%20change%20the%20format%20for%20the%20loads%20excel%20sheet.%20I%20have%20tried%20to%20use%20the%20index-match%20formula%20but%20I%20have%20run%20into%20the%20issue%20where%20I%20have%20items%20shipped%20from%20a%20PO%23%20being%20shipped%20on%20separate%20dates%20and%20I%20can%20not%20figure%20out%20how%20to%20get%20to%20the%20formula%20to%20add%20the%20two%20amounts%20together.%20I%20will%20take%20any%20advice%20given.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emy%20boss%20wants%20a%20running%20total%20of%20what%20all%20items%20were%20ordered%20and%20shipped%20on%20each%20PO%23.%20I%20have%20attached%20an%20example%20work%20book%20and%20have%20highlighted%20and%20commented%20on%20the%20parts%20I%20am%20haveing%20issues%20with.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2977746%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2977776%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20up%20a%20table%20to%20pull%20totals%20from%20PO%23%20and%20SKU%23%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2977776%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1221860%22%20target%3D%22_blank%22%3E%40Ctucker29%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20use%20this%20formula%20in%20G12%3A%3C%2FP%3E%0A%3CP%3E%3DSUMIFS(LOADS%5B%5BAmount%20%5D%5D%2CLOADS%5BP.O.%20'%23%5D%2C%24B12%2CLOADS%5BSKU%20'%23%5D%2CF%242)%3C%2FP%3E%0A%3CP%3EOr%20should%202%20loads%20for%20the%20same%20PO%23%20and%20SKU%23%20on%20the%20same%20day%20be%20counted%20as%201%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2977822%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20up%20a%20table%20to%20pull%20totals%20from%20PO%23%20and%20SKU%23%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2977822%22%20slang%3D%22en-US%22%3Efor%20simplicity's%20sake%2C%20I%20had%20got%20rid%20of%20a%20few%20columns%20such%20as%20the%20truck%20trailer%20%23%20for%20each%20item%20shipped.%20I%20had%20tried%20your%20formula%20and%20received%20a%20spill%20error.%3C%2FLINGO-BODY%3E
New Contributor

I have two different charts that I need to pull information from for my boss and I can not change the format for the loads excel sheet. I have tried to use the index-match formula but I have run into the issue where I have items shipped from a PO# being shipped on separate dates and I can not figure out how to get to the formula to add the two amounts together. I will take any advice given.

 

my boss wants a running total of what all items were ordered and shipped on each PO#. I have attached an example work book and have highlighted and commented on the parts I am haveing issues with.

3 Replies

@Ctucker29 

You could use this formula in G12:

=SUMIFS(LOADS[[Amount ]],LOADS[P.O. '#],$B12,LOADS[SKU '#],F$2)

Or should 2 loads for the same PO# and SKU# on the same day be counted as 1?

for simplicity's sake, I had got rid of a few columns such as the truck trailer # for each item shipped. I had tried your formula and received a spill error.
best response confirmed by Ctucker29 (New Contributor)
Solution

@Ctucker29 

Hmm... I don't get that - here is the workbook with the modified formula.