If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SUMIF, wrong result, strange behaviour
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 |
#2
|
|||
|
|||
SUMIF, wrong result, strange behaviour
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. "Werner Rohrmoser" wrote: 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 |
#3
|
|||
|
|||
SUMIF, wrong result, strange behaviour
Never tried with long text... However SUMPRODUCT should work
=SUMPRODUCT(--(D13=C1),--(E1:E3)) If this post helps click Yes --------------- Jacob Skaria "Werner Rohrmoser" wrote: 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 |
#4
|
|||
|
|||
SUMIF, wrong result, strange behaviour
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 wrote: 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 -- Dave Peterson |
#5
|
|||
|
|||
SUMIF, wrong result, strange behaviour
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 |
#6
|
|||
|
|||
SUMIF, wrong result, strange behaviour
I never would have, either.
It's scary how many things I trust without double checking vbg. Werner Rohrmoser wrote: 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 |
Thread Tools | |
Display Modes | |
|
|