Formula to Sum a Group based on Blank Spaces between Groups

[secrestj]

Forum members: I have exhausted my internet/forum searches and AI formula assistance on my issue and turning to you. Thank you for your help in advance. I am looking for formula and not VBA. I am using Office 365.

I want to have a formula that I can drag down rows (in Column C) that will return the sum of a group of another column (Column B) based on a blank cell between the groups (Column B). The groups vary in size (my example shows 3 and 4 categories), with some having 2 to 7 categories within a grouping. I have oversimplified then table for the Forum Members use. I will change the formula as necessary to differing columns or changes in formula operations (e.g. sum to percentage calculations), but I need to BASE formula to start with. I have tried numerous ways and failed. Also, looking for the formula to be in a single cell and not rely on a 'helper column'.

For the cells in Column C between totals, I want it to return blank (or ""), as I want to be able to drop the formula into any spreadsheet provided to me and put the formula at the top and drag to obtain quick results to report on.

I hope this is not too much a lift and the members can help. Thank you again. See sample table below.

 

[Fluff]

I am using Office 365.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=IF(B2="",SUM(C$2:C2)-SUM(D$1:D1),"")

 

[hagia_sofia]

Another option:

=LET(
r,C2:C19,
a,SCAN(0,r,LAMBDA(x,y,IF(y="",0,x+y))),
b,VSTACK(0,DROP(a,-1)),
IF(r<>"","",b))

 

[JamesCanale]

pile on:

MrExcelPlayground23.xlsx
	A	B	C	D
1	Type	Status	amount
2	a	x	20
3	b	x	40
4	c	x	30
5	total 1			90
6	a	t	20
7	b	t	10
8	c	t	10
9	total 2			40
10	q	x	50
11	w	x	10
12	e	x	30
13	r	x	40
14	total 3			130
15	q	t	10
16	w	t	10
17	e	t	20
18	r	t	50
19	total 4			90
Sheet28

Cell Formulas
Range	Formula
D2	D2	=IF(C2<>"","",SUM(DROP(C1:C$2,IFNA(XMATCH(,C1:C$2,0,-1),0))))
D3:D19	D3	=IF(C3<>"","",SUM(DROP(C$2:C2,IFNA(XMATCH(,C$2:C2,0,-1),0))))

Someday AI will be better at this. But today is not that day.

 

[secrestj]

Wow - as usual, the Forum Member pull through. I thank Fluff for the recommendation on updating my account and formula; I am running with JamesCanale's version, as I understand it - thank you. However, I just ran hagia_sofia version and amazing - I am going to do much more research in breaking down this formula and its potential future applications - I am always learning from everyones' various approaches to the same problem.

Thank you for each of your efforts. This is a great forum.

 

[Fluff]

Glad we could help & thanks for the feedback.

I am running with JamesCanale's version, as I understand it

Does that mean you don't understand the formula I suggested?

 

[secrestj]

Glad we could help & thanks for the feedback.


Does that mean you don't understand the formula I suggested?

No - you misunderstood. I went with the JamesCanale version, as I believe I can adapt it for my other formula changes I need. I am still working it to see if it works for me. Thank you for your follow up.