2 Different SUMS for 2 columns with duplicates

Romos1_excel

New Member
Joined
Apr 5, 2018
Messages
7
How come 2 columns that were supposed to have the same SUM as all values are Duplicates (Conditional Formatting Applied) they have different SUMS ?

Thank you all!!!



Book1.xlsx
ABC
1ABC
2x1€ 8.057.289,36€ 8.057.289,36
3x2€ 12.400.000,00€ 12.400.000,00
4x3€ 3.083.570,00€ 3.083.570,00
5x4€ 40.000.000,00€ 40.000.000,00
6x5€ 10.000.000,00€ 10.000.000,00
7x6€ 33.500.000,00€ 57.950.000,00
8x7€ 620.000,00€ 33.500.000,00
9x8€ 5.054.394,50€ 36.068.000,00
10x9€ 8.680.000,00€ 620.000,00
11x10€ 6.023.000,00€ 5.054.394,50
12x11€ 1.125.771,98€ 8.680.000,00
13x12€ 3.100.000,00€ 6.023.000,00
14x13€ 37.200.000,00€ 1.125.771,98
15x14€ 975.000,00€ 3.100.000,00
16x15€ 30.000.000,00€ 37.200.000,00
17x16€ 21.080.000,00€ 975.000,00
18x17€ 86.800.000,00€ 30.000.000,00
19x18€ 2.431.930,00€ 21.080.000,00
20x19€ 715.279,12€ 86.800.000,00
21x20€ 2.480.000,00€ 2.431.930,00
22x21€ 1.240.000,00€ 715.279,12
23x22€ 0,00€ 33.740.154,58
24x23€ 55.000.000,00€ 2.480.000,00
25x24€ 3.500.000,00€ 1.240.000,00
26x25€ 7.500.000,00€ 0,00
27x26€ 12.322.856,60€ 55.000.000,00
28x27€ 1.500.000,00€ 3.500.000,00
29x28€ 1.860.000,00€ 7.500.000,00
30x29€ 8.680.000,00€ 12.322.856,60
31x30€ 7.129.163,00€ 1.500.000,00
32x31€ 39.599.596,00€ 1.860.000,00
33x32€ 38.000.000,00€ 8.680.000,00
34x33€ 450.000,00€ 7.129.163,00
35x34€ 402.324,58€ 39.599.596,00
36x35€ 15.000.000,00€ 38.000.000,00
37x36€ 3.100.000,00€ 450.000,00
38x37€ 2.480.000,00€ 402.324,58
39x38€ 11.445.200,00€ 15.000.000,00
40x39€ 868.000,00€ 3.100.000,00
41x40€ 700.000,00€ 2.480.000,00
42x41€ 3.720.000,00€ 11.445.200,00
43x42€ 2.294.000,00€ 868.000,00
44x43€ 715.200,00€ 700.000,00
45x44€ 595.200,00€ 3.720.000,00
46x45€ 1.212.000,00€ 2.294.000,00
47x46€ 1.100.000,00€ 715.200,00
48x47€ 73.142.400,00€ 595.200,00
49x48€ 1.123.440,00€ 1.212.000,00
50x49€ 4.850.000,00€ 1.100.000,00
51x50€ 496.000,00€ 73.142.400,00
52x51€ 3.000.000,00€ 1.123.440,00
53x52€ 1.473.802,00€ 4.850.000,00
54x53€ 4.417.280,00€ 170.000,00
55x54€ 10.185.732,00€ 12.400.000,00
56x55€ 744.000,00€ 496.000,00
57x56€ 11.248.338,00€ 23.408.100,00
58x57€ 210.500.000,00€ 1.473.802,00
59x58€ 60.000.000,00€ 3.000.000,00
60x59€ 117.800.000,00€ 10.185.732,00
61x60€ 6.200.000,00€ 4.417.280,00
62x61€ 868.000,00€ 744.000,00
63x62€ 3.720.000,00€ 1.240.000,00
64x63€ 1.240.000,00€ 200.000.000,00
65x64€ 200.000.000,00€ 11.248.338,00
66x65€ 50.670.780,00€ 1.702.168,75
67x66€ 756.400.000,00€ 4.000.000,00
68x67€ 1.702.168,75€ 26.900.000,00
69x68€ 1.240.000,00€ 50.000.000,00
70x69€ 170.000,00€ 5.000.000,00
71x70€ 26.900.000,00€ 119.210.000,00
72x71€ 50.000.000,00€ 4.910.000,00
73x72€ 5.000.000,00€ 39.999.796,00
74x73€ 4.000.000,00€ 19.936.100,00
75x74€ 60.200.000,00€ 60.200.000,00
76x75€ 19.936.100,00€ 900.000,00
77x76€ 2.480.000,00€ 9.920.000,00
78x77€ 1.922.000,00€ 1.922.000,00
79x78€ 900.000,00€ 6.200.000,00
80x79€ 80.080.564,00€ 80.080.564,00
81x80€ 3.000.000,00€ 22.401.592,00
82x81€ 6.200.000,00€ 3.000.000,00
83x82€ 22.401.592,00€ 15.178.883,00
84x83€ 33.000.000,00€ 47.988.000,00
85x84€ 57.950.000,00€ 4.000.000,00
86x85€ 12.400.000,00€ 4.000.000,00
87x86€ 9.920.000,00€ 34.312.198,00
88x87€ 34.312.198,00€ 10.500.000,00
89x88€ 4.000.000,00€ 2.976.000,00
90x89€ 165.000.000,00€ 2.604.000,00
91x90€ 47.988.000,00€ 2.000.000,00
92x91€ 10.500.000,00€ 5.000.000,00
93x92€ 2.976.000,00€ 48.745.559,40
94x93€ 2.604.000,00€ 129.930.000,00
95x94€ 36.068.000,00€ 85.310.000,00
96x95€ 23.408.100,00€ 11.000.000,00
97x96€ 119.210.000,00€ 300.000.000,00
98x97€ 4.910.000,00€ 40.500.000,00
99x98€ 15.178.883,00€ 165.000.000,00
100x99€ 2.000.000,00€ 331.178.000,00
101x100€ 5.000.000,00€ 8.500.000,00
102x101€ 7.824.000,00€ 311.587.597,66
103x102€ 105.400.000,00€ 64.240.000,00
104x103€ 5.000.000,00€ 161.205.456,00
105x104€ 48.745.559,40€ 210.500.000,00
106x105€ 42.749.062,00€ 6.200.000,00
107x106€ 3.169.159,76€ 60.000.000,00
108x107€ 3.472.000,00€ 117.800.000,00
109x108€ 888.534,40€ 50.670.780,00
110x109€ 1.402.429,04€ 756.400.000,00
111x110€ 39.999.796,00€ 33.000.000,00
112x111€ 161.205.456,00€ 868.000,00
113x112€ 669.600,00€ 2.480.000,00
114x113€ 992.000,00€ 1.240.000,00
115x114€ 2.898.752,00€ 1.240.000,00
116x115€ 102.170.000,00€ 6.200.000,00
117x116€ 2.095.600,00€ 3.169.159,76
118x117€ 2.000.000,00€ 3.472.000,00
119x118€ 24.029.392,80€ 105.400.000,00
120x119€ 3.720.000,00€ 1.240.000,00
121x120€ 6.500.000,00€ 888.534,40
122x121€ 650.000.000,00€ 2.095.600,00
123x122€ 1.240.000,00€ 7.824.000,00
124x123€ 3.720.000,00€ 6.500.000,00
125x124€ 129.930.000,00€ 24.029.392,80
126x125€ 40.500.000,00€ 2.000.000,00
127x126€ 85.310.000,00€ 650.000.000,00
128x127€ 11.000.000,00€ 3.720.000,00
129x128€ 300.000.000,00€ 2.898.752,00
130x129€ 331.178.000,00€ 102.170.000,00
131x130€ 8.500.000,00€ 42.749.062,00
132x131€ 311.587.597,66€ 1.402.429,04
133x132€ 64.240.000,00€ 992.000,00
134x133€ 4.000.000,00€ 669.600,00
135x134€ 33.740.154,58€ 3.720.000,00
136
137SUM€ 5.464.254.246,54€ 5.462.974.246,54
138
139Diff€ 1.280.000,00
Sheet5
Cell Formulas
RangeFormula
B137:C137B137=SUM(B2:B136)
B139B139=B137-C137
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:C135Cell ValueduplicatestextNO
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
The fact that all the numbers are duplicates does not mean that the 2 columns are identical.
 
Upvote 0
The fact that all the numbers are duplicates does not mean that the 2 columns are identical.
I 've checked that all values are numbers with the ISNUMBER function ...since all the numbers come in pairs there should be a zero diff.
 
Upvote 0
I 've checked that all values are numbers
That is not the point. Whilst all the numbers have been duplicated somewhere, the columns are not exactly the same which is why you get a different sum.
You could easily have a number that occurs two or more times in col C (and hence a duplicate) that does not occur in col B
 
Upvote 0

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top