title: How do I get the count of non matching cells in a row between two sheets?
body: <p>I have two sheets, Sheet A containing a truth table like such:</p>
  
  <pre><code>    A B C D E
  Foo T F F T F
  Bar T F F F F
  </code></pre>
  
  <p>Sheet B contains another table:</p>
  
  <pre><code>A B C D E
  T F F T F
  </code></pre>
  
  <p>I need to add a column to table A counting how many cells match the corresponding cell in sheet B, as such:</p>
  
  <pre><code>Foo T F F T F 2
  Bar T F F F F 1
  </code></pre>
  
  <p>Best I could come up with for the first count cell was </p>
  
  <pre><code>=COUNTIF(B2:F2,B2:F2=INDIRECT("sheet-b!A2:E2"))
  </code></pre>
  
  <p>but this gives me <code>0</code> even though it should be 2. </p>
  
  <p><em>Edit:</em>
  I was able to come up with this, which correctly counts the number of differing cells:</p>
  
  <pre><code>=SUM(ARRAYFORMULA(IF(B2:F2=INDIRECT("sheet-b!A2:E2"),1,0)))
  </code></pre>
  
  <p>The next thing I want is to add an AND statement, and count the cell if it matches or if the <code>sheet-b</code> cell is F:</p>
  
  <pre><code>=SUM(ARRAYFORMULA(IF(AND("F"=INDIRECT("sheet-b!A2:E2"),B5:F5=INDIRECT("sheet-b!A2:E2")),1,0)))
  </code></pre>
  
  <p>However it doesn't seem like i can use an AND statement in this formula. Even the following gives a sum of zero:</p>
  
  <pre><code>=SUM(ARRAYFORMULA(IF(AND(TRUE,B2:F2=INDIRECT("sheet-b!A2:E2")),1,0)))
  </code></pre>
  
  <p>how come?</p>
  
  <p><em>Edit2</em>: <a href="https://docs.google.com/spreadsheets/d/1V2dHzqWzBStg3NmJEXuTzuhw2g929IJkqYFPEH2cDfM/edit?usp=sharing" rel="nofollow noreferrer">Link to example spreadsheet</a></p>