check if two columns are equal by row and sum

[MetLife]

Hi,

I have two columns of data, "J10:J110" and "K10:K110".

In put column "L", I entered the formula "L10 = (J10=K10)*1" and "L11 = (J11=K11)*1", ....

Then I summed column L so that I could figure out the number of instances where they weren't matching. But I was thinking there must be a way to create a formula in a single cell that sums the total count of instances where the values are different.

There are two cases, one where the column data is "text" and the other where the column data is "numbers".

Does anyone know how to do this? I kind of wanted to avoid array formulas if possible because those were a little confusing to me.

Thanks,

 

[SanjayGMusafir]

But I was thinking there must be a way to create a formula in a single cell that sums the total count of instances where the values are different.

If you are searching for mismatches better try the below formula from Cell L10 and below -

=IF(J10<>K10,1,"")

And sum the range L10:L110

This way you can filter the records that mismatch...

 

[MetLife]

If you are searching for mismatches better try the below formula from Cell L10 and below -

=IF(J10<>K10,1,"")

And sum the range L10:L110

This way you can filter the records that mismatch...

Thats pretty much what I have in the spreadsheet.

But I want to make the spreadsheet design more succinct. So I want to replace summing range L10:L110 with a single formula in cell L9.

There must be a way of using sumproducts to do this.

 

[Sanjeev1976]

You can try :

=SUM((J10:J110<>K10:K110)*1)