While working on a method of filtering dynamic ranges, I came across an unexpected behavior of COUNTIF().

I wanted to evaluate a range (A2:A9) to see which lines match a shorter list in B2:B3. Useful for further use in FILTER(), SUMPRODUCT() and similar functions. Example below: go through a list on 9 stocks; mark the two stocks that are on the shorter list of 2; return a 9-item dynamic array of 1 and 0 in the same order as on the list of original 9 stocks.

An equivalent expression in Python would be plain vanilla iteration

if criteria_str in evaluated_range:

newlist.append(1)

else:

newlist.append(0)

I got what I wanted by using COUNTIF().

However I am completely confused about the order of arguments. Based on my past experience before Excel 365 enabled common functions to return dynamic ranges, I expected that [range] is A2:A9 and [criteria] is B2:B3. However, Excel assumes the opposite. "All items" are "criteria" (red) and "some items" are "range" (blue)?

The first argument is the range to search and the second is what to search for. If there is more than one the COUNTIFS returns an array the size of the second argument.

To put it another way, your formula is the same as doing:

=COUNTIF(B$2:B$3,A2)

And dragging the formula down, but it just iterates for you. It has always been this way, you just did not "See" the results.

In older versions you could have highlighted C2:C9. Then put your formula in the formula bar and hit Ctrl-Shift-Enter and the results would spill like you see in Office 365.

CodePudding user response：

Everything works in your formula as expected. You can use both =COUNTIF(B2:B3,A2:A9) and =COUNTIF(A2:A9,B2:B3) expressions depended on wanted result. With first you get your wanted result, simply you check every element in list A2:A9 if it appears in list B2:B3 and if it does, count it as 1. With second formula you would check how many times every element in list B2:B3 does appear in list A2:A9.

I have modified your list (added UBER to A10), maybe it will be easier to understand: