Say I have this simple table:

I want to calculate the count of pairs that don't equal to each other. In this example only the first row has a matching pair, other 3 pairs don't match, so it should return 3. To add a bit more complexity, both column A and B are dynamic ranges (with same length).

I tried to use countif(A1:A4<>B1:B4, TRUE) but it doesn't work. What would be a simple way to achieve this?

Thanks!

CodePudding user response：

Use SUMPRODUCT:

=SUMPRODUCT(--(A1:A4<>B1:B4))