Exclude a cell

[DF2507]

Excel 2002

Any one know how I can sum all the cell values in a column except the value
of the last cell to be entered which could be say half-way down the column?

Is there a function to exclude a particular cell from "sum" ?

I know a lot of ppl are far more advanced than I am but we all start
somewhere lol

Thanks for your help

Dale

 

[Ardus Petus]

To exclude cell A16:
=SUM(A:A)-A16

Not very elegant, but effcient!

Cheers,

 

[Sandy Mann]

Dale,

Excel will have to be told which was the last entry. There may be better
ways but I would try it this way:

If you already have data in the spreadsheet then insert a new Column A

Right-click on the sheet tab and the enter the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim aRow As Long

If Target.Column <> 2 Then Exit Sub

Application.EnableEvents = False

aRow = Target.Row

Range("A:A").ClearContents

Cells(aRow, 1).Value = aRow

Application.EnableEvents = True

End Sub

alter the " Column <> 2 " to the column number that you want to sum

Then in the cell you want the total minus the last entry:

=SUM(B:B)-INDIRECT("B"&SUM(A:A))

alter the column reference to you needs.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[DF2507]

Thx that will help with part of my problem but how would I make A16
variable.........for instance.....the last cell I enter could be A17 not A16
but I don't want to keep going back to my formula to amend it........

 

[Ardus Petus]

=SUM(OFFSET(A1,,,COUNTA(A:A)-2))

HTH
--
AP

Thx that will help with part of my problem but how would I make A16
variable.........for instance.....the last cell I enter could be A17 not
A16 but I don't want to keep going back to my formula to amend it........

 

[Ragdyer]

If the range might contain empty cells, you could try this:

=SUM(A:A,-LOOKUP(99^99,A:A))

 

[Sandy Mann]

Ah! I read the OP's comment:

of the last cell to be entered which could be say half-way down the
column?

as half way down a colum of figures. Of course it is much more logical that
the last used cell is half way down the column.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[DF2507]

Thx Ragdyer I think that has worked

thx

DF

Ah! I read the OP's comment:


as half way down a colum of figures. Of course it is much more logical
that the last used cell is half way down the column.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[DF2507]

Been having problems with posting.......some go missing and others aren't
where they should be, sorry all but don't think it was my fault....

DF