CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulascountifsumproduct
gus_mac
gus_mac

Reputation: 41

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

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 value returned in this case should be 2 (rows 3 and 5).

Upvotes: 2

Views: 2436

Answers (4)

JvdV
JvdV

Reputation: 75890

To me it sounds like you could use:

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

Upvotes: 5

Tim
Tim

Reputation: 88

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;
}

Upvotes: 2

General Grievance
General Grievance

Reputation: 4998

=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)

Upvotes: 4

Marios
Marios

Reputation: 27370

Try this:

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

result

Upvotes: 2

Related Questions