Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF, wrong result, strange behaviour
Hi,
I have a found something strange, which I do not understand. Formula in A1: =SUMIF(D1:D3,C1,E1:E3) value in C1: 02016521000513438888 formatted as text values in D1:D3 : 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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(D1:D3,C1,E1:E3) value in C1: 02016521000513438888 formatted as text values in D1:D3 : 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF, wrong result, strange behaviour
Never tried with long text... However SUMPRODUCT should work
=SUMPRODUCT(--(D1:D3=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(D1:D3,C1,E1:E3) value in C1: 02016521000513438888 formatted as text values in D1:D3 : 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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(D1:D3,C1,E1:E3) value in C1: 02016521000513438888 formatted as text values in D1:D3 : 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shortcut key strange behaviour | Excel Worksheet Functions | |||
Shortcut key strange behaviour | Excel Worksheet Functions | |||
Strange if(***) behaviour? | Excel Discussion (Misc queries) | |||
sumif wrong result | Excel Worksheet Functions | |||
Strange behaviour | Excel Worksheet Functions |