Forum Discussion
Comparing two ranges of cells in an if function
It could be
=IF(SUMPRODUCT(--($A$2:$R$2<>$A$3:$R$3)),"",S3)
I tried this one, because it looks much easier than above but it always returns the true value even when I check it and I know its not. It looks like it should work. (Unless sumproduct function counts everything as a number value and doesnt work well with text- I dont know enough about it)
Thank you though. I do appreciate the help
Hi Joseph,
Yes, SUM ignores text and logical values, as well as SUMPRODUCT when it sums multiplication of the arrays.
In our case we have no one text value returning to SUMPRODUCT, we have only logical values TRUE or FALSE as result of cells comparasion. Logical values also don't work, thus with double dash we convert TRUE and FALSE in 1 and 0 accordingly (same result if we multiply resulting array on 1).
Finally SUMPRODUCT receives only numeric 1/0 array.
Back to your sample - in attached the formula works correctly.
SergeiBaklanthat would sure save a lot of typing (learned something new) and good pick up as I forgot to set it to S3!
Cheers
Damien
