Transferring Totals (MCorn)

[MCorn]

Hi all
I posted a question in which I got an answer from Frank Kabel. (Thanks by the way Frank)
Here is the question

Hi
I have a check book spreadsheet that when you make a deposit of a payment the total shows in the following cell. My >question is this: Is there a way to take the total that moves from different cells and show in a fixed cell? For example
100.00 100.00 150.0
50.00 50.0
100.00 150.0

Here is the answer Frank gave

H
try the following formula to get the value of the last row in column
(your total)
=OFFSET($C$1,COUNTA($C:$C)-1,0
works only if there are no blanks in column C in betwee

The problem is this: =OFFSET($C$1,COUNTA($C:$C)-1,0) will not work when there is a formula in column C. The formula that I'm using is =IF(AND(ISBLANK(A2),ISBLANK(B2)),"",C1-A2+B2
I first thought it was the formula, so I copied the results in column C to column D using =c
That was not the problem. Does anybody know a tweek to Frank's formula that will make it work with a formula in the column
I hope this made sense! Thanks MCor

 

[Frank Kabel]

Hi
the problem is that COUNTA treats the result "" of your formula as an
entry. One workaround:
=OFFSET($C$1,SUMPRODUCT(--($C$1:$C$1000<>""))-1,0)

 

[Frank Kabel]

Hi
thanks for the feedback.
They probably live in Kelsheim and Ingelstadt and yes quite near to
Frankfurt

 

[Frank Kabel]

Hi Mark
good point -> would be the easier solution