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.

  • Alfo, did the formula I provided below work for you? If so, would you mark it as the correct answer? Thanks. – Erik Tyler Oct 29 '18 at 6:45

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:


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


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.