Is there a way to generate a dynamic table of subtotals?

[JenniferMurphy]

Is there a way to generate a dynamic table of subtotals from the data in another table?

The table on the left contains donations made to several people during 2021. The table on the right shows the subtotals for each of these people. Is there a way to generate the table on the right dynamically -- that is, without me having to enter each name on its own row -- so that if I add a new donation to a new person in the table on the left, the table on the right will automatically grow a new row? Thanks

Donations & RMD.xlsx
	B	C	D	E	F	G
5	Date	To	Amount		Name	Year Total
6	2/03/21	Susie	$100		Freddie	$800
7	2/14/21	Freddie	$200		Johnny	$100
8	3/13/21	Freddie	$200		Molly	$700
9	4/01/21	Susie	$100		Susie	$400
10	5/22/21	Molly	$300		Total	$2,000
11	6/15/21	Molly	$200
12	6/19/21	Johnny	$100
13	7/07/21	Molly	$100
14	8/31/21	Freddie	$300
15	9/09/21	Susie	$200
16	9/21/21	Freddie	$100
17	12/22/21	Molly	$100
18	Total		$2,000
Test2

Cell Formulas
Range	Formula
G6:G9	G6	=SUMIFS(Table1[Amount],Table1[To],"=" & [@Name])
G10	G10	=SUBTOTAL(109,[Year Total])
D18	D18	=SUBTOTAL(109,[Amount])

 

[Alex Blakenburg]

I think it in unlikely that you will be able to get the result you want in a Table format without using code.
A pivot table is going to get closest to what you have.
You could use Dynamic array formulas but the output won't be in a table and hence you won't have an automated total line.

20220308 Summary Table Unique.xlsx
	B	C	D	E	F	G
5	Date	To	Amount		Name	Year Total
6	03-Feb-21	Susie	100		Susie	400
7	04-Feb-21	Freddie	200		Freddie	800
8	05-Feb-21	Freddie	200		Molly	700
9	06-Feb-21	Susie	100		Johnny	100
10	07-Feb-21	Molly	300		Additional	500
11	08-Feb-21	Molly	200
12	09-Feb-21	Johnny	100
13	10-Feb-21	Molly	100
14	11-Feb-21	Freddie	300
15	12-Feb-21	Susie	200
16	13-Feb-21	Freddie	100
17	14-Feb-21	Molly	100
18	08-Mar-22	Additional	500
19	Total		2500
Sheet2

Cell Formulas
Range	Formula
F6:F10	F6	=UNIQUE(Table1[To])
G6:G10	G6	=SUMIFS(Table1[Amount],Table1[To],F6#)
D19	D19	=SUBTOTAL(109,[Amount])
Dynamic array formulas.

 

[JenniferMurphy]

I think it in unlikely that you will be able to get the result you want in a Table format without using code.
A pivot table is going to get closest to what you have.
You could use Dynamic array formulas but the output won't be in a table and hence you won't have an automated total line.

That's what I was afraid of. I might take a shot at writing some code. I'll also try my hand at your solution.

Thanks

 

[Alex Blakenburg]

I will look forward to hearing what you come up with.

 

[Dave Patton]

N.B. No numbers in range G12:G20

Sumifs.xlsm
	A	B	C	D	E	F	G
1
2		Total		5200			5200
3
4
5		Date	To	Amount		Name	Year Total
6		3-Feb-21	Susie	100		Freddie	800
7		14-Feb-21	Freddie	200		George	1000
8		13-Mar-21	Freddie	200		Johnny	100
9		1-Apr-21	Susie	100		Molly	900
10		22-May-21	Molly	300		Susie	400
11		15-Jun-21	Molly	200		William	2000
12		19-Jun-21	Johnny	100
13		7-Jul-21	Molly	100
14		31-Aug-21	Freddie	300
15		9-Sep-21	Susie	200
16		21-Sep-21	Freddie	100
17		22-Dec-21	Molly	100
18		23-Dec-21	Molly	200
19		24-Dec-21	George	1000
20		22-Dec-21	William	2000
1d

Cell Formulas
Range	Formula
D2	D2	=SUBTOTAL(109,Table1[Amount])
G2	G2	=SUM(G6:G20)
F6:F11	F6	=SORT(UNIQUE(Table1[To]))
G6:G11	G6	=SUMIFS(Table1[Amount],Table1[To],F6#)
Dynamic array formulas.

 

[Dave Patton]

Sumifs.xlsm
	A	B	C	D	E	F	G
1
2		Total		4600			4600
3
4
5		Date	To	Amount		Name	Year Total
6		3-Feb-21	Susie	100		Bill	500
7		14-Feb-21	Freddie	200		Carole	1200
8		13-Mar-21	Freddie	200		Freddie	500
9		1-Apr-21	Susie	100		George	1000
10		22-May-21	Molly	300		Johnny	100
11		15-Jun-21	Molly	200		Molly	900
12		19-Jun-21	Johnny	100		Susie	400
13		7-Jul-21	Molly	100
14		9-Sep-21	Susie	200
15		21-Sep-21	Freddie	100
16		22-Dec-21	Molly	100
17		23-Dec-21	Molly	200
18		24-Dec-21	George	1000
19		31-Dec-21	Bill	500
20		31-Dec-21	Carole	1200
1d

Cell Formulas
Range	Formula
D2	D2	=SUBTOTAL(109,Table1[Amount])
G2	G2	=SUM(G6:INDEX(G:G,6+SUM(1/COUNTIF(Table1[To], Table1[To]))))
F6:F12	F6	=SORT(UNIQUE(Table1[To]))
G6:G12	G6	=SUMIFS(Table1[Amount],Table1[To],F6#)
Dynamic array formulas.

 

[Peter_SSs]

This result is not a formal table but acts like one in that it will expand/contract with the data in the original (formal) table, like Alex's.
This one includes the sorting of Names (like Dave's) and bottom Total row as shown in your desired results.

22 03 08.xlsm
	B	C	D	E	F	G
5	Date	To	Amount		Name	Year Total
6	3/02/2021	Susie	100		Freddie	800
7	14/02/2021	Freddie	200		Johnny	100
8	13/03/2021	Freddie	200		Molly	700
9	1/04/2021	Susie	100		Susie	400
10	22/05/2021	Molly	300		Total	2000
11	15/06/2021	Molly	200
12	19/06/2021	Johnny	100
13	7/07/2021	Molly	100
14	31/08/2021	Freddie	300
15	9/09/2021	Susie	200
16	21/09/2021	Freddie	100
17	22/12/2021	Molly	100
18	Total		2000
ST

Cell Formulas
Range	Formula
F6:F10	F6	=FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,SORT(UNIQUE(Table1[To])),"Total")&"</c></p>","//c")
G6:G10	G6	=SUMIFS(Table1[Amount],Table1[To],IF(F6#="Total","*",F6#))
D18	D18	=SUBTOTAL(109,[Amount])
Dynamic array formulas.

 

[RoryA]

Variation on Peter's if you have LET, LAMBDA and BYROW:

F6:

=LET(names,FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,SORT(UNIQUE(Table1[To])),"Total")&"</c></p>","//c"),CHOOSE({1,2},names,BYROW(names,LAMBDA(a,SUMIF(Table1[To],IF(a="Total","*",a),Table1[Amount])))))

 

[Peter_SSs]

Using some of Rory's changes but without the need for LAMBDA/BYROW?

=LET(To,Table1[To],names,FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,SORT(UNIQUE(To)),"Total")&"</c></p>","//c"),CHOOSE({1,2},names,SUMIF(To,IF(names="Total","*",names),Table1[Amount])))

 

[Dave Patton]

The =SUBTOTAL(109,[Amount]) obviously works. How is it entered?
My system will not accept the formula but =SUBTOTAL(109,Table1[Amount]) does work.