Google Sheets: Count number of rows in a column that do not match corresponding row in another column?

Question

Say we have the following spreadsheet in google sheets:

a  a
b  b
c  
d  e
e  d

How would I build a formula that counts the number of rows in column B that do not match the corresponding row in column A, and are not blank? In other words I want to get the number of rows that changed to a new letter in column B. So in this example the formula should return 2.

Thank you for your help.

UPDATE:

Now suppose I have this spreadsheet:

a  a  
b  b  b
c     a
d  e  e
e  d  e

How would I build on the last formula for the third column, where the value returned is:

• (the number of rows in column 3 that don't match the corresponding row in column 2) + (if column 2 is blank, the number of rows in column 3 that do not match the corresponding row in column 1)
• and I also don't want to count blanks in the third column.

The value returned in this case should be 2 (rows 3 and 5).

Answer 1

=IFNA(ROWS(FILTER(A:B,
  (A:A<>B:B)*
  (B:B<>"")
)),0)

1. FILTER by matching conditions * for AND + for OR.
2. ROWS counts rows
3. IFNA returns 0 if nothing was found.

or with QUERY

=INDEX(QUERY(A:B,"select count(B) where B<>A"),2)

Answer 2

To me it sounds like you could use:

=SUMPRODUCT((B:B<>"")*(B:B<>A:A))

Answer 3

Try this:

=ARRAYFORMULA(COUNTA($B$1:$B)-SUM(COUNTIFS($A$1:$A, $B$1:$B,$B$1:$B,"<>")))

Answer 4

I see 2 ways to complete this.

First you could add a function to each row to return 1 or 0 if the value changed and was not blank and then sum results. This unfortunately adds a messy column in your spreadsheet.

=if(A1<>IF(ISBLANK(B1),A1,B1),1,0)

Second you could create a function where you would pass the range as a string. The call from the worksheet would look like this:

=myFunction("A1:B5")

Then create a script by opening Tools -> Script editor and use something like this

function myFunction(r) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(r);
  var numRows = range.getNumRows();
  var areDifferent = 0;
  
  for (let i=1; i<= numRows; i++) {
    let currentValue = range.getCell(i,1).getValue();
    let cmpValue = range.getCell(i,2).getValue();
    if ((currentValue != cmpValue) && (cmpValue != "")) {
      areDifferent++;
    }
  }
  return areDifferent;
}