VLOOKUP Count values in multiple sheets

Hello,

I'm new to excel forumulas and was hoping for some assistance. Please also see tables below.

I'm trying to match the values in "item" and "item location" columns then return the "Total quantity value" back to the "remaining stock sheet" in the appropiate field e.g. stock at shop, stock in storage and then total stock. I've tried acheiving this with a VLOOKUP however it only returns the first value not the full count of "Purple" items. I can also only match on one column value not "Item" and "Item location".

If someone could please point me in the right direction it would be appreciated.

Thanks


VLOOKUP.PNG

You can use SUMIFS, along the lines of the following in B2 of the Remaining Stock sheet:

=SUMIFS(Quantity,Item,$A2,Item_Location,B$1)

where you will need to change the entries in B1 and C1 of the Remaining stock sheet to just Shop and Storage respectively, and the ranges for Item, Item_Location and Quantity relate to the columns in your New stock sheet. Copy the formula into C2, then copy both down as appropriate.

Hope this helps.

Pete

Thank you Pete, that worked a treat.

You're welcome.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete