I've got a simple table (10 columns, 200 rows). The values in the final
column are totalled.

However, this total changes depending on which other column I use to sort
the whole table. (I can get three different values all +/- $100 around
$6000. This would suggest a maximum of two cells are being 'missed'.

The cell references in the total formula have been double-checked and are
accurate. I've set up another cell that sums the column using absolute
addressing. Its total changes on an identical basis.

All formats for cells in the total column have been double checked and are
consistently showing currency.

So I'm at a loss two answer two questions :
- what is the correct total
- why the difference
- how do I resolve it

Any help will be appreciated.

TIA

Check the SUM formula?

--
HTH

Bob Phillips

"RFJ" wrote in message
...
I've got a simple table (10 columns, 200 rows). The values in the final
column are totalled.

However, this total changes depending on which other column I use to sort
the whole table. (I can get three different values all +/- $100 around
$6000. This would suggest a maximum of two cells are being 'missed'.

The cell references in the total formula have been double-checked and are
accurate. I've set up another cell that sums the column using absolute
addressing. Its total changes on an identical basis.

All formats for cells in the total column have been double checked and are
consistently showing currency.

So I'm at a loss two answer two questions :
- what is the correct total
- why the difference
- how do I resolve it

Any help will be appreciated.

TIA

Try expanding your range to include 'border cells' - if your current SUM formula is

=SUM(C2:C200)

then try

=SUM(C1:C201)

Where C1 and C201 aren't affected by your sort.

HTH,
Bernie
MS Excel MVP


"RFJ" wrote in message ...
I've got a simple table (10 columns, 200 rows). The values in the final column are totalled.

However, this total changes depending on which other column I use to sort the whole table. (I can
get three different values all +/- $100 around $6000. This would suggest a maximum of two cells
are being 'missed'.

The cell references in the total formula have been double-checked and are accurate. I've set up
another cell that sums the column using absolute addressing. Its total changes on an identical
basis.

All formats for cells in the total column have been double checked and are consistently showing
currency.

So I'm at a loss two answer two questions :
- what is the correct total
- why the difference
- how do I resolve it

Any help will be appreciated.

TIA

Are the values that are being summed formulas. If so perhaps sorting is
effecting them somehow and/or the sorting is not working properly (ex. only
columns 1-3 are being sorted and 7-10 are staying where they are). Weird
problem....

"RFJ" wrote:

I've got a simple table (10 columns, 200 rows). The values in the final
column are totalled.

However, this total changes depending on which other column I use to sort
the whole table. (I can get three different values all +/- $100 around
$6000. This would suggest a maximum of two cells are being 'missed'.

The cell references in the total formula have been double-checked and are
accurate. I've set up another cell that sums the column using absolute
addressing. Its total changes on an identical basis.

All formats for cells in the total column have been double checked and are
consistently showing currency.

So I'm at a loss two answer two questions :
- what is the correct total
- why the difference
- how do I resolve it

Any help will be appreciated.

TIA

The cells being sorted are all values - so no complications there.

In answer to Bob's suggestion, the cell references in the formulae have been
checked, many times :(

Bernie's suggestion of extending the range, unfortunately, has no effect

The problem is repeatable on this spreadsheet in terms of deleting the
formulae and re-entering them. Bizarrely, the difference in total values
cannot be explained by a single cell (eg one total varies by 17.50 but the
smallest cell value is 35) - that also rules out header row complications in
terms of the sort.

I can solve the problem by cutting and paste-special the values into a new
spreadsheet. But it leaves me with an uncomfortable feeling that it was just
luck that I resorted the spreadsheet and found the problem. And that leaves
a rhetorical question of will it happen again and how will I know :((


Robin

"William Horton" wrote in message
...
Are the values that are being summed formulas. If so perhaps sorting is
effecting them somehow and/or the sorting is not working properly (ex.
only
columns 1-3 are being sorted and 7-10 are staying where they are). Weird
problem....

"RFJ" wrote:

I've got a simple table (10 columns, 200 rows). The values in the final
column are totalled.

However, this total changes depending on which other column I use to sort
the whole table. (I can get three different values all +/- $100 around
$6000. This would suggest a maximum of two cells are being 'missed'.

The cell references in the total formula have been double-checked and are
accurate. I've set up another cell that sums the column using absolute
addressing. Its total changes on an identical basis.

All formats for cells in the total column have been double checked and
are
consistently showing currency.

So I'm at a loss two answer two questions :
- what is the correct total
- why the difference
- how do I resolve it

Any help will be appreciated.

TIA