Google Sheets – Count Non-Matching Cells in a Row

google sheets

I want to count how many cells in a range of a row (G2:N2) are not equal to the cell above it, i.e.:

G2 != G1, F2 != F1, H2 != H1, ... , N2 != N1

And then also be able to drag that down, so each row has a total of how many differences occur on this row.

Best Answer

Try this in O2 and then drag-copied down as far as needed:

=COUNTA(IFERROR(QUERY(TRANSPOSE(QUERY(G1:N2)),"Select Col2 Where Col1 Is Not Null And Col1 != Col2")))

How it works:

=QUERY(G1:N2)

A copy of the selected range from the previous row and current row is made in memory.

=TRANSPOSE(QUERY(G1:N2))

This is flipped so that the rows become columns (again, in memory only).

=QUERY(TRANSPOSE(QUERY(G1:N2)),"Select * Where Col2 Is Not Null And Col1 != Col2")

Another QUERY is run on the flipped two-column range, to limit it only the second column (which, again, is a flipped version of the current row in the Sheet) and only where cells in that column are not empty but are equal to the first column (i.e., previous row).

IFERROR(QUERY(TRANSPOSE(QUERY(G1:N2)),"Select Col2 Where Col1 Is Not Null And Col1 != Col2"))

Control for the instance where an error occurs (for instance, if you drag past the range with values accidentally).

=COUNTA(IFERROR(QUERY(TRANSPOSE(QUERY(G1:N2)),"Select Col2 Where Col1 Is Not Null And Col1 != Col2")))

Count what's left.

Edit: fixed equal/not equal

Related Topic