Hello,
I have been scratching my head for a week on this question.
I have two tables
- Table 1 - it contains a list of people, the problem they worked on, and how many interactions they had on the problem.
- Table 2 - it contains a list of people who owned and solved a problem.
A problem is C followed by a number
For example,
Table 1, R had in total 7 interactions on 4 problems (C1 =2 , C5 =1, C3 =1, and C4 = 3)
Table 2, R owned and solved 1 problem (C3)
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>
What I'm trying to write is a formula that gives me
- the number of interactions a owner did have (Table 1) on problems he didn't solve (Table 2).
In the example above, the formula will give me 6 (C1 =2 as it was owned by K , C5 =1 - owned by P, and C4 = 3 - owned by B)
I have tried to use SUMIFS with arrays, combination of SUM and IFS with arrays, etc. without success.
Any pointer, direction will be really helpful.
Thank you.
Issey
I have been scratching my head for a week on this question.
I have two tables
- Table 1 - it contains a list of people, the problem they worked on, and how many interactions they had on the problem.
- Table 2 - it contains a list of people who owned and solved a problem.
A problem is C followed by a number
For example,
Table 1, R had in total 7 interactions on 4 problems (C1 =2 , C5 =1, C3 =1, and C4 = 3)
Table 2, R owned and solved 1 problem (C3)
Owner | Problem | Interaction | Owner | Problem | ||
R | C1 | 2 | R | C3 | ||
K | C2 | 3 | A | C1 | ||
P | C2 | 6 | B | C4 | ||
R | C5 | 1 | K | C2 | ||
K | C1 | 7 | P | C5 | ||
R | C3 | 1 | C | C6 | ||
R | C4 | 3 | D | C7 | ||
P | C1 | 7 | P | C8 |
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>
What I'm trying to write is a formula that gives me
- the number of interactions a owner did have (Table 1) on problems he didn't solve (Table 2).
In the example above, the formula will give me 6 (C1 =2 as it was owned by K , C5 =1 - owned by P, and C4 = 3 - owned by B)
I have tried to use SUMIFS with arrays, combination of SUM and IFS with arrays, etc. without success.
Any pointer, direction will be really helpful.
Thank you.
Issey