SUM formula that resizes?

[Excel Curious]

Is there a SUM formula that can automatically resize as you copy and paste
it, such that ,it will sum all the previous rows up to the previous SUM
formula?

What I want to do is very similar to a subtotal, but without any common item
to subtotal by.

EXAMPLE:
1] 7
2] 5
3] 6
4] <SUM formula here for rows 1-3> RESULT 18
5] 2
6] 8
7] <copy above formula from 4, but have SUM rows 5-6> RESULT 10
8] 3
9] <copy above formula from 4, but have SUM row 8> RESULT 3

Thanks

 

[Tom Hutchins]

Just use the AutoSum button on the Standard toolbar. It will behave the way
you described.

Hope this helps,

Hutch

 

[Excel Curious]

It only sort of works. I occasionaly also have blank rows, so it ends up only
adding everything below the blank.

Just use the AutoSum button on the Standard toolbar. It will behave the way
you described.

Hope this helps,

Hutch

Is there a SUM formula that can automatically resize as you copy and paste
it, such that ,it will sum all the previous rows up to the previous SUM
formula?

What I want to do is very similar to a subtotal, but without any common item
to subtotal by.

EXAMPLE:
1] 7
2] 5
3] 6
4] <SUM formula here for rows 1-3> RESULT 18
5] 2
6] 8
7] <copy above formula from 4, but have SUM rows 5-6> RESULT 10
8] 3
9] <copy above formula from 4, but have SUM row 8> RESULT 3

Thanks

 

[Tom Hutchins]

Okay, try this user-defined function. You need to paste this function into a
VBA module in your workbook.

Public Function SumMore() As Double
Dim x As Long, Ttl As Double
Ttl = 0
For x = 1 To Application.Caller.Row - 1
If Left(Application.Caller.Offset(-x, 0).Formula, 5) = "=SUM(" Then
Exit For
ElseIf IsNumeric(Application.Caller.Offset(-x, 0).Value) Then
Ttl = Ttl + Application.Caller.Offset(-x, 0).Value
End If
Next x
SumMore = Ttl
End Function

You can call it manually by putting the following fornula in any cell:

=SumMore()+(NOW()*0)

This macro puts that formula in the active cell. You could attach this macro
to a custom toolbar button:

Sub AddSumMore()
ActiveCell.Formula = "=SumMore()+(NOW()*0)"
End Sub

The function would have to be added to every workbook where you want to use
it. It will ignore text and empty cells. If you are new to macros, you may
find this Jon Peltier link helpful:

http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch

It only sort of works. I occasionaly also have blank rows, so it ends up only
adding everything below the blank.

Just use the AutoSum button on the Standard toolbar. It will behave the way
you described.

Hope this helps,

Hutch

Is there a SUM formula that can automatically resize as you copy and paste
it, such that ,it will sum all the previous rows up to the previous SUM
formula?

What I want to do is very similar to a subtotal, but without any common item
to subtotal by.

EXAMPLE:
1] 7
2] 5
3] 6
4] <SUM formula here for rows 1-3> RESULT 18
5] 2
6] 8
7] <copy above formula from 4, but have SUM rows 5-6> RESULT 10
8] 3
9] <copy above formula from 4, but have SUM row 8> RESULT 3

Thanks