Formula to add additional names to Sumifs list?

[Coyotex3]

Hello guys, so I have a sheet with the following datasets:

PERSONAL.xlsm
	A	B	C	D	E	F	G	H
Sheet3

PERSONAL.xlsm
	A	B	C	D	E	F	G	H	I
1	Contributor	Customer	Amount		Customer	Profit	Individual 1	Individual 2	Individual 3
2	Individual 1	Client A	100		Client A	930	100	130	450
3	Individual 2	Client B	200		Client B	1950	150	200	1500
4	Individual 3	Client C	300		Client C	650	250	100	300
5	Individual 4	Client A	250
6	Individual 1	Client B	150
7	Individual 2	Client C	100
8	Individual 3	Client A	450
9	Individual 4	Client B	100
10	Individual 1	Client C	250
11	Individual 2	Client A	130
12	Individual 3	Client B	1500
Sheet3

Cell Formulas
Range	Formula
F2:F4	F2	=SUMIFS(Table1[Amount],Table1[Customer],E2)
G2:I2,H3:I4	G2	=SUMIFS(Table1[Amount],Table1[Customer],$E2,Table1[Contributor],G$1)
G3:G4	G3	=SUMIFS(Table1[Amount],Table1[Customer],E3,Table1[Contributor],G$1)

The dataset on the left is updated daily and I would like to be able to dynamically update the dataset with the formula to account for any new clustomers which Individuals 1-3 worked with?

PERSONAL.xlsm
	A	B	C	D	E	F	G	H	I
1	Contributor	Customer	Amount		Customer	Profit	Individual 1	Individual 2	Individual 3
2	Individual 1	Client A	100		Client A	1030	100	130	450
3	Individual 2	Client B	200		Client B	2300	150	200	1500
4	Individual 3	Client C	300		Client C	773	250	100	300
5	Individual 4	Client A	250		Client D	1000	200	0	300
6	Individual 1	Client B	150		Client E	750	0	250	0
7	Individual 2	Client C	100
8	Individual 3	Client A	450
9	Individual 4	Client B	100
10	Individual 1	Client C	250
11	Individual 2	Client A	130
12	Individual 3	Client B	1500
13	Individual 4	Client C	123
14	Individual 5	Client A	100
15	Individual 6	Client E	500
16	Individual 7	Client D	400
17	Individual 5	Client B	350
18	Individual 6	Client F	200
19	Individual 7	Client G	250
20	Individual 5	Client F	350
21	Individual 6	Client G	205
22	Individual 7	Client D	100
23	Individual 1	Client D	200
24	Individual 2	Client E	250
25	Individual 3	Client D	300
Sheet3

Cell Formulas
Range	Formula
F2:F6	F2	=SUMIFS(Table1[Amount],Table1[Customer],E2)
G2:I2,H3:I6	G2	=SUMIFS(Table1[Amount],Table1[Customer],$E2,Table1[Contributor],G$1)
G3:G6	G3	=SUMIFS(Table1[Amount],Table1[Customer],E3,Table1[Contributor],G$1)

Lastly, if possible, would there be a way of seeing if any outside contributor(4-7) worked with Customer(A-C) on a separate list?

 

[Jeffrey Mahoney]

To answer the first part, add this formula to Cell G1 and remove the headers from H1 to the right
=TRANSPOSE(UNIQUE(Table1[Contributor]))

 

[Coyotex3]

To answer the first part, add this formula to Cell G1 and remove the headers from H1 to the right
=TRANSPOSE(UNIQUE(Table1[Contributor]))

Hi Jeffrey, adding this formula to Cell G1 gives me a unique list of contributors. I only want to see the additional contributors *IF* they worked on any of the original customers which would be Clients A,B, and C on the list. I would like to see the additional contributors displayed on a separate list.

 

[Jeffrey Mahoney]

This, of course, includes all the possible combinations, but you could parse out the needed data.

Book5
	P	Q	R
1	Contributor	Client	Combo Count
2	Individual 1	Client A	1
3	Individual 2	Client B	1
4	Individual 3	Client C	1
5	Individual 4	Client A	1
6	Individual 1	Client B	1
7	Individual 2	Client C	1
8	Individual 3	Client A	1
9	Individual 4	Client B	1
10	Individual 1	Client C	1
11	Individual 2	Client A	1
12	Individual 3	Client B	1
13	Individual 4	Client C	1
14	Individual 5	Client A	1
15	Individual 6	Client E	1
16	Individual 7	Client D	2
17	Individual 5	Client B	1
18	Individual 6	Client F	1
19	Individual 7	Client G	1
20	Individual 5	Client F	1
21	Individual 6	Client G	1
22	Individual 1	Client D	1
23	Individual 2	Client E	1
24	Individual 3	Client D	1
Sheet1

Cell Formulas
Range	Formula
P2:Q24	P2	=UNIQUE(Table1[[Contributor]:[Customer]])
R2:R24	R2	=COUNTIFS(Table1[Contributor],P2,Table1[Customer],Q2)
Dynamic array formulas.

 

[Jeffrey Mahoney]

I had to add another column to your table to help with know that a client was an original client in the formula. So the formula in H1 uses that column to filter the Contributors

Book5
	A	B	C	D	E	F	G	H	I	J	K	L
1	Contributor	Customer	Amount	Original Client		Customer	Profit	Individual 1	Individual 2	Individual 3	Individual 4	Individual 5
2	Individual 1	Client A	100	TRUE		Client A	1030	100	130	450	250	100
3	Individual 2	Client B	200	TRUE		Client B	2300	150	200	1500	100	350
4	Individual 3	Client C	300	TRUE		Client C	773	250	100	300	123	0
5	Individual 4	Client A	250	TRUE		Client D	1000	200	0	300	0	0
6	Individual 1	Client B	150	TRUE		Client E	750	0	250	0	0	0
Sheet1

Cell Formulas
Range	Formula
H1:L1	H1	=TRANSPOSE(UNIQUE(FILTER(Table1[Contributor],Table1[Original Client]=TRUE,"")))
G2:G6	G2	=SUMIFS(Table1[Amount],Table1[Customer],F2)
H2:L6	H2	=SUMIFS(Table1[Amount],Table1[Customer],$F2,Table1[Contributor],H$1)
D2:D6	D2	=OR([@Customer]="Client A",[@Customer]="Client B",[@Customer]="Client C")
Dynamic array formulas.

 

[Fluff]

Another option for your 1st question

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Contributor	Customer	Amount		Customer	Profit	Individual 1	Individual 2	Individual 3
2	Individual 1	Client A	100		Client A	1030	100	130	450
3	Individual 2	Client B	200		Client B	2300	150	200	1500
4	Individual 3	Client C	300		Client C	773	250	100	300
5	Individual 4	Client A	250		Client D	1000	200	0	300
6	Individual 1	Client B	150		Client E	750	0	250	0
7	Individual 2	Client C	100
8	Individual 3	Client A	450
9	Individual 4	Client B	100
10	Individual 1	Client C	250
11	Individual 2	Client A	130
12	Individual 3	Client B	1500
13	Individual 4	Client C	123
14	Individual 5	Client A	100
15	Individual 6	Client E	500
16	Individual 7	Client D	400
17	Individual 5	Client B	350
18	Individual 6	Client F	200
19	Individual 7	Client G	250
20	Individual 5	Client F	350
21	Individual 6	Client G	205
22	Individual 7	Client D	100
23	Individual 1	Client D	200
24	Individual 2	Client E	250
25	Individual 3	Client D	300
Master

Cell Formulas
Range	Formula
E2:E6	E2	=UNIQUE(FILTER(Table1[Customer],ISNUMBER(XMATCH(Table1[Contributor],G1:I1))))
F2:F6	F2	=SUMIFS(Table1[Amount],Table1[Customer],E2#)
G2:G6	G2	=SUMIFS(Table1[Amount],Table1[Customer],E2#,Table1[Contributor],G1)
H2:H6	H2	=SUMIFS(Table1[Amount],Table1[Customer],E2#,Table1[Contributor],H1)
I2:I6	I2	=SUMIFS(Table1[Amount],Table1[Customer],E2#,Table1[Contributor],I1)
Dynamic array formulas.

 

[Fluff]

For the 2nd part how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Contributor	Customer	Amount		Customer	Profit	Individual 1	Individual 2	Individual 3
2	Individual 1	Client A	100		Client A	1030	100	130	450
3	Individual 2	Client B	200		Client B	2300	150	200	1500
4	Individual 3	Client C	300		Client C	773	250	100	300
5	Individual 4	Client A	250		Client D	1000	200	0	300
6	Individual 1	Client B	150		Client E	750	0	250	0
7	Individual 2	Client C	100
8	Individual 3	Client A	450
9	Individual 4	Client B	100
10	Individual 1	Client C	250
11	Individual 2	Client A	130		Individual 4	Client A
12	Individual 3	Client B	1500		Individual 5	Client A
13	Individual 4	Client C	123		Individual 4	Client B
14	Individual 5	Client A	100		Individual 5	Client B
15	Individual 6	Client E	500		Individual 4	Client C
16	Individual 7	Client D	400		Individual 7	Client D
17	Individual 5	Client B	350		Individual 7	Client D
18	Individual 6	Client F	200		Individual 6	Client E
19	Individual 7	Client G	250
20	Individual 5	Client F	350
21	Individual 6	Client G	205
22	Individual 7	Client D	100
23	Individual 1	Client D	200
24	Individual 2	Client E	250
25	Individual 3	Client D	300
Master

Cell Formulas
Range	Formula
E2:E6	E2	=UNIQUE(FILTER(Table1[Customer],ISNUMBER(XMATCH(Table1[Contributor],G1:I1))))
F2:F6	F2	=SUMIFS(Table1[Amount],Table1[Customer],E2#)
G2:G6	G2	=SUMIFS(Table1[Amount],Table1[Customer],E2#,Table1[Contributor],G1)
H2:H6	H2	=SUMIFS(Table1[Amount],Table1[Customer],E2#,Table1[Contributor],H1)
I2:I6	I2	=SUMIFS(Table1[Amount],Table1[Customer],E2#,Table1[Contributor],I1)
E11:F18	E11	=SORT(FILTER(Table1[[Contributor]:[Customer]],(ISNUMBER(XMATCH(Table1[Customer],E2#)))*(ISNA(XMATCH(Table1[Contributor],G1:I1)))),2)
Dynamic array formulas.

 

[Coyotex3]

I had to add another column to your table to help with know that a client was an original client in the formula. So the formula in H1 uses that column to filter the Contributors

Book5
	A	B	C	D	E	F	G	H	I	J	K	L
1	Contributor	Customer	Amount	Original Client		Customer	Profit	Individual 1	Individual 2	Individual 3	Individual 4	Individual 5
2	Individual 1	Client A	100	TRUE		Client A	1030	100	130	450	250	100
3	Individual 2	Client B	200	TRUE		Client B	2300	150	200	1500	100	350
4	Individual 3	Client C	300	TRUE		Client C	773	250	100	300	123	0
5	Individual 4	Client A	250	TRUE		Client D	1000	200	0	300	0	0
6	Individual 1	Client B	150	TRUE		Client E	750	0	250	0	0	0
Sheet1

Cell Formulas
Range	Formula
H1:L1	H1	=TRANSPOSE(UNIQUE(FILTER(Table1[Contributor],Table1[Original Client]=TRUE,"")))
G2:G6	G2	=SUMIFS(Table1[Amount],Table1[Customer],F2)
H2:L6	H2	=SUMIFS(Table1[Amount],Table1[Customer],$F2,Table1[Contributor],H$1)
D2:D6	D2	=OR([@Customer]="Client A",[@Customer]="Client B",[@Customer]="Client C")
Dynamic array formulas.

Hi Jeffrey, thank you for this! This almost gets us to what I was going for. Fluff's solution below used a formula on Column E to account for all the customers the contributors worked with.

 

[Coyotex3]

Another option for your 1st question

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Contributor	Customer	Amount		Customer	Profit	Individual 1	Individual 2	Individual 3
2	Individual 1	Client A	100		Client A	1030	100	130	450
3	Individual 2	Client B	200		Client B	2300	150	200	1500
4	Individual 3	Client C	300		Client C	773	250	100	300
5	Individual 4	Client A	250		Client D	1000	200	0	300
6	Individual 1	Client B	150		Client E	750	0	250	0
7	Individual 2	Client C	100
8	Individual 3	Client A	450
9	Individual 4	Client B	100
10	Individual 1	Client C	250
11	Individual 2	Client A	130
12	Individual 3	Client B	1500
13	Individual 4	Client C	123
14	Individual 5	Client A	100
15	Individual 6	Client E	500
16	Individual 7	Client D	400
17	Individual 5	Client B	350
18	Individual 6	Client F	200
19	Individual 7	Client G	250
20	Individual 5	Client F	350
21	Individual 6	Client G	205
22	Individual 7	Client D	100
23	Individual 1	Client D	200
24	Individual 2	Client E	250
25	Individual 3	Client D	300
Master

Cell Formulas
Range	Formula
E2:E6	E2	=UNIQUE(FILTER(Table1[Customer],ISNUMBER(XMATCH(Table1[Contributor],G1:I1))))
F2:F6	F2	=SUMIFS(Table1[Amount],Table1[Customer],E2#)
G2:G6	G2	=SUMIFS(Table1[Amount],Table1[Customer],E2#,Table1[Contributor],G1)
H2:H6	H2	=SUMIFS(Table1[Amount],Table1[Customer],E2#,Table1[Contributor],H1)
I2:I6	I2	=SUMIFS(Table1[Amount],Table1[Customer],E2#,Table1[Contributor],I1)
Dynamic array formulas.

Fluff, thank you for your help once more! This is perfect and accomplishes what I was trying to accomplish.

As a side note(open ended), do you think there is a more efficient way of accomplishing what I'm trying to do? The data on the right does not need to follow a specific format. Essentially I have is a list of my team members and a list of my clients(Individuals 1-3, and Clients 1-3). And I would just like to account for an instance where other team members work on my clients, and when my team members work on clients that are not on my list. The only way I could think of accomplishing this was by setting up the data in the method above. Open to more efficient methods if any.

Thank you!

 

[Fluff]

You maybe better of with PQ or pivots, but I don't know anything about them so could be wrong.