Calc : Need XNOR formula 100 cells wide 2 deep (Correlation function doesn't work) [SOLVED]

English
#1

I need a formula that fits in a single cell. I have two rows of 1 and 0 data 100 cells wide. That is 100 pairs of 1 and 0 data, each top row cell (1 or 0) directly over a bottom row cell(1 or 0), 100 cells wide left to right. I would like a single cell formula that returns how many of the top row 1 or 0 data matches the bottom row 1 and 0 data. This might kinda look like the correlation function but it would be XOR or XNOR and not correlation. I suppose I could put 100 XOR formulas in one cell, but there should be a better formula than that. This is for my personal use.

Thanks All - Mike

#2

Use my favorite function:
=SUMPRODUCT(I4:N104=I5:N105)
where I4:N104 and I5:N105 are the two rows.

1 Like
#3

I’m on my way out the door right now, but I will give it a try later today. Thanks!

I tried this when I got home, and it worked correctly! This is an excellent solution. Thanks!

  • Mike Colley

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.