Search code examples
excelexcel-2007

Excel consolidate sum yields different column total

I have two columns that look like this:

Make/Model      Parc
Abarth*500      15
Abarth*500      19
Abarth*500      2
Abarth*500      14
Abarth*500      15
Abarth*500      25
Abarth*500      20
Abarth*500      17
Abarth*500C     12
Abarth*500C     16
Abarth*500C     23
Abarth*500C     18
Abarth*500C     1
Alfa Romeo*145  405
Alfa Romeo*145  431
Alfa Romeo*146  36
Alfa Romeo*146  80
Alfa Romeo*146  121
Alfa Romeo*146  72
Alfa Romeo*146  39

etc...

The sum of the values in the "Parc" column is 10,475,387.

I perform a consolidate on this table to remove the repetitions in "Make/Model" and sum the values in the "Parc" column for each "Make/Model."

Excel Consolidate

Once I perform the consolidation, everything looks as expected:

Make/Model      Parc
Abarth*500      127
Abarth*500C     70
Alfa Romeo*145  836
Alfa Romeo*146  348
Alfa Romeo*147  3848
Alfa Romeo*155  29
Alfa Romeo*156  3148
Alfa Romeo*159  573
Alfa Romeo*164  111

etc...

So what's the problem? The new sum of the "Parc" column is 10,554,076! When I perform random "spot" checks everything seems correct. I do not understand how the sum could have gone up? I have over 18,000 rows in this dataset so individually checking all of the data would be very time consuming.

You can find the full dataset here: Excel File

Edit: I added the screenshot of me using the consolidate tool to give you an idea of what I did to get this result.

Edit 2: I have tried removing all formatting. Also, these numbers are all integers and the option to "Automatically insert a decimal point" is not checked. I have also tried creating a new column of =INT() values to ensure they are all integers. My results are exactly the same in all cases.

Solution

  • The asteriks is a special character in Excel. Easiest solution that I know is to replace the asterisk by a space. To do so in the find/replace box, type '~*' in the find box and ' ' in the replace then do replace all (without quotes).

    Both sum and sumif then yield 10,475,387.