Data based on multiple criteria in initial table and correspondence table

[gege-lolo]

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

[Pete_UK]

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

[Pete_UK]

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

[gege-lolo]

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

[Pete_UK]

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

[gege-lolo]

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