Summing From a Date to a Desired Cell Value

[guy123456]

Hello Everyone,

I am trying to sum values in a table starting from a certain date range and ending when the values bceom zeros again. I have attached an example to better clarify. I was hoping this could be done with functions. Any help would be much appreciated!

Thanks!

[Arkadi]

Since you wanted a function, that is what is provided:

In any cell put

Please Login or Register  to view this content.

it will then give you the sum starting at that date down to the first 0 it encounters.

Please Login or Register  to view this content.

[dflak]

Here's an non-VBA way of doing it. You could probably roll this all up into one function, but I left it as helper cells so the logic is more clear.

Cell E1 has the date of interest.

Cell E2 has the formula =MATCH(E1,A:A,0) - this formula finds the first row on which the date occurs.

Cell E3 has the formula =MATCH(0,OFFSET(B1,E2,0,1000,1),0) - this gets a bit more complicated. First we'll look at the range. This is provided by the offset part.

OFFSET(B1,E2,0,1000,1) This says, starting at cell B1 go down the number of rows indicated in cell E2, go over zero columns and then give me a range 1000 rows deep and 1 column wide.

That's what we apply the match to. We are looking for the first zero in this range and it happens 6 rows below the specified date and that is your answer.

[samba_ravi]

=sum(offset(b1:b24,match(i3,a1:a24,0)-1,,match(0,offset(b1:b24,match(i3,a1:a24,0)-1,,rows(b1:b24)-match(i3,a1:a24,0)+1),0),))

[samba_ravi]

=MATCH(0,INDEX(B1:B24,MATCH(I3,A1:A24,0)):B24,0)-1
if there is only 1s and 0s then the above will work

[FlameRetired]

Inspired by nflsales's excellent solution ... suppose the ranges are unknown.

=MATCH(0,INDEX(B:B,MATCH(I3,A:A,1)):INDEX(B:B,MATCH(1E+306,A:A,1)),0)-1