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