Exclude a cell

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

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

Not very elegant, but effcient!

Cheers,
--
AP

"DF2507" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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
>

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

[email protected]
[email protected] with @tiscali.co.uk


"DF2507" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
> To exclude cell A16:
> =SUM(A:A)-A16
>
> Not very elegant, but effcient!
>
> Cheers,
> --
> AP
>
> "DF2507" <[email protected]> a écrit dans le message de news:
> [email protected]...
>> 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
>>
>
>

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

HTH
--
AP

"DF2507" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> To exclude cell A16:
>> =SUM(A:A)-A16
>>
>> Not very elegant, but effcient!
>>
>> Cheers,
>> --
>> AP
>>
>> "DF2507" <[email protected]> a écrit dans le message de news:
>> [email protected]...
>>> 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
>>>
>>
>>
>
>

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

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ardus Petus" <[email protected]> wrote in message
news:[email protected]...
> =SUM(OFFSET(A1,,,COUNTA(A:A)-2))
>
> HTH
> --
> AP
>
> "DF2507" <[email protected]> a écrit dans le message de news:
> [email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> To exclude cell A16:
> >> =SUM(A:A)-A16
> >>
> >> Not very elegant, but effcient!
> >>
> >> Cheers,
> >> --
> >> AP
> >>
> >> "DF2507" <[email protected]> a écrit dans le message de news:
> >> [email protected]...
> >>> 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
> >>>
> >>
> >>
> >
> >
>
>

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

[email protected]
[email protected] with @tiscali.co.uk


"Ragdyer" <[email protected]> wrote in message
news:[email protected]...
> If the range might contain empty cells, you could try this:
>
> =SUM(A:A,-LOOKUP(99^99,A:A))
>
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Ardus Petus" <[email protected]> wrote in message
> news:[email protected]...
>> =SUM(OFFSET(A1,,,COUNTA(A:A)-2))
>>
>> HTH
>> --
>> AP
>>
>> "DF2507" <[email protected]> a écrit dans le message de news:
>> [email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> To exclude cell A16:
>> >> =SUM(A:A)-A16
>> >>
>> >> Not very elegant, but effcient!
>> >>
>> >> Cheers,
>> >> --
>> >> AP
>> >>
>> >> "DF2507" <[email protected]> a écrit dans le message de news:
>> >> [email protected]...
>> >>> 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
>> >>>
>> >>
>> >>
>> >
>> >
>>
>>
>

Thx Ragdyer I think that has worked

thx

DF


"Sandy Mann" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "Ragdyer" <[email protected]> wrote in message
> news:[email protected]...
>> If the range might contain empty cells, you could try this:
>>
>> =SUM(A:A,-LOOKUP(99^99,A:A))
>>
>> --
>> HTH,
>>
>> RD
>>
>> ---------------------------------------------------------------------------
>> Please keep all correspondence within the NewsGroup, so all may benefit !
>> ---------------------------------------------------------------------------
>> "Ardus Petus" <[email protected]> wrote in message
>> news:[email protected]...
>>> =SUM(OFFSET(A1,,,COUNTA(A:A)-2))
>>>
>>> HTH
>>> --
>>> AP
>>>
>>> "DF2507" <[email protected]> a écrit dans le message de news:
>>> [email protected]...
>>> > 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" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> >> To exclude cell A16:
>>> >> =SUM(A:A)-A16
>>> >>
>>> >> Not very elegant, but effcient!
>>> >>
>>> >> Cheers,
>>> >> --
>>> >> AP
>>> >>
>>> >> "DF2507" <[email protected]> a écrit dans le message de
>>> >> news:
>>> >> [email protected]...
>>> >>> 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
>>> >>>
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>
>

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


"DF2507" <[email protected]> wrote in message
news:[email protected]...
> Thx Ragdyer I think that has worked
>
> thx
>
> DF
>
>
> "Sandy Mann" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "Ragdyer" <[email protected]> wrote in message
>> news:[email protected]...
>>> If the range might contain empty cells, you could try this:
>>>
>>> =SUM(A:A,-LOOKUP(99^99,A:A))
>>>
>>> --
>>> HTH,
>>>
>>> RD
>>>
>>> ---------------------------------------------------------------------------
>>> Please keep all correspondence within the NewsGroup, so all may benefit
>>> !
>>> ---------------------------------------------------------------------------
>>> "Ardus Petus" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> =SUM(OFFSET(A1,,,COUNTA(A:A)-2))
>>>>
>>>> HTH
>>>> --
>>>> AP
>>>>
>>>> "DF2507" <[email protected]> a écrit dans le message de news:
>>>> [email protected]...
>>>> > 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" <[email protected]> wrote in message
>>>> > news:[email protected]...
>>>> >> To exclude cell A16:
>>>> >> =SUM(A:A)-A16
>>>> >>
>>>> >> Not very elegant, but effcient!
>>>> >>
>>>> >> Cheers,
>>>> >> --
>>>> >> AP
>>>> >>
>>>> >> "DF2507" <[email protected]> a écrit dans le message de
>>>> >> news:
>>>> >> [email protected]...
>>>> >>> 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
>>>> >>>
>>>> >>
>>>> >>
>>>> >
>>>> >
>>>>
>>>>
>>>
>>
>>
>
>