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

1. ## 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!!!

2. ## 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. ## 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. ## Re: Data based on multiple criteria in initial table and correspondence table

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

5. ## 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. ## Re: Data based on multiple criteria in initial table and correspondence table

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

#### Thread Information

##### Users Browsing this Thread

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

#### 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