Unexpected COUNTIF behavior when mixing numbers and text (EXCEL)

Question

This question is about how the Excel COUNTIF function processes different data types when used as an array formula.

There are many good posts detailing the use of COUNTIF for tasks such as extracting unique values ​​from a list, such as this post . I managed to use examples from this and other posts to solve specific problems, but I am trying to get a deeper understanding of array formulas in order to adapt my formulas to new needs.

I came across a peculiar COUNTIF behavior. In general, Excel seems to treat strings as "greater than" numbers, so the following examples are true:

Cell Formula Returns =1<2 TRUE ="a"<"b" TRUE ="a">"b" FALSE =1<"b" TRUE 

Now suppose the range A1: A6 contains the following data set:

 1 2 3 A B C 

For each cell of this set, I want to check how many of all the cells in the set are less than or equal to this cell (a useful method in more complex formulas). I introduce the following array formula in the range B1: B6:

{=COUNTIF($A$1:$A$6,"<="&$A$1:$A$6)} (CTRL + SHIFT + ENTER)

According to the examples of comparing numbers and strings above (also shown in column D below), I expect the result shown below to look like column C. However, the array formula returns the result shown in column B, which assumes the rows and numeric elements are counted separately using the COUNTIF array.

 Column A Column B Column C Column D 1 1 1 A1<"C" = TRUE 2 2 2 A2<"C" = TRUE 3 3 3 A3<"C" = TRUE A 1 4 A4<"C" = TRUE B 2 5 A5<"C" = TRUE C 3 6 A6<"C" = FALSE 

So, the question is how to output in column C? ( EDIT: To clarify, I'm specifically looking for solutions that use the properties of the COUNTIF array.)

Any insight into why a massive COUNTIF appears to behave differently than single-cell examples is also much appreciated.

NOTE. I have translated examples from a non-English version of Excel, so I apologize in advance for any typos.

PS. For the background, I ran into this problem when I tried to build a formula that would extract unique values ​​from a list with possible duplicates and sort the unique values ​​in numerical / alphabetical order. My real solution is to do this in two steps. Here one solution is proposed, how to do it in one step .

Answer 1

Different behavior can be easily shown if you compare

=COUNTIF($A$1:$A$6,"<=A")

with

{=COUNT(IF($A$1:$A$6<="A",1))}

The first will only get text values ​​from $A$1:$A$6 , because it is clearly text for comparison, and it will quickly ignore other values. =COUNTIF($A$1:$A$6,"<=3") will receive only numeric values ​​from $A$1:$A$6 for the same reasons. Even if the criterion were concatenation with reference to the cell, then concatenation would be the first process and would lead to either "<= A" or "<= 3". Therefore, it is always clear what to compare, text or numbers.

The second first needs an array of comparisons, then performs an IF , gets an array of 1 or FALSE and then counts. But "A" can also be a cell reference. Therefore, it is not clear what to compare at the beginning, and the first array should compare all the values ​​in $A$1:$A$6 .

So COUNTIF(S) and SUMIF(S) cannot be used to compare mixed text and numeric data.

The solution is already shown XOR LX.

Btw .: via PS. For a background PS. For a background you should consider the following solution from the German Excel website: http://www.excelformeln.de/formeln.html?welcher=236 .

In the above example:

B2 formula down

 {=INDEX($A$2:$A$99,MATCH(LARGE(COUNTIF(A$2:A$99,">="&A$2:A$99)+99*ISNUMBER(A$2:A$99),ROWS($1:1)),COUNTIF(A$2:A$99,">="&A$2:A$99)+99*ISNUMBER(A$2:A$99),0))&""} 

In this solution, COUNTIF compared with >= , so the largest text or number will be considered the lowest, and therefore get the lowest position. All line item positions are added from 99. Thus, they are larger than all possible text positions. So we have a sorted sorted array. Then, using LARGE , the list is created from the highest to the lowest.

Answer 2

First of all, a superbly posed question and an interesting topic to download.

I also raised an eyebrow when I first encountered this behavior of the COUNTIF(S) / SUMIF(S) functions. In their defense, I suppose, we could build situations in which we really want strings and numbers to be counted separately.

To build the required array in the formula, you will need something like:

MMULT(0+(TRANSPOSE($A$1:$A$6)<=$A$1:$A$6),ROW($A$1:$A$6)^0)

although note that the necessary transposition will mean that any setting that includes this construct will require fixing with the CSE.

Hello

Answer 3

I doubt countif is the right function for what you want to achieve here.

try this (ctrl + shift + enter):

 ={SUM(IF(A1>=$A$1:$A$6,1,0))} 

You'll get

 1 2 3 4 5 6 

PS: CountIf is basically an array function inside. Using it in another array function leads to many array functions, and their behavior becomes complicated. Array functions are best used with a clear logical path.

As tested in Excel 2013, you will only get 1 in all results instead of what was suggested in column B.

Currently, in the function you provide, countif cannot determine which cell to compare with which cell. Array functions expand ranges and then perform the provided action. Therefore, it compares each cell with the same cell and leads to 1.

Answer 4

Try FormulaArray in B1 , then copy to B6 :

 =SUM(($A$1:$A$6<=$A1)*1)