Sum Except Last Register

[pegbol]

.
Hi,

The formula showed below is located in cell B1 and works OK. But, I would like help me to improve it.

=SUBTOTAL(9,$A$21:$A$65536)-OFFSET($A$21,(COUNTA($A$21:$A$65536)-1),0)

What I am pretending to do is subtotal all registers in column A except the last register
(from A21 ahead).

Thanks in advance.

[Ken Puls]

I don't follow exactly what you're asking...

If I enter the value 10 in cells A21:A30 (ten rows), I get 90. The sum of 10 rows * 10 less the value of the last cell.

Is that not what you wanted?

[pegbol]

Yes, that is exactly what I want to do.

Is there any way to improve the formula?.

[Ken Puls]

The only change I would make is to maybe go with:

=SUM($A$21:$A$65536)-OFFSET($A$21,(COUNTA($A$21:$A$65536)-1),0)

I'm not sure if you had a reason for the subtotal, though, but I don't really see any reason not to use the Sum formula.

Is there any specific reason you want to improve it? It just appears too long, or you're concerned another user won't understand it?

[Zack Barresse]

The only reason to use SUBTOTAL is if Autofilter is being enacted here. Otherwise, use SUM.

[pegbol]

firefytr is right. I will use an autofilter in the column A.

As you said kpuls, to me the formula seemed too long and I wanted to see if could be improved in another way.

I apologize if my request was not so clear.

Appreciate your explanation. Thanks so much for your kind assitance.

regards,
Pedro.