-3

I have two ranges and I want to count how many values have changed between them. I am trying to find a function that compares the two ranges and returns the number of cells that change. I know I could do a countif and then sum that but I am looking to get the answer in a single cell.

I am looking to be able to count the highlighted cells, which in this case is 20. enter image description here

Improve this question
1
  • As in changed position, or changed value? Would Sheet1 {A,B,C,D} and Sheet2 {A,C,D,B} result in 3 changes, or none? Please show some examples.
    – P.b
    Commented Jun 11 at 14:40

1 Answer 1

Reset to default
1

=sumproduct(--(Range1 <> Range 2)) works

Range1 <> Range2 returns a bunch true/false values and the -- turns it into 0,1 values

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .