Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF provides weird results
Hi,
I had never expected the result that comes out from this example: Excel cannot mix text and figures in 1 single list of values. Her comes what happens: My model contains, in A1 to A6 the values 1 2 3 a b c In B1 I input COUNTIF($A$1:$A$6,"<="&$A$1:$A$6) and I copy it down to B6. I get: 1 2 3 1 2 3 instead of 1 2 3 4 5 6 My question: why if 1 and 2 and 3 are smaller than "a", the count of values smaller than or equal to "a" returns 1 and not 4. I cannot understand it. Does anybody have any suggestions? Thank you |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF provides weird results
wrote...
.... My model contains, in A1 to A6 the values 1 2 3 a b c In B1 I input COUNTIF($A$1:$A$6,"<="&$A$1:$A$6) and I copy it down to B6. I get: 1 2 3 1 2 3 instead of 1 2 3 4 5 6 My question: why if 1 and 2 and 3 are smaller than "a", the count of values smaller than or equal to "a" returns 1 and not 4. I cannot understand it. .... Microsoft built extra intelligence into COUNTIF and SUMIF. When it comes to matching error values, it can be handy. Unfortunately, it follows its own logic when it comes to comparing text to numbers. If you want the result above, don't use COUNTIF. Use separate formulas =SUMPRODUCT(--($A$1:$A$6<=A1)) =SUMPRODUCT(--($A$1:$A$6<=A2)) =SUMPRODUCT(--($A$1:$A$6<=A3)) =SUMPRODUCT(--($A$1:$A$6<=A4)) =SUMPRODUCT(--($A$1:$A$6<=A5)) =SUMPRODUCT(--($A$1:$A$6<=A6)) Is there a good reason Excel handles <= comparisons differently in COUNTIF/SUMIF criteria arguments vs elsewhere? No. COUNTIF/SUMIF are poorly documented, and it'd surprise me if any of the |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF provides weird results
Thank you Harlan,
However, sumproduct does not seem to work differently than countif !!! I get the same results |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF provides weird results
On Feb 10, 9:50 am, wrote:
Sorry, it works perfectly. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
And/Or Results - Ron/Excelent | Excel Discussion (Misc queries) | |||
COUNTIF with pivot table results? | Excel Worksheet Functions | |||
autofilter does not hide unwanted results | Excel Worksheet Functions | |||
CountIf formula results in the formula itself being displayed. | Excel Worksheet Functions | |||
calculating results in formulas | Excel Discussion (Misc queries) |