formula to sum an entire column except the last cell

[hkbhansali]

Hi,

I have an amount in column "B" (for example "B7:B25" and "B26" has Total of "B7:B25". (length is variable).


I want sum in cell "C5".

How can I sum all values in the range except the last one?

[Richard Buttrey]

Hi

One way

=SUM(B7:B26)/2

Personally, and ever since I stopped using lined paper and pencils to add up a column of numbers, I always put the SUM() function in Excel ABOVE the data and set it t sum as many cells as you're ever likely to have. That means it's fixed and you don't have to worry abut adding new data.

[Vraag en antwoord]

or

=LOOKUP(9E+99,B:B)

[RaulSerg]

Two other alternatives:

=SUMPRODUCT(B7:B26*NOT(ISFORMULA(B7:B26)))

or:

=SUBTOTAL(9,B7:B26)
provided that in B26 also a SUBTOTAL(9,B7:B25) is used instead of SUM(B7:B25).
SUBTOTAL() here ignores another SUBTOTAL().

[hkbhansali]

Hi RaulSerg

row length may be variable..Sometime B7:B65, or Sometime B7:B54... and more

[Richard Buttrey]

Hi RaulSerg

row length may be variable..Sometime B7:B65, or Sometime B7:B54... and more

The number of rows don't matter if you simply sum all the values INCLUDING the total and divide by two.

[AliGW]

You could try this:

=SUM(B7:B5000)-LOOKUP(2,1/(B7:B5000<>""),B7:B5000)

Make the range longer than it will ever need to be.

[hkbhansali]

Thanks AliGW

[hkbhansali]

Thanks ...RaulSerg - Vraag en antwoord - Richard Buttrey

Thanks for your kind support..

[AliGW]

No problem.