How do I sum rows until first blank row?

[MikeMcCollister]

I would like to sum rows in a table until the first blank row and then stop. I've tried

=SUM(TRIMRANGE(B2:$B$22))

but it keeps going to the end of the range. Any idea how to do that?

Note that I will only do this when the "Description" is set to "-Various-" but I wanted to keep it simple for this forum.

Thanks,

Mike

 

[hagia_sofia]

Hello, maybe something like:

=LET(
d,A2:A22,
v,B2:B22,
g,SCAN(0,--(d="-Various-"),LAMBDA(a,b,a+b)),
s,GROUPBY(g,v,SUM,,0),
IF((d<>"")*(v=""),XLOOKUP(g,INDEX(s,0,1),INDEX(s,0,2)),""))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C
1
2	-Various-		10
3		1
4		2
5		3
6		4
7	-Various-		18
8		5
9		6
10		7
11	-Various-		17
12		8
13		9
14	-Various-		75
15		10
16		11
17		12
18		13
19		14
20		15
21
22
23	Total	120
Sheet6

Cell Formulas
Range	Formula
C2:C22	C2	=MAP(A2:A22,B2:B22,LAMBDA(x,y,IF(x="-various-",SUM(y:INDEX(y:B22,IFNA(XMATCH(x,INDEX(A:A,ROW(x)+1):A22),y))),"")))
B23	B23	=SUM(B2:B22)
Dynamic array formulas.

 

[MikeMcCollister]

Thanks for the inspiration. I have come up with a fairly simple formula:

=IF(A2="-Various-", SUM(B2:XLOOKUP(, B3:B$22, B3:B$22)), "")

It just fails if the last group of numbers does not have a blank cell at B22 or above. I'll play around some more to see if there is something more elegant.

 

[MikeMcCollister]

I added the B$22 to XLOOKUP so that it will work if the list goes to the end.

=IF(A2="-Various-", SUM(B2:XLOOKUP(, B3:B$22, B3:B$22, B$22)), "")

 

[Fluff]

Glad you sorted it & thanks for the feedback.