sumif problem with long value

[sanketgroup]

Hi

Column A..............................Column B

205002117588910030...................5
205002117588910040...................2



Column C......................................Column D (sumif)
205002117588910030........................=sumif(a:a,c1,b:b)


answer in column D should be 5. but it show me 7.
Means it is summing the value of both 205002117588910030 & 205002117588910040.

205002117588910030
205002117588910040
Difference is only last two digit, and sumif believes that both values are same.

Values are stored as text.
As its 18 digit long, i cannot use General or number. so column a, and c are as TEXT. I think its considering only upto 2050021175889100.

 

[Akashwani]

Hi,

Welcome to MrExcel.

You have hit the number limit that a cell can hold.
Can you prefix your numbers with a letter?
If not, take a look here.....

http://precisioncalc.com/xlprecision.html

I've not used it, but it may help you with your situation.

Or you could try....
http://www.openoffice.org/

Good luck

Ak