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?
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?
My English is very poor, so please be patient >_<"
Thanks & Regards.
hkbhansali
Hi
One way
Formula:Please Login or Register to view this content.
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.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
or
Please Login or Register to view this content.
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().
Hi RaulSerg
row length may be variable..Sometime B7:B65, or Sometime B7:B54... and more
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.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks AliGW
Thanks ...RaulSerg - Vraag en antwoord - Richard Buttrey
Thanks for your kind support..
No problem.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks