Unique with Sort but exluding rows

[wildturkey]

Morning All

I'm hoping someone could tweak the following to exclude people who are marked as 'Seas or DD' in colum H please - any pointers gratefully accepted

=LET(Uni,UNIQUE(G:G),Tot,SUMIFS(F:F,G:G,Uni),INDEX(SORT(CHOOSE({1,2},Uni,Tot),2,-1),SEQUENCE(MIN(ROWS(Uni),2000)),{1,2}))

Test Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Transaction Type	Date	Document Number	Due Date	Age	Open Balance	Vendor	Due Date			DARREN	99399.64
2	Bill	10/12/2023	287310 SH	10/12/2023	31	589.96	JAMIE	31/01/2024			ASHLEY	9698.52
3	Bill	16/12/2023	287569 SH	16/12/2023	25	709.66	JAMIE	31/01/2024			HARRY	9079.08
4	Bill	23/12/2023	287787 SH	23/12/2023	18	721.36	JAMIE	31/01/2024			RYAN	7240.36
5	Bill	19/12/2023	19065	31/01/2024	-21	72	NATHAN	31/01/2024			JONATHAN	7194
6	Bill	02/01/2024	60427	02/01/2024	8	132	CARL	29/02/2024			EMILY	6670.89
7	Bill	16/01/2024	A102017876-0124	29/02/2024	-50	6670.89	EMILY	29/02/2024			LAURA	4917.22
8	Bill	12/12/2023	2023A1/1573	29/02/2024	-50	1425.6	CONNOR	31/01/2024			BRADLEY	4582.73
9	Bill	07/12/2023	10006	07/12/2023	34	7194	JONATHAN	31/01/2024			CALLUM	3810.43
10	Bill	01/01/2024	84006487	01/01/2024	9	27	LAURA	Seas or DD			EMMA	3563.25
11	Bill	01/01/2024	84006172	01/01/2024	9	60	LAURA	Seas or DD			AARON	3044.22
12	Bill	01/01/2024	84006296	01/01/2024	9	60	LAURA	Seas or DD			JAMIE	2020.98
13	Bill	01/01/2024	84006488	01/01/2024	9	162	LAURA	Seas or DD			CONNOR	1425.6
14	Bill	01/01/2024	84006490	01/01/2024	9	135	LAURA	Seas or DD			CARL	132
15	Bill	01/01/2024	84006489	01/01/2024	9	279	LAURA	Seas or DD			NATHAN	72
16	Bill	01/01/2024	84006491	01/01/2024	9	99	LAURA	Seas or DD			Vendor	0
17	Bill	01/01/2024	84006483	01/01/2024	9	72	LAURA	Seas or DD			0	0
18	Bill	02/01/2024	84008364	02/01/2024	8	1035.79	LAURA	Seas or DD
19	Bill	02/01/2024	84008365	02/01/2024	8	538.25	LAURA	Seas or DD
20	Bill	02/01/2024	84008025	02/01/2024	8	382.16	LAURA	Seas or DD
21	Bill	02/01/2024	84008353	02/01/2024	8	772.7	LAURA	Seas or DD
22	Bill	02/01/2024	84008363	02/01/2024	8	1308.77	LAURA	Seas or DD
23	Bill Credit	09/01/2024	84011940	09/01/2024	1	-51	LAURA	Seas or DD
24	Bill	09/01/2024	84011747	09/01/2024	1	60.55	LAURA	Seas or DD
25	Bill Credit	09/01/2024	84011876	09/01/2024	1	-24	LAURA	Seas or DD
26	Bill	18/12/2023	227948	18/12/2023	23	79.08	HARRY	31/01/2024
27	Bill	19/12/2023	2001705	19/12/2023	22	9000	HARRY	31/01/2024
28	Bill	01/12/2023	4995	31/01/2024	-21	286.49	CALLUM	31/01/2024
29	Bill	01/12/2023	4996	31/01/2024	-21	1796.62	CALLUM	31/01/2024
30	Bill	08/12/2023	5025	31/01/2024	-21	363.48	CALLUM	31/01/2024
31	Bill	15/12/2023	5063	31/01/2024	-21	1030.62	CALLUM	31/01/2024
32	Bill	04/01/2024	5096	29/02/2024	-50	324.82	CALLUM	29/02/2024
33	Bill	05/01/2024	5106	29/02/2024	-50	8.4	CALLUM	29/02/2024
34	Bill	06/02/2024	526132318-0224	06/02/2024	-27	3563.25	EMMA	Seas or DD
35	Bill	31/12/2023	INV-10310	30/01/2024	-20	3044.22	AARON	31/01/2024
36	Bill	16/11/2023	INV-1248	16/11/2023	55	4439.3	RYAN	31/12/2023
37	Bill	24/11/2023	INV-1252	24/11/2023	47	2657.06	RYAN	31/12/2023
38	Bill	12/12/2023	INV-1272	12/12/2023	29	144	RYAN	31/01/2024
39	Bill	07/01/2020	10752 2017	07/01/2020	###	32985.28	DARREN	Seas or DD
40	Bill	08/02/2021	11167 2017	08/02/2021	###	31055.39	DARREN	Seas or DD
41	Bill	21/04/2021	10825 2018	21/04/2021	994	35358.97	DARREN	Seas or DD
42	Bill	13/12/2023	21862	13/12/2023	28	3476.43	ASHLEY	31/01/2024
43	Bill	19/12/2023	21886	19/12/2023	22	2640.95	ASHLEY	31/01/2024
44	Bill	27/12/2023	21903	27/12/2023	14	3581.14	ASHLEY	31/01/2024
45	Bill	28/12/2023	2.023E+11	28/12/2023	13	2955.88	BRADLEY	31/01/2024
46	Bill	28/12/2023	2.023E+11	28/12/2023	13	1626.85	BRADLEY	31/01/2024
47
report624

Cell Formulas
Range	Formula
K1:L17	K1	=LET(Uni,UNIQUE(G:G),Tot,SUMIFS(F:F,G:G,Uni),INDEX(SORT(CHOOSE({1,2},Uni,Tot),2,-1),SEQUENCE(MIN(ROWS(Uni),2000)),{1,2}))
E2	E2	=31
F2	F2	=589.96
E3	E3	=25
F3	F3	=709.66
E4	E4	=18
F4	F4	=721.36
E5,E28:E31	E5	=-21
F5,F17	F5	=72
E6,E18:E22	E6	=8
F6	F6	=132
E7:E8,E32:E33	E7	=-50
F7	F7	=6670.89
F8	F8	=1425.6
E9	E9	=34
F9	F9	=7194
E10:E17	E10	=9
F10	F10	=27
F11:F12	F11	=60
F13	F13	=162
F14	F14	=135
F15	F15	=279
F16	F16	=99
F18	F18	=1035.79
F19	F19	=538.25
F20	F20	=382.16
F21	F21	=772.7
F22	F22	=1308.77
E23:E25	E23	=1
F23	F23	=-51
F24	F24	=60.55
F25	F25	=-24
E26	E26	=23
F26	F26	=79.08
E27,E43	E27	=22
F27	F27	=9000
F28	F28	=286.49
F29	F29	=1796.62
F30	F30	=363.48
F31	F31	=1030.62
F32	F32	=324.82
F33	F33	=8.4
E34	E34	=-27
F34	F34	=3563.25
E35	E35	=-20
F35	F35	=3044.22
E36	E36	=55
F36	F36	=4439.3
E37	E37	=47
F37	F37	=2657.06
E38	E38	=29
F38	F38	=144
E39	E39	=1464
F39	F39	=32985.28
E40	E40	=1066
F40	F40	=31055.39
E41	E41	=994
F41	F41	=35358.97
E42	E42	=28
F42	F42	=3476.43
F43	F43	=2640.95
E44	E44	=14
F44	F44	=3581.14
E45:E46	E45	=13
F45	F45	=2955.88
F46	F46	=1626.85
Dynamic array formulas.

 

[wildturkey]

Twigged sorry

=LET(Uni,UNIQUE(FILTER(G:G,H:H<>"seas or DD")),Tot,SUMIFS(F:F,G:G,Uni),INDEX(SORT(CHOOSE({1,2},Uni,Tot),2,-1),SEQUENCE(MIN(ROWS(Uni),2000)),{1,2}))

 

[Fluff]

As you have 365 you could also use this, which will return the header of a blank row

=LET(Uni,UNIQUE(FILTER(G2:G10000,(H2:H10000<>"seas or DD")*(G2:G10000<>""))),Tot,SUMIFS(F:F,G:G,Uni),TAKE(SORT(HSTACK(Uni,Tot),2,-1),2000))

 

[wildturkey]

As you have 365 you could also use this, which will return the header of a blank row

=LET(Uni,UNIQUE(FILTER(G2:G10000,(H2:H10000<>"seas or DD")*(G2:G10000<>""))),Tot,SUMIFS(F:F,G:G,Uni),TAKE(SORT(HSTACK(Uni,Tot),2,-1),2000))

And that does tidy it up nicely thank you!!

 

[Fluff]

You're welcome & thanks for the feedback.