excelexcel-formulasumcriteria

Matching criteria in multiple tables in Excel


I need your help on the following issue. I can't combine criteria from different tables. For example, I have 10 "J" personnel. I want to distribute these personnel in the second table according to the department volume which is in the first table. You can see the result I want to achieve in the "Result!" column.

Name Qty
Personnel "J" 10
Personnel "K" 5

Table 1:

Department Volume
A 70
B 50
C 30
D 120

Table 2:

Department Name Result!
A Personnel "J" 7
B Personnel "K" 5
C Personnel "J" 3

I need to distribute personnel quantity according to the department volume. For example personnel "J" only works in "A" and "C" departments. Total volume of these departments are 100. (A=%70, C=%30). Total J quantity is 10. A = 10 x 70% = 7, B = 10 x 30% = 3. Personnel "K" only works in "B" department. Total volume of this department is 50. (B=%100).Total K quantity is 5. so B = 5 x 100% = 5

Thank you in advance!


Solution

  • I've solved the problem:

    =SUMIF(Table1[Department];"A";Table1[Volume])/
    SUMPRODUCT(SUMIFS(Table1[Volume];Table1[Department];[Department]);
    --([Staff]="Personnel J"))*SUMIF(Table0[Staff];"Personnel J";Table0[Total])