sum previous x number of cells

[Guest]

Based on the table bellow, I am trying to add all the numbers in colmn B
between two 1s in column A. To state it in anotger way, in column C every
time column A equals 1, I want to add the last x many cells of column B.
A B C
1 5 5
5 0
1 5 10
5 0
1 5 10
5 0
The problem is that the frequency at which the 1s in column A appear changes.
Is there a formula that would either tell cells in column C to add all the
values in column B since the last 1 in column A or alternatevely to tell the
cells in column C to add the last x number of cells in column B. I hope this
makes sense.
Thanks. Neda

 

[Bob Phillips]

C1: =IF(A1=1,B1,"")
C2: =IF(A2<>1,"",SUM(INDIRECT("A"&MAX(IF($A$1:A1=1,ROW($A$1:A1))))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter. Copy C2 down.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

C2 should be

=IF(A2<>1,"",SUM(INDIRECT("B"&MAX(IF($A$1:A1=1,ROW($A$1:A1)))&":B"&SUM(ROW()
))))

still an array formula.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)