Help with Formula

[Krista1228]

Hi, 

I have an inventory spreadsheet that displays 2 columns of different Names. These names are ID's of product we buy from 2 different companies (hence why there are 2 separate columns). We need to have both ID's in the spreadsheet because this is how we receive them based off of the info we get from the companies. BUT, since they are technically the same product, we need to know how many we have of all of them together. The first issue I am having is that I cant seem to get my received column to find how many items we have received based on both of the separate ID's. I achieved this in the past by using sumifs function: 

=SUMIFS(Received_Q3[Received],Received_Q3[Order ID],[@[Order ID]]) 

where received_Q3 is a table on another sheet

[Received] is the name of the column of the received amount in the received table

[Order ID] is the name of item in the column on the received table 

and [@[Order ID]]) relates to the order ID on the current table I'm sending the information to. 

Because I now have 2 "Order ID columns" with separate ID's, I cant seem to get sumifs to look up the same way by using 2 different column ID's. 

I feel like it is something easily done that I am missing? 

I'm not a pro at excel at all and I've learned a lot of stuff a long the way in the past 2 years of doing this by asking questions, looking up info/videos myself and getting accustomed to the specific formula I'm using at that time. 

I have another issue that I am facing in the the receiving table where ive used vlookup to lookup information from the order ID column which comes from a data validation list from a named range. Since I now have 2 id columns, obviously vlookup will not look up the information in the second column as well since vlookup only works on the first column in a table. is there any other formula/function to work around this? 

I have attached my test workbook that I've been toying around with for the past couple of days to try to get things to work. 

Thanks! 

[#2]

@Krista1228 

To sum the received amounts using the two vendor ID's, you could try:

=SUMPRODUCT(--(([@[Atlantic Plywood ID]]<>"")*(Received_Q3[Atlantic ID]=[@[Atlantic Plywood ID]])+([@[Premium Plywood ID]]<>"")*(Received_Q3[Premium ID]=[@[Premium Plywood ID]])>0),Received_Q3[Received])

But, it looks like your received table is using the vendor ID to pull in the corresponding ID you use internally from the inventory tracking? Could your inventory tracking table just sumif using your internal ID since it appears that field exists in both tables (maybe you weren't because of the issue w/ pulling the ID into the received table)?

=SUMIF(Received_Q3[ID],[@ID],Received_Q3[Received])

For the ID, you could try (you indicate you have office 365, but if you didn't it would require you to hit Ctrl+Shift+Enter):

=INDEX(Inventory_Master[ID],MATCH(1,--(([@[Atlantic ID]]<>"")*(Inventory_Master[Atlantic Plywood ID]=[@[Atlantic ID]])+([@[Premium ID]]<>"")*(Inventory_Master[Premium Plywood ID]=[@[Premium ID]])>0),0))

Going forward, I would probably consider setting up a table to serve solely as a crosswalk between the vendor codes and my product codes (vendor name, vendor product ID, my product ID). Then, in the received table, have vendor name, vendor id, my id, and look up the vendor name+vendor id in the table and return my id (google "excel multiple criteria lookup" and I think you should find some examples). Then, all of the downstream formulas solely use my product ID's for lookups/calculations (crosswalk the vendor codes right away, and then just use my internal unique codes from there so there's only one source cell for lookup/sumif functions).

Also, if you have office 365, I would think you would have problems with formulas like this one in your consumption table (cell C2, but the rest of the table is the same) due to a change in excel's default calculation method from "implicit intersection" to "dynamic array." 

=VLOOKUP(A:A,Inventory_Master,3,FALSE)

You might try:

=VLOOKUP(A2,Inventory_Master,3,FALSE)

[#3]

@JMB17 

Ok,

So I've spent the past several days working on creating my own ID's. Unfortunately the ID's that you thought were "internal" were simply just the vendors descriptions (Identifications) of the product (not our own-we didn't have any). So now that I've made a vendor crosswalk for everything and have tried my best to come up with "our own unique ID's" my next issue is, when I count the inventory- we go by the vendor's numbers because they are already stamped on the side of the the sheetstock material (and if not then we write them). The guys downstairs want to keep vendor specific products separate from each other because if there is an issue with an order we need to easily identify which vendor it was from (and also makes it super easy to count and track for me). Now I have no way of tracking the inventory in my inventory tracking sheet because everything is based off of "our ID." I used to be able to ctrl F and type in an ID to find it quickly and mark how many we have, now I cant. I also set the tracking sheet up to have a column to show the vendor and ID's that correspond to "our ID" because we need these numbers for ordering (which I also do). The guys upstairs however, who engineer everything, only need to know how much of a specific product we have in stock (hence why I needed to come up with a way to combine the products from different vendors/ids into 1 calculation). This also tells us exactly how much of that product we have in stock despite being from different vendors, which is great when I do the sales because I'm now not trying to look up 4 different vendors for 1 item. So I guess what it comes down to is I need a way to be able to track the inventory based off of the individual vendor ID's but also need to be able to see how much of that item we have across several vendor ID's. Does that make sense? I've attached the updated workbook. 

[#4]

@Krista1228 

Power Pivot Solution