CODEWITHSUNDEEP
excelexcel-formula
zgirod
zgirod

Reputation: 4379

Exact same CountIf formula giving different results from different cells

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.

enter image description here

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:

enter image description here

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.

enter image description here

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:

enter image description here

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

Upvotes: 0

Views: 3253

Answers (1)

Steve M.
Steve M.

Reputation: 60

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)

Upvotes: 0

Related Questions