Sum Rows Based On Location

  • please see the attached sheet to help follow what i am saying. i need a formula that finds the word "Find Me" and performs the calculation written to the side of the word "find me", and stops with the word "finished". the difference in rows between "find me" and "finished" varies. i am at a loss on this one. hopefully someone out there is got the thinking cap on today!

  • Re: Sum Rows Based On Location


    the only problem with your solution - that i should have spelled out better - is that there is data all up and down in column A. so if you were to use that formula you would have values returned all over.

  • Re: Sum Rows Based On Location


    Hi z,


    While a few ideas come to mind I think some clarification is needed:


    Is the data layed out consistently? as in:


    - Is the answer always required in Col C with "FIND ME" in Col A/same row?


    - Will there always be a blank row between the last number in the column and the word "FINISHED"?


    -Will the column of data be changing and need constant updates?


    Cheers,


    dr

  • Re: Sum Rows Based On Location


    in reposonse to the previous reply, the data will always be in column "a" and it does need constant updating. the rows in the column and text will be changing all the time. yes there is always a blank under last entry before the word "finished." it is not nessecary to have the results in column "c" they can go anywhere even on another sheet, likewise with the formulas. i am at a total loss on this one - please help!

  • Re: Sum Rows Based On Location


    Based on your example input formula in cell B7 custom format cell to [COLOR="DarkRed"]0;-0;;@[/COLOR] and copied down.



    =LOOKUP(10^307,CHOOSE({1,2},0,IF(A7="",SUM(B$7+INDEX($B$6:$B6,MATCH(9.99999999E+307,B$6:B6))),A7-A6)))



    Hope it helps!

  • Re: Sum Rows Based On Location


    Hi zotee,


    No indication that you got what you need so here's a Sheet_Change sub that (I think) will do what you want.



    It does, however require the numbers to be consecutive (no blank rows in series).
    What it does:


    -Gets the current range of numbers IF they are in a series (no blank rows)
    -finds first and last row
    -does arithmetic


    What it doesn't do (as written):


    -doesn't regard deletion/insertion of a row as a change
    -doesn't regard deletion of a number in the series (blank row) as a change


    Therefore it requires the numbers to be in a series.


    Note: If this is useful and blank rows needed to be dealt with it could be done.


    As always, try this on a COPY of your worksheet.


    [vba]
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    '
    ' Finds and recalculates CURRENT instance of "FIND ME"
    '
    Dim i As Long
    Dim c As Range
    Dim amt As Long
    Dim lastrow As Long
    Dim firstrow As Long
    Dim firstaddress As String


    'handle error that escapes checks
    On Error GoTo reset
    'turn off screen for speed
    Application.ScreenUpdating = False
    'stop this sub from firing on change
    Application.EnableEvents = False

    'check if change in Col A
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    'check if change was number
    If Not IsNumeric(Target) Or Target = "" Then GoTo reset
    firstrow = Target.End(xlUp).Row + 2
    'get last number in series
    lastrow = Cells(firstrow, 1).End(xlDown).Row
    'do arithmetic
    For i = lastrow To firstrow Step -1
    amt = amt + Cells(i, 1) - Cells(i - 1, 1)
    Next i
    'put result in Col C at "FIND ME"
    Cells(firstrow - 2, 3) = amt
    End If


    reset:

    Application.ScreenUpdating = True
    Application.EnableEvents = True


    End Sub
    [/vba]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!