Need help with a index and match function combined with a sumif function

%3CLINGO-SUB%20id%3D%22lingo-sub-1303721%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20a%20index%20and%20match%20function%20combined%20with%20a%20sumif%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1303721%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20for%20help%20with%20creating%20a%20formula%20that%20will%20pull%20data%20from%20another%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20what%20I'm%20looking%20for%20is%20on%20the%20%22placing%20orders%20template%22%20(which%20is%20a%20spreadsheet%20I'm%20using%20to%20see%20if%20we%20need%20to%20buy%20materials%20that%20we%20are%20low%20on%20in%20inventory).%20I%20need%20a%20column%20to%20pull%20data%20from%20the%20other%20spreadsheet%20which%20is%20a%20log%20that%20contains%20all%20of%20our%20purchase%20orders%20that%20shows%20also%20the%20status%20of%20these%20orders%2C%20which%20is%20located%20on%20the%20column%20that%20is%20labeled%20%22status%22%20which%20is%20highlighted%20in%20orange.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20I%20was%20originally%20trying%20was%20an%20IF%20statement%20nesting%20an%20index%20and%20match%20function%20to%20pull%20the%20orders%20where%20the%20status%20is%20%22waiting%20for%20confirmation%2C%22%20%22confirmed%2C%22%20%22shipping%20date%20confirmed%2C%22%20combined%20with%20a%20sumif%20to%20sum%20those%20qtys%20that%20are%20linked%20to%20that%20particular%20part%20number%20(labeled%20%22P%2FN%22%20in%20the%20placing%20orders%20template%22)%2C%20when%20a%20part%20number%20is%20populated%20on%20the%20%22placing%20orders%20template.%22%20The%20whole%20intention%20I%20have%20with%20this%20is%20to%20pull%20the%20qtys%20that%20I%20have%20actually%20placed%20to%20our%20suppliers%2C%20to%20show%20what%20we%20currently%20have%20%22on%20order%22%20in%20our%20supplier%20pipeline.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%20I%20tried%20using%20an%20IF%20statement%20along%20with%20an%20index%20and%20match%20function%20as%20the%20logical%20statement%20to%20identify%20the%20status%20of%20the%20order%2C%20along%20with%20a%20sumif%20to%20sum%20the%20associated%20part%20number%20if%20it%20meets%20those%20constraints%20from%20the%20index%20and%20match.%20Although%2C%20this%20was%20unsuccessful.%20Not%20sure%20if%20the%20logic%20I'm%20using%20is%20compatible%20with%20the%20syntax%20I'm%20using.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20someone%20can%20give%20me%20a%20hand%20on%20this%20problem%2C%20that%20would%20be%20much%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20there%20needs%20to%20be%20any%20additional%20explanation.%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1303721%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Contributor

Hello,

 

I'm looking for help with creating a formula that will pull data from another worksheet.

 

So, what I'm looking for is on the "placing orders template" (which is a spreadsheet I'm using to see if we need to buy materials that we are low on in inventory). I need a column to pull data from the other spreadsheet which is a log that contains all of our purchase orders that shows also the status of these orders, which is located on the column that is labeled "status" which is highlighted in orange.

 

The formula I was originally trying was an IF statement nesting an index and match function to pull the orders where the status is "waiting for confirmation," "confirmed," "shipping date confirmed," combined with a sumif to sum those qtys that are linked to that particular part number (labeled "P/N" in the placing orders template"), when a part number is populated on the "placing orders template." The whole intention I have with this is to pull the qtys that I have actually placed to our suppliers, to show what we currently have "on order" in our supplier pipeline.

 

Again I tried using an IF statement along with an index and match function as the logical statement to identify the status of the order, along with a sumif to sum the associated part number if it meets those constraints from the index and match. Although, this was unsuccessful. Not sure if the logic I'm using is compatible with the syntax I'm using.

 

If someone can give me a hand on this problem, that would be much appreciated.

 

Let me know if there needs to be any additional explanation.

Thanks.

 

 

 

0 Replies