Is it possible to skip a row in a formula that calculates totals?

[ramona]

I am using Excel 2003. I have 65 rows, and columns K-Q and S are manually
entered. Column R is the sum of K-Q. This is the formula that I have in
column T except for the first row: =IF(R12*S12=0," ",((R12*S12)+T11)) I
used this formula because the users do not wish for the running total to show
in any but the last used row. Now they'd like to skip a row or two at
intervals of their choice. My question is: How can I get my formula to
continue to total if my users skip a row or two, without having to manually
change my formula at each interval? Columns R and T are locked and
protected. This is going to be their template.

Thanks x10 to anyone who tries even if this is not possible. I just love
this site!!

 

[Lars-Åke Aspelin]

I am using Excel 2003. I have 65 rows, and columns K-Q and S are manually
entered. Column R is the sum of K-Q. This is the formula that I have in
column T except for the first row: =IF(R12*S12=0," ",((R12*S12)+T11)) I
used this formula because the users do not wish for the running total to show
in any but the last used row. Now they'd like to skip a row or two at
intervals of their choice. My question is: How can I get my formula to
continue to total if my users skip a row or two, without having to manually
change my formula at each interval? Columns R and T are locked and
protected. This is going to be their template.

Thanks x10 to anyone who tries even if this is not possible. I just love
this site!!

Try this formula in column T (row 12 as in your example)

=IF(R12*S12=0,"
",R12*S12+INDEX(T$1:T$1000,MAX(ROW(T$1:T11)*((T$1:T11)<>" "))))

Note: this is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

i.e. you replace T11 in your formula with the INDEX expression.
This expression returns the content of the cell in the T column with
the highest row number upto 11, but omitting cells with the space in.
This gives the possibilty to "skip row" when inputting the data and
still have the correct summing.

Note that you must change the 1000 to cover the number of rows that
may contain input data.

Hope this helps / Lars-Åke

 

[ramona]

Thank you, Lars-Ake.
I definitely appreciate your assistance. It took me a while to figure it out
but I had a typo. It works beautifully!!

Thank you SOOOOOOOOO much. I really to love this site!!