# Sum of a column changes when sorted???

#### ctblalock

##### New Member
I have a quantity of values listed in one column. The values range from 0.0018 to 22,620.0000
The last cell (A3859) has the formula =SUM(A1:A3858).
Changing the order of the values (ex: ascending to descending) causes the sum of the values to change
(ex: 973,867.904500000 to 973,867.904499998).
WHY?

Because I am talking about only one column of data and changes in the fourth decimal place, this may seem unimportant. However, this example is taken out of a very large analysis that I am doing which involves using "=EXACT" and conditional formatting. These are compromised by the fractional changes in sum.

Thanks,
ctb

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### plettieri

##### Well-known Member
Hi:

Sounds strange! Changing the order of the cells being summed should not change the results....do have any cells of the interest group using round, special cell formats, or are the cells being summed reliant on other cells?

plettieri

#### ctblalock

##### New Member
...do have any cells of the interest group using round, special cell formats, or are the cells being summed reliant on other cells?
plettieri

plettieri,

No re rounding.
No re links to other cells or summed from other cells.
Regarding special cell formats, not that I know of but because I had imported the data into Excel from our MRP system (csv format) and, just to make sure, I selected the entire worksheet, format, cells, number tab, number with two decimals points. The result did not prevent the sum from changing when sorted.

I read the article that Oaktree referred to and because the data was imported I thought that may be the culprit. HOWEVER, using the =ROUND workaround actually exacerbated the problem (even after using copy, paste special, values).

Any additional thoughts or suggestions for avoiding the problem are appreciated.

ctb

#### plettieri

##### Well-known Member

Hi:

I am running out of ideas but...

You might try (in a helper cells ie Column B) verifying the cells of interest are in fact values by multiplying by them by 1., then sorting (resorting)and summing that range of cells.....does the total then still differ?

plettieri

#### ctblalock

##### New Member
plettieri,

i did as you suggested. that seems to have fixed it. the sum no longer changes no matter which way i sort it. any additional explanation as to what was going on? thanks very much.

ctb

#### plettieri

##### Well-known Member
Hi ctblaock:

Sorry, I can't expain that ....My last effort to you was to be sure you were actually summing cells that contained values. When you created the helper column were there any differences bewteen the A and B columns?

Maybe one of the resident gurus can offer something concrete...

plettieri

Replies
6
Views
255
Replies
4
Views
848
Replies
1
Views
222
Replies
22
Views
340
Replies
1
Views
199

1,141,628
Messages
5,707,503
Members
421,511
Latest member
mgroah1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back