SUMIF, wrong result, strange behaviour

[Werner Rohrmoser]

Hi,

I have a found something strange, which I do not understand.

Formula in A1: =SUMIF(D13,C1,E1:E3)
value in C1: 02016521000513438888 formatted as text
values in D13 : 0086WX6272000039947, 02016521000513439916,
02026821000513439916
values in E1:E3 : 130
2,214 1,675

Result of SUMIF should be 0, but I get 2,214.

This is something I do not understand.
BTW, you can change the last 4 digits of C1 to whatever you like,
you'll always get 2,214.

Any help is really appreciated.
Thanks.

Werner

 

[joel]

The problem is C1 is a string so excel is comparing the 1st character of the
strings. You can use VALUE to convert the string to a number.

 

[Jacob Skaria]

Never tried with long text... However SUMPRODUCT should work

=SUMPRODUCT(--(D13=C1),--(E1:E3))

If this post helps click Yes

 

[Dave Peterson]

Excel keeps track of 15 significant digits.

02016521000513438888
is 20 characters long.

=sumif() and =countif() treat numbers and text the same:

=countif(a:a,"1")
and
=countif(a:a,1)
will return the same value. It doesn't distinguish between that text '1 and the
number 1.

I'm guessing that excel does some sort of manipulation of text to numbers and
back to text (invisible to us mere mortals) when it builds that comparison
string.

There are other things that =countif() and =sumif() can't do, too:
Put:
=REPT("x",255)&"y"
in A1:A2

Then convert A1 to a value (copy|paste special|values)
Leave A2 a formula.

Put this in B1:
=countif(a1,"*y")
and drag down to B2.

Notice the difference in the values that are returned.

Just more stuff to worry about when you're double checking your workbooks.

 

[Werner Rohrmoser]

Hi Dave,

yes I've made some tests with 15 and more digits with and without
alpha-signs
and the result is, that only 15 digits are recognized, if the
expression contains only numbres.
Never thought of that.

Thank you.

Werner

 

[Dave Peterson]

I never would have, either.

It's scary how many things I trust without double checking <vbg>.