COUNTIF a cell in a row does not equal the cell to right of it.

[tamdunk]

Hi, I'm trying to make a function to count the instances where one cell has a different value to the cell beside it. the columns are effectively pairs so I'm only looking to where count every pair of cells do not match. I tried the following formula, but cant get the syntax right, am not sure if it would even work.

=COUNTIF(MOD(G2:R2,2)=0, (MOD(G2:R2,2)=0) <> (MOD(G2:R2, 2) =1))

I managed to get the required outcome by making a macro,

For I = Range("G2").Column To Range("R2").Column Step 2
         If Cells(2, I).Value <> Cells(2, (I + 1)).Value Then
         difTot = difTot + 1
         End If
Next I

but I'd rather have it as a formula if at all possible. Any help would be appreciated.

 

[Joe4]

Are you counting every other row?
How far down do you need to go?

 

[steve the fish]

Are we talking about comparing columns or rows? Columns this will work but change the range to suit:

=SUMPRODUCT(--(A:A<>B:B)

 

[tamdunk]

G        H        I        J        L     K       DIFFERENCE
GOOD     GOOD     GOOD    BAD    GOOD     BAD          2

This is what I'm getting at, so because I & J and L & K dont match, the total in difference would be 2

 

[tamdunk]

Hi we're comparing every 2nd column with the one beside it so is column G different to H, is I different to J, is K different L etc. And counting each occurance where they don't match. It'll be for a couple of hundred rows in the end.

 

[steve the fish]

You could get your example like this:

=SUMPRODUCT(--(G1:K1<>H1:L1),MOD(COLUMN(G1:K1),2))