Hello all.
I'm using SUMPRODUCT to give me a tally of how many times a given value in row X corresponds column-wise with another value in row Y. This involves several numeric values (between 0 and 4) and one non-numeric value ("Ø"). Here are some examples of the formulae which do work:
=SUMPRODUCT((D109:X109="Ø")*(D107:X107=2))
=SUMPRODUCT(($D120:$X120=0)*($D118:$X118=1))
In fact, all of the formulae work perfectly except for the one needed to tally corresponding 0s in both rows. I have tried several declinations of the formula, but none give me the expected result (unless by coincidence). I believe the problem stems from SUMPRODUCT recognizing the non-numerical cells as 0s, but I can find no way of solving it, and my different solutions give different results but none of them the expected one.
In the below example, you can see two formulae I have tried. The expected result is 1, corresponding to the co-occurrence of 0 in V96 and V98.
(Please excuse my French... Excel! SOMMEPROD = SUMPRODUCT, and semi-colons ";" function as commas ",")
SOMMEPROD 0.png
SOMMEPROD 2.png
Any help is very much appreciated.
Bookmarks