Multiple Sums/Subtotal with changing number of cells between

[woodrrow]

Joe 2
John 1
Sara 2.5
Fred 1
Sam 8
SUM1
George 1
John 1
Joe 1
Lisa 8
SUM2

I can't find this answer anywhere. I want to be able to sum everything above SUM1 line, and everything above SUM2 line until it reaches SUM1. I have several hundred rows where I need to add a sum, but the number of cells between each one varies. How could I do this? The names start in A1, and the numbers start in B1.

Thanks for any and all help!

[TheN]

type =sum(

then select the range you wish to sum, while holding down the shift key, then end you parenthesis.

So, in your example, if that is starting in A1, in cell B6, you would end up with:

=sum(B1:B5)

and in cell B11

=sum(B6:B10)

Alternatively, you can manually type it in.

Hope this helps.

[woodrrow]

I want to be able to do that without manually entering a sum function into several hundred cells. Is there a way to highlight all of my "SUM rows", filter by that color, paste formula and remove filters? Is there a way to sum until adjacent cell hits a blank?

[TheN]

The closest thing I can think of to what you are describing is fairly resource heavy, but yes, it can be done.

Depending on how often you need to insert the values, this may or may not be a better solution:

in cell C1 in your given sample data:

Please Login or Register  to view this content.

It will not put it directly into the column, but it will show up only where your values should be. However, since it is dragged down as far as the data is, it will make your file size unnecessarily larger, and depending upon how much data you have, that might be a concern.

[woodrrow]

Finally found the answer here https://www.extendoffice.com/documen...til-blank.html

Thanks for your input though.

[Tony Valko]

Here's one way...

Data Range

	A	B	C
1	Name	Value	Total
2	John	1
3	Sara	2.5
4	Fred	1
5	Sam	8
6		SUM1	12.5
7	George	1
8	John	1
9	Joe	1
10	Lisa	8
11		SUM2	11

This formula entered in C2 and copied down:

=IF(COUNT(B2),"",SUM(B$2:B2)-SUM(C$1:C1))

[woodrrow]

Thanks Tony!

[Tony Valko]

You're welcome!