Exact same CountIf formula giving different results from different cells

Question

I saw an solution where someone was using a range as the criteria for COUNTIF and while trying to understand it better I found some really odd things happening and hoping someone could explain to me what is going on. Here is the setup of the excel.

Name,,Name
Excitebike,,Excitebike
RC Pro Am,,Super Mario Brothers
Punch Out,,Duck Hunt
Super Mario Brothers        
Duck Hunt

Hopefully you can use the above to copy and paste it in. In column A there is a list of names and in column C there is a list of some of the names. In cell E1 there is a formula:

=COUNTIF($C$2:C4,$A$2:$A$6)

Then in cell E2 there is the exact same formula.

=COUNTIF($C$2:C4,$A$2:$A$6)

Here is a screen shot so you can see the formulas are identical:

So cell E1 and E2 have the exact same formula but are giving me a different result. As you can see in the first screen shot cell E1 gives a result of 0 while E2 gives a result of 1. Then if I make cell E1 into an array formula it gives a result of 1.

Why would the exact same formula in two different cells give a different result and why when changing cell E1 to an array formula would it change the result? I am using Excel 2016.

UPDATED: Additional questions.

When passing in a array into COUNTIF does it check each element in the range against each element in the criteria or does it just check row in the range against the corresponding row in the criteria?

Even when I put them in the same order, I cannot get the COUNTIF to return a number greater then 1. I would expect if the first 3 match the COUNTIF should return 3 but it is returning 1 for me. Please see below:

While rows 2, 3 and 4 match it is still giving an answer of 1.

You need to change both to array formulas or it will use the active row as the iteration point. It is because the one that is found in both is on the same row in column A as the formula in column E. Put Punch Out in C4 and you will not get 2 in either till you enter them in as arrays. — Scott Craner
To simplify your example to see what @ScottCraner is talking about, change A1:A6 to just be numbers 1 through 6. Then change E1 to just =$A$1:$A$6 and it will return 1. Drag that formula down and it will return 2, 3, 4, 5, and 6, for each respective row. — JNevill
@ScottCraner Thanks for the information. I put Punch Out in cell C4 and changed them to be array formulas and they are still both just 1. I have tried many different combinations and I cannot get them to return a number higher then 1. I am going to update my question slightly as well. — zgirod
When you put =COUNTIF(A2:A6,C2:C6) in E2 it will be evaluated as if it was =COUNTIF(A2:A6,C2). Unless A2:A6 contains the value found in C2 more than once, you will only get 1 back as an answer. The first parameter in Countif() is supposed to be a range, so you are all good there. It's that second parameter that gets evaluated as a single cell based on the range entered as the second parameter and the row in which the formula resides. That's where the strange behavior is happening. — JNevill

Steve M. Steve M. · Accepted Answer · 2016-11-14T23:03:04

I was a little surprised this worked at all. Typically I've used the "criteria" as >4 or <10 etc. Nice to know you can do a string comparison at all.

When using CountIf outside of an array formula you're going to be getting a comparison of values in adjacent cells. Typically CountIF is looking for a single criteria, not a range. At least that's the way I've always used it. eg, first formula in the cell range compared to first cells in each of the cell ranges.

Try these two experiments. Copy Super Mario Brothers from the right column to the left column and the results are going to now show 1 and 1 in the two formulas. Put it back. Move the two cells you have formulas in down one row, and you should see the results go from 1 and 0 to 1 and 1. Move it one more cell lower and it changes the values again.

I'm not sure this is what you're trying to accomplish, but copy this into the formula at and then copy down 6 rows. =COUNTIF($A$2:A$6,C2)

Exact same CountIf formula giving different results from different cells

1 Answers