Number of differences between two array

[catalincirjan]

Hello,

I have the task of counting the number of changes/updated in a range K3:K500.
I do a snapshot (aka Copy Paste) at the beging of the the week and create a new sheet "Reference"

The solution I thought about was
=SUM(IF(Tracks!K3:K500<>Reference!K3:K500,1,0)

The logic was that it would go and compare Tracks!K3 with Reference!K3 and if they were different it would return 1. And so on until K500 and do a SUM at the end.
This function always is showing me 0 as a result.

Note: cells contain information in text (Ex: Completed, pending approval, schedueled for the 29th etc -> nothing standardized)
Note2: I did manage to make a more rustic version of this in a seperate sheet: "Calculus" where in cell A3 i put =IF(Tracks!K3<>Reference!K3,1,0) -> then I pun the formula in all 500 rows. At the end I do a sum. But there MUST be a simpler solution.

Hope I explained the issue well enough

 

[jasonb75]

Did you array confirm your formula? If not then it will only show the result based on one row (this will be the same row that the formula is located in).

Another option would be to use sumproduct, which doesn't need to be array confirmed.

=SUMPRODUCT(--(Tracks!K3:K500<>Reference!K3:K500))

 

[catalincirjan]

Not sure how to array confirm. I must admit, to my shame, it's the first time I hear about it.
The SUMPRODUCT works, so thank you very much for that!

 

[jasonb75]

Not sure how to array confirm. I must admit, to my shame, it's the first time I hear about it.

It's unlikely that you would hear about it if you have never had cause to use it before.

All you need to do is type in your formula as normal, then hold down Ctrl and Shift when you press Enter.

 

[catalincirjan]

After array confirming the initial formula, it also worked.
Thank you very much Jasonb75!