Create total row below table and make the total row expand or contract

[bearcub]

I am using the following 2 formulas to create a table that summarizes commissions that are being paid. I would like make the table dynamic so I do have a number of blank rows below the data. Right now I have to insert blank rows to account an expanding table.

Also, I am getting a weird Total Value after the last table row.

I am using the unique function to spill the people being paid this month and the following formula to add all the orders that should be paid to them

List all the people being paid this month:
IF(UNIQUE(Table6[Rep Name])="","",UNIQUE(Table6[Rep Name]))

Add all the orders on this sheets:
BYROW(Q6#,LAMBDA(r,SUMIF(A:A,r,B:B)))

Is there a way to create a formula that perform both of these and create a dynamic "Total" row that expands and contracts with the # of people being paid:

Here is the table that I am referring to in column P & Q. The third column is reconcile the names on the summary sheet going to payroll.

 

[Eric W]

I don't know what your worksheet actually looks like, but you can do something like this:

Book2
	A	B	C	D	E
1	Name	Amt		Name	Amt
2	Aaron	1		Aaron	10
3	Bob	2		Bob	11
4	Aaron	3		Corey	4
5	Corey	4		David	12
6	David	5		Ian	18
7	Aaron	6
8	David	7		TOTAL	55
9	Ian	8
10	Bob	9
11	Ian	10
12
Sheet2

Cell Formulas
Range	Formula
D2:E8	D2	=LET(t,SORT(UNIQUE(FILTER(A2:A20,A2:A20<>""))),s,SUMIF(A:A,t,B:B),r,ROWS(t),sq,SEQUENCE(r+2),CHOOSE({1,2},IFS(sq<=r,t,sq=r+1,"",1,"TOTAL"),IFS(sq<=r,s,sq=r+1,"",1,SUM(s))))
Dynamic array formulas.

 

[bearcub]

That you, that'll work. The formula did create Calc# error in the column that is pulling from the summary sheet. Once I change the # to a number the formula worked fine.

For some reason,

BYROW(Q6#, LAMBDA(r, SUMIF(A:A, r, B:B)))

caused a CALC# error which I thought was odd. I think it had to do with Q6#, once I converted it to a number range the formula worked. Is there any to make this formula dynamic?

BYROW(Q6#,LAMBDA(r,SUMIF('Comm_June 24 US Summary'!F:F,r,'Comm_June 24 US Summary'!I:I)))

 

[Eric W]

Sorry, my version of Excel doesn't have BYROW, so I can't troubleshoot it. But if I understand it correctly, then this:

BYROW(Q6#, LAMBDA(r, SUMIF(A:A, r, B:B)))

is equivalent to this:

=SUMIF(A:A,Q6#,B:B)

assuming Q6# is a 1-column range.

 

[bearcub]

That is good to know, thank you. However, when I do that I get a spill error because the array formula in column P is referencing 2 columns. I think I can live with having to update the range manually.