# 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

Hi,

You could test following formula :

HTH

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Re: Sum from match to last row

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

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 ...?

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Re: Sum from match to last row

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

A basic UDF could like the following :

Hopefully, the result is correct ...

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

HTH

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Re: Sum from match to last row

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

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 ?

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Re: Sum from match to last row

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!!!

• Re: Sum from match to last row

The Offset function requires a starting point A1, then the number of rows, and the number of columns ...

In this case, MATCH("a",A1:A20,0)-1 will generate the number of rows ... to reach the starting row ...

Without this issue of matching the starting row ... your formula would be =SUMPRODUCT((A1:A10="a")*(A1:A10="x")*(B1:B10))

Hope this clarifies ...

Happy Holidays ... to you too ... :smile:

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

## Participate now!

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