+ Reply to Thread
Results 1 to 6 of 6

Data based on multiple criteria in initial table and correspondence table

  1. #1
    Registered User
    Join Date
    11-07-2019
    Location
    NY
    MS-Off Ver
    2016
    Posts
    3

    Question Data based on multiple criteria in initial table and correspondence table

    Hello guys,

    I am really struggling and would appreciate any help on this!

    On my initial table, I have information about one person:
    - person #
    - country
    - grade
    - contract type (TR, FTA, TA)
    -> I need to have the corresponding amount for each person on each row

    The corresponding amount is in another table
    - contract type (only 2 contract types (FTA, TA) as TR = FTA)
    - country
    - grade
    - amount

    How can I get the amount in the first table based on the table of correspondence in the second table?

    Thank you so much for any support on this!!!
    Attached Files Attached Files

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

    Re: Data based on multiple criteria in initial table and correspondence table

    Your amounts in column D on the Table sheet are text values rather than numbers, so to convert them to numbers you can highlight column D, then click on Data | Text-to-Columns, and then click Finish. Then you can use this formula in E2 of the Staffing sheet:

    =SUMIFS(Table!$D:$D,Table!$B:$B,B2,Table!$C:$C,C2,Table!$A:$A,D2)

    Copy this down as required. You will get some zero values, as there are no entries for "TR" in your Table sheet.

    Hope this helps.

    Pete

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

    Re: Data based on multiple criteria in initial table and correspondence table

    Ah, I've just read your post again, in which you say the "TR" is the same as "FTA", so change the formula in E2 to this:

    =SUMIFS(Table!$D:$D,Table!$B:$B,B2,Table!$C:$C,C2,Table!$A:$A,IF(D2="TR","FTA",D2))

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    11-07-2019
    Location
    NY
    MS-Off Ver
    2016
    Posts
    3

    Re: Data based on multiple criteria in initial table and correspondence table

    Amazing, thank you Pete so much!!!!!!!!

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

    Re: Data based on multiple criteria in initial table and correspondence table

    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.

    Also, since you are new to the forum, you might 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

  6. #6
    Registered User
    Join Date
    11-07-2019
    Location
    NY
    MS-Off Ver
    2016
    Posts
    3

    Re: Data based on multiple criteria in initial table and correspondence table

    I cannot find solved with my phone - will try with the computer

+ 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] Extract data from table based on criteria applied to multiple columns
    By Feremartinez in forum Excel General
    Replies: 9
    Last Post: 11-21-2018, 12:34 PM
  2. [SOLVED] Selecting data from large table based on multiple interfering criteria
    By Gulddrengen in forum Excel General
    Replies: 4
    Last Post: 12-03-2016, 05:17 AM
  3. [SOLVED] Pull data from a table based on multiple criteria - old formula trying to use AND
    By tweaver in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-12-2016, 04:37 PM
  4. [SOLVED] Populate data on table based on multiple criteria and value selected from drop-down
    By hcyeap in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-26-2015, 07:45 AM
  5. Replies: 9
    Last Post: 12-03-2014, 06:17 PM
  6. [SOLVED] Help Summing Data from Table Based on Multiple Criteria
    By D. from So Cal in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-18-2014, 02:16 PM
  7. Enter data into specific row in data table based on multiple criteria
    By bberger1985 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-05-2013, 11:04 AM

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