Sum from match to last row

Hello, I'm trying to come up with a function to sum the from a match in the first column down to the last row, summing a second match starting from the row of the first match, using the following sample data:

[TABLE="class: grid, width: 50, align: left"]

[tr]

[td]

a

[/td]

[td]

10

[/td]

[/tr]

[tr]

[td]

a

[/td]

[td]

5

[/td]

[/tr]

[tr]

[td]

x

[/td]

[td]

9

[/td]

[/tr]

[tr]

[td]

b

[/td]

[td]

8

[/td]

[/tr]

[tr]

[td]

b

[/td]

[td]

20

[/td]

[/tr]

[tr]

[td]

x

[/td]

[td]

21

[/td]

[/tr]

[tr]

[td]

x

[/td]

[td]

7

[/td]

[/tr]

[tr]

[td]

c

[/td]

[td]

11

[/td]

[/tr]

[tr]

[td]

x

[/td]

[td]

3

[/td]

[/tr]

[tr]

[td]

x

[/td]

[td]

12

[/td]

[/tr]

[/TABLE]

Sample input/output would be:
Find "a" & "x" and Sum=10+5+9+21+7+3+12
Find "b" & "x" and Sum=8+20+21+7+3+12
Find "c" & "x" and Sum=11+3+12

I thought this would be simple, but it's hurting my brain a bit. Any help would be greatly appreciated. - Rob

Re: Sum from match to last row

I figured out a portion of it with a bit of digging:

=SUM(SUMIF(A1:A10,{"b";"x"},B1:B10))

Similar to using SUMIF with an OR statement.

Not sure how to sum from the first match in a column.

Re: Sum from match to last row

Quote from Carim;761710

Hi,

You could test following formula :

=SUM(INDIRECT(ADDRESS(MATCH("b",A1:A20,0),2,4)):INDIRECT("B"&SUMPRODUCT(MAX((ROW(B1:B20))*(B1:B20<>"")))))

HTH

Display More

Carim, thanks for the response. This is very close to what I'm looking for. However, I can't figure out how to not sum all the non-matching rows below. I thought there may have been and easy way to do this with formulas, but I may resort to some VBA coding.

Re: Sum from match to last row

Quote from Carim;761761

Hi,

Sorry I did not realize there was one more condition ... the non matching rows ...

Your life would be easier with a UDF ... :smile:

Are you familiar enough with macros ... or would you like an example ...?

Display More

No problem. I appreciate the offer, but I can usual grind out a solution with macros. I just prefer to not use them if this sheet has the potential to make it into the wild If you think there is a way to accomplish this with a non-UDF then any assistance would be greatly appreciated.

Re: Sum from match to last row

Quote from Carim;761763

A basic UDF could like the following :

Code

Function RobySum(init As String, rng As Range)
Dim x As Long
Dim y As Long
Dim i As Long


x = Application.Match(init, rng, 0)
y = Range("A65536").End(xlUp).Row
  For i = x To y
    If Cells(i, 1).Value = init Or Cells(i, 1).Value = "x" Then
      RobySum = RobySum + Cells(i, 2)
    End If
  Next i
End Function

Display More

Hopefully, the result is correct ...

Now, for a non-UDF solution ... do you mind if there is a helper column ?

HTH

Display More

Thanks Carim. I've been playing around with several helper columns to get this to work so I do not mind in the least.

Re: Sum from match to last row

Quote from Carim;761766

An attempt within a single formula ...

=SUMPRODUCT(((OFFSET(A1,MATCH("a",A1:A20,0)-1,0):A10="a")+(OFFSET(A1,MATCH("a",A1:A20,0)-1,0):A10="x"))*(OFFSET(B1,MATCH("a",A1:A20,0)-1,0):B10))

Obviously "a" has to be replaced by a cell containing "a" or "b" or "c" ... since "x" seems to be fixed ...

Does it produce the expected result ?

Display More

Excellent, this checks out in all cases. I can follow along, but I don't quite understand whats going on with the [A10="a"] part of the formula. This does exactly what I need. Much thanks to you Carim and happy holidays!!!