formula to calculate inventory, vlookup?

Maybe vlookup is what I am looking for?

I have sheets with multiple columns, and 1000+ rows.

Column B is a SKU number. Column F is quantity on hand.

In a separate sheet (Sheet2) i want to look up each sku, and calculate the quantity on hand for every location that has that SKU.

Would be even BETTER, if I could get excel to list data from an extra column (column A is location)

example:

Sheet2 should look like

45568(sku)
(location) | (quantity)
(location) | (quantity)
(location) | (quantity)
_____________
Total quantity on hand

and I want to repeat this for every sku. sheet two would likely take up two columbs per sku, showing location in first, on hand in the second, then calculate a total

I will look into this when I get home.

Thank you for your kindness!

I am unsure what to look for in your sample sheet, can you elaborate

Still hoping someone can help with this.
I am attaching an example of what I need. Sheet1 will have data, and sheet2 I want to calculate totals by SKU. Like I said, I want excel to show me a list of locations with the SKU I choose, then show the amount on hand per location, then tally a total.

example.xlsx

thats the data that I want to have shown. open sheet1 for the data ill be using.

ahh! thank you both!

Marcol, that is beautiful. Now I need to figure out how to apply that to my spreadsheets at work. I will investigate!

before I mark this thread as SOLVED, can someone help me be able to implement the work that Marcol did? is it a pivot table? where do I need to start?

It isn't a pivot table.

First you need a List of Unique SKUs, I used Remove Duplicates on a copy of your list in Sheet1 Column B
From that you can get the matching descriptions, and use that for the drop-down Validation List.

All this can be simplified by using dynamic named ranges to handle any variable amount of SKUs.

1/. Does each location have the same amount of SKUs?
2/. Can you have new and/or SKUs deleted?

Not each location has the same amount of SKUS
I was able to work out the table on the right from your sheet2, but the dropdown list is giving me trouble

Okay look back later tonight, I'll prepare a workbook for you, got to go for now.

Thanks again Margol for taking the time. I guess my example should have been more fitting to what I am going to use this formula for.
QOH is typically the F column. and each sheet that I work with has thousands of rows. Like I said, for the most part, each store will have the same SKUs, but some locations will have more or less depending on which are deactivated per location. I guess I just need guidance on how to format the dropdown and table that it produces.

EDIT: didnt see that you had responded. Will read through your last post.

Thank you!!