# Count how many times in a row a cell does not equal the cell above it

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:

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