+ Reply to Thread
Results 1 to 4 of 4

VLOOKUP Count values in multiple sheets

  1. #1
    Registered User
    Join Date
    11-12-2019
    Location
    Brisbane, Australia
    MS-Off Ver
    Microsoft Excel 2019
    Posts
    2

    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

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,548

    Re: VLOOKUP Count values in multiple sheets

    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

  3. #3
    Registered User
    Join Date
    11-12-2019
    Location
    Brisbane, Australia
    MS-Off Ver
    Microsoft Excel 2019
    Posts
    2

    Re: VLOOKUP Count values in multiple sheets

    Thank you Pete, that worked a treat.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,548

    Re: VLOOKUP Count values in multiple sheets

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] SUM VLOOKUP which calculates multiple values across multiple sheets
    By McCaughley7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-04-2016, 03:57 PM
  2. VLookup Formula for Inventory count with different/multiple Values
    By Sweden12 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-05-2014, 03:55 AM
  3. [SOLVED] Needing to count unique values across multiple sheets
    By TheTubaGeek in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-10-2014, 09:50 AM
  4. [SOLVED] count unique values across multiple sheets based on date
    By roninn75 in forum Excel General
    Replies: 8
    Last Post: 02-25-2014, 05:20 PM
  5. Vlookup function to return multiple values from multiple sheets
    By bsamant in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-13-2013, 11:31 AM
  6. Replies: 8
    Last Post: 07-13-2012, 06:00 AM
  7. Vlookup- multiple corresponding values (2 separate sheets)
    By mitelkm in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-14-2008, 12:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1