User friendly summarising

**teylyn** · 08-12-2010, 06:08 AM

hello,

the formula for your first problem could be

=SUM(INDEX(Data!$B:$B,MATCH(Summary!C3,Data!$A:$A,0)):INDEX(Data!$B:$B,MATCH(Summary!$C$4,Data!$A:$A,0)-1))

I don't quite see what you want to achieve with the second problem. None of the data matches what you have in your description. Please explain exactly which cells to sum.

Offsetting upwards from the row labelled M, the value of 38 is achieved at row labelled F, not E. Please explain.

**StephenR** · 08-12-2010, 06:19 AM

Rather tortuous, but I think it gets you there.

Sub x()

Dim r As Long, r1 As Long, r2 As Long

Sheets("Summary").Activate

With Application
    If Range("C3") <> "" And Range("C4") <> "" Then
        r1 = .Match(Range("C3"), Sheets("Data").Columns(1), 0)
        r2 = .Match(Range("C4"), Sheets("Data").Columns(1), 0)
        Range("C6") = .Sum(Sheets("Data").Range("B1").Offset(r1 - 1).Resize(r2 - r1))
        Exit Sub
    End If
    
    If Range("C11") <> "" And Range("C12") <> "" Then
        r1 = .Match(Range("C11"), Sheets("Data").Columns(1), 0) - 1
        r2 = 1
        Do Until .Sum(Sheets("Data").Range("B1").Offset(r1 - r2).Resize(r2)) >= Range("C12")
            r2 = r2 + 1
        Loop
        Range("C14") = .Index(Sheets("Data").Columns(1), r1 - r2 + 1)
        Exit Sub
    End If
End With

MsgBox "Not enough info"

End Sub

teylyn: in the second method you start from L, not M. I see your solution will probably be two lines long...