Sum of a dynamic number of rows

[LeeB123]

Hello,

My first post. Currently, I manually paste in my formula for each month to calculate the sum of the "Count" rows and then manually update the range of rows (N4:N6 etc). This is a pain and I am sure it can be easier.
The screen shot is an example, but in reality there are often thousands of rows, making the setting of the row range time consuming and prone to errors. The observation is that the header of the rows is always the same, set to "Count" in the example.

In laymen's terms, I need to dynamically adjust the range according to whether the "Count" header is for that particular month.
For example, pasting the dynamic count formula in to cell N33, I would want it to find the "Count" header of cell N22 and therefore chose the range of N23:N32 dynamically.

Many thanks in advance to you clever folks.

Lee

 

[sxhall]

This will go through column N and when it finds the word count it will add in the formula to sum the cells. Added in a check that if there is a value next to 'Total' then it will skip and move to the next.

Relies on there being a empty cell between each section as in your example...

Sub AddSum()

Dim SRow As Integer
Dim Lrow As Integer

For Each i In Range("N:N")

    If i.Value = "Count" Then
    
        SRow = i.Row
        If Range("N" & SRow).End(xlDown).Offset(0, -1).Value = "Total" Then 'Checks to see if the formula or total is already there
        
        Else
        
        Lrow = Range("N" & SRow).End(xlDown).Row
        Range("N" & SRow).End(xlDown).Offset(1, 0).Formula = "=SUM(N" & SRow & ":N" & Lrow & ")"
        
        End If
        
    End If
    
Next i

End Sub

Results from my mock up of your image...

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(r,INDEX(N:N,ROW()-1),SUM(INDEX(N:N,XMATCH("Count",N$1:r,,-1)):r))

 

[RobP]

@Fluff - thats a pretty cunning formula - and whilst I can sort of understand that your searching backwards from the last row of data - I can't wrap my head around the syntax at the backend.

INDEX(N:N,XMATCH("Count",N$1:r,,-1)):r

At the frontend, "r" returns "12" eg. the data from the row above. so

N$1:r

is reading as:

N$1:12

Is that correct, or is r somehow returning a "Range address" in disguise... ?

then the :r at the end I can't fathom as a range either ..

Any clarity would be good to attempt to stop my headspin ..

cheers
Rob

 

[Fluff]

r is the range address, not the row number, so it's N12 rather than 12

 

[Peter_SSs]

Another possibility would be to put the first formula in like this where the '6' values in the formula are the row immediately above the first Total row.
So, for your sample data with the formula in N7 it would be:
=SUM(N$1:N6)-2*SUMIF(M$1:M6,"Total",N$1:N6)

Then copy/paste that formula to all the other 'Total' rows

 

[RobP]

So I played a bit more.. and substituted r with N12 instead.

=LET(r,INDEX(N:N,ROW()-1),INDEX(N:N,XMATCH("Count",N$1:N12,,-1)):N12)

Result is it misses the last row of data for me (12 in this case). I must be missing something.

=INDEX(N:N,ROW()-1)

just gives me "12" rather than "N12" as you mentioned - so there's some theory somewhere I'm not grasping...

 

[Fuji]

vba

Sub test()
    Dim x, i&
    x = Filter([transpose(if((m1:m10000="ID")+(m1:m10000="TOTAL"),row(1:10000)))], False, 0)
    For i = 1 To UBound(x)
        If (UCase$(Cells(x(i), "m")) = "TOTAL") * (UCase$(Cells(x(i - 1), "m")) = "ID") * (x(i) - x(i - 1) > 1) Then
            Cells(x(i), "n").FormulaR1C1 = "=sum(r" & x(i - 1) + 1 & "c:r[-1]c)"
        End If
    Next
End Sub

 

[Fluff]

This will give you the content of the cell

=INDEX(N:N,ROW()-1)

However if you use the evaluate formula button on the formula I posted, you will see that r has a cell address stored in it, not the cell content.

 

[RobP]

Thanks Fluff,

I've never seen before that if you use

=INDEX(N:N,ROW()-1)

you get the cell contents, but if you use

LET(r,INDEX(N:N,ROW()-1),r)

(on its own) .. you still get the cell contents .. but in your overall formula when you use it as above, r somehow becomes a cell reference instead ..

I'll probably never remember all that ..!

Rob