CODEWITHSUNDEEP
excelexcel-formula
kishore naidu
kishore naidu

Reputation: 151

Excel function to SUM all the values in a column except the last value

I have a weekly customer deposit amount in a column that includes empty value in between. How can i sum the amount in that column leaving the last cell value. The column is dynamic and next week more deposits come in that increases the Column length. Again i have to sum the entire values leaving the last cell value in that column. Please suggest excel function that automates this. Thanks in Advance.

Upvotes: 0

Views: 3756

Answers (2)

A.S.H
A.S.H

Reputation: 29332

You can use VLOOPKUP to catch the last value and subtract it, without the need to generate intermediate arrays:

=SUM(A:A)-VLOOKUP(1E+99,A:A, 1)

p.s. unlike VLOOKUP, LOOKUP is provided for backward compatibility so should be used only when necessary.

Upvotes: 2

hsoesanto
hsoesanto

Reputation: 523

You could try something like: =SUM(A:A)-LOOKUP(2,1/(A:A<>""),A:A)

It would sum everything in that column and minus the last cell value, effectively excluding that one. NB: that should not be placed in the same column as may result in circular reference

Upvotes: 1

Related Questions