title: Exact same CountIf formula giving different results from different cells
body: <p>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.</p>
  
  <p><a href="https://i.stack.imgur.com/vqLDJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vqLDJ.png" alt="enter image description here"></a></p>
  
  <pre><code>Name,,Name
  Excitebike,,Excitebike
  RC Pro Am,,Super Mario Brothers
  Punch Out,,Duck Hunt
  Super Mario Brothers        
  Duck Hunt   
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>=COUNTIF($C$2:C4,$A$2:$A$6)
  </code></pre>
  
  <p>Then in cell E2 there is the <strong>exact same formula</strong>.</p>
  
  <pre><code>=COUNTIF($C$2:C4,$A$2:$A$6)
  </code></pre>
  
  <p>Here is a screen shot so you can see the formulas are identical:</p>
  
  <p><a href="https://i.stack.imgur.com/LTgfV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LTgfV.png" alt="enter image description here"></a></p>
  
  <p>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.</p>
  
  <p><a href="https://i.stack.imgur.com/VLYBR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VLYBR.png" alt="enter image description here"></a></p>
  
  <p>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.</p>
  
  <p>UPDATED: Additional questions.  </p>
  
  <p>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?</p>
  
  <p>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:</p>
  
  <p><a href="https://i.stack.imgur.com/XtqWo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XtqWo.png" alt="enter image description here"></a></p>
  
  <p>While rows 2, 3 and 4 match it is still giving an answer of 1.</p>