How to sum a column based on criteria from current and other tables?

[Issey]

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)

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Owner[/TD]
[TD="width: 64"]Problem[/TD]
[TD="width: 64"]Interaction[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Owner[/TD]
[TD="width: 64"]Problem[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]C1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]C3[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]C2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]C1[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]C2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]C4[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]C5[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]K[/TD]
[TD]C2[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]C1[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]P[/TD]
[TD]C5[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]C3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]C6[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]C4[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]C7[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]C1[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]P[/TD]
[TD]C8[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[AlanY]

try

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	Owner	Problem	Interaction		Owner	Problem		Owner	Interaction
2	R	C1	2		R	C3		R	6
3	K	C2	3		A	C1
4	P	C2	6		B	C4
5	R	C5	1		K	C2
6	K	C1	7		P	C5
7	R	C3	1		C	C6
8	R	C4	3		D	C7
9	P	C1	7		P	C8
Sheet1

Cell Formulas
Range	Formula
I2	=SUMIF(A2:A9,H2,C2:C9)-IFERROR(VLOOKUP(VLOOKUP(H2,E2:F9,2,0),B2:C9,2,0),0)

 

[Issey]

Hello AlanY,

Thank you for your quick help!!!

I'm going to review this formula and try to understand the logic behind it.

I have read to use VLOOKUP, the columns need to be ordered, is it right?

Cheers,
Issey

 

[AlanY]

you're welcome.

that vlookup()s in the formula don't needed to be sorted. the ,0 at the end to force an exact match and the iferror() trap down the no match.

the first part, sumif() is to add up all the interactions

the second part of lookup()s, first to find if the problem been solved, VLOOKUP(H2,E2:F9,2,0) to get the problem reference, in this example C3.

then the outer lookup() use C3 to look for the no of interactions in B2:C9 and to be taken away from the total

 

[Issey]

Thank you again!!!

Cheers,
Issey

 

[Issey]

Hello AlanY,

I was wondering why I was looking at arrays, and it looks I didn't give the correct problem description.

The problem I originally gave was when an owner solved only one problem.

In the table 2, R only solved C3.

[TABLE="class: cms_table"]
<tbody>[TR]
[TD="width: 64"]Owner[/TD]
[TD="width: 64"]Problem[/TD]
[TD="width: 64"]Interaction[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Owner[/TD]
[TD="width: 64"]Problem[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]C1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]C3[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]C2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]C1[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]C2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]C4[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]C5[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]K[/TD]
[TD]C2[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]C1[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]P[/TD]
[TD]C5[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]C3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]C6[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]C4[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]C7[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]C1[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]P[/TD]
[TD]C8[/TD]
[/TR]
</tbody>[/TABLE]
But an owner can actually solve multiple problems.
For example, now, R solved two problems (C3 and C4), so the number of interactions should be 3.

[TABLE="class: cms_table"]
<tbody>[TR]
[TD="width: 64"]Owner[/TD]
[TD="width: 64"]Problem[/TD]
[TD="width: 64"]Interaction[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Owner[/TD]
[TD="width: 64"]Problem[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]C1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]C3[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]C2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]C1[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]C2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]C4[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]C5[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]K[/TD]
[TD]C2[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]C1[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]P[/TD]
[TD]C5[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]C3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]C6[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]C4[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]C7[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]C1[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]P

Z[/TD]
[TD]C8

C10[/TD]
[/TR]
</tbody>[/TABLE]

Any pointers that could help me?

Cheers,
Rithy

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H
1	Owner	Problem	Interaction		Owner	Problem	Owner	Interaction
2	R	C1	2		R	C3	R	3
3	K	C2	3		A	C1
4	P	C2	6		R	C4
5	R	C5	1		K	C2
6	K	C1	7		P	C5
7	R	C3	1		C	C6
8	R	C4	3		D	C7
9	P	C1	7		P	C8
10					Z	C10
11
Data

In H2 control+shift+enter, not just enter:

=SUM(IF($A$2:$A$9=G2,IF(ISNA(MATCH($B$2:$B$9,IF($E$2:$E$10=G2,$F$2:$F$10),0)),$C$2:$C$9)))

 

[Issey]

Thank you for your help again!!! This is exactly what I was trying to do
Cheers,

 

[Aladin Akyurek]

Thank you for your help again!!! This is exactly what I was trying to do
Cheers,

You are welcome.