Then welcome to the board!!!
The two minus signs are used for coercion effects. It would be the same thing is you used +0 or *1. It's best described in an array-type formula (as SUMPRODUCT naturally acts).
If you take an array of values and check it against something it will return a TRUE/FALSE value. Either they match or they don't. We then want to Count all of these occurances, but need to coerce the Boolean (True/False) value into a Binary (1/0) value for that, which is where the double negation comes into play. Let's look at the following formula ..
=SUMPRODUCT(--(A1:A5="YUPPER"))
If we have in col A ...
A1 NOPE
A2 YUPPER
A3 YUPPER
A4 NOPE
A5 YUPPER
As you can see, there would be three correct answers that match. This is done like the following..
=SUMPRODUCT(--(A1:A5="YUPPER"))
...
=SUMPRODUCT(--({"NOPE","YUPPER","YUPPER","NOPE","YUPPER"}="YUPPER"))
..
=SUMPRODUCT(--({FALSE,TRUE,TRUE,FALSE,TRUE}="YUPPER"))
..
=SUMPRODUCT({0,1,1,0,1})
We can do this because of Excel's ability to say that a True Boolean value is equivelant to a Binary 1, and a False Boolean value is equivelant to a Binary 0 .
You can set this up to more than one condition, which will check rows against rows and columns against columns (meaning it is not cumulative across arrays). So in the case of the formula ...
=SUMPRODUCT(--($B$2:$B$8=F2),--($D$2:$D$8="X"))
.. we just added another array (seperated by a comma in the syntax of SUMPRODUCT) and checked column B to see if it matched the value in F2, we matched (or aligned) that array against another that checked column D for an X. Those cells that had a match in each corresponding row for each condition received another 1 in the count.
Please note, we did use the comma in this case where we could have also used the * symbol. We don't need to here because of how SUMPRODUCT works, it does the array multiplication naturally; the help files describe this process well (at least in 2002). The equivelant Logical operation that * produces is AND. To produce an OR type of logical expression, the + sign can be substituted. For an example take the formula version ...
=SUMPRODUCT(--($B$2:$B$8=F2)+($D$2:$D$8="X"))
We make sure and take out the comma and leave both conditions inside of one array as we can take one or the other. In our first example of the YUPPER cells, we'll add another condition...
In col A ...
A1 NOPE
A2 YUPPER
A3 YUPPER
A4 NOPE
A5 YUPPER
In col B ...
B1 MAYBE
B2 NOPE
B3 NOPE
B4 NOPE
B5 MAYBE
We'll count all occurances of YUPPER in col A that match MAYBE in col B.
=SUMPRODUCT(--(A1:A5="YUPPER"),--(B1:B5="MAYBE"))
...
=SUMPRODUCT(--({"NOPE","YUPPER","YUPPER","NOPE","YUPPER"}="YUPPER"),--({"MAYBE","NOPE","NOPE","NOPE","MAYBE"}))
..
=SUMPRODUCT(--({FALSE,TRUE,TRUE,FALSE,TRUE}="YUPPER"),--(TRUE,FALSE,FALSE,FALSE,TRUE}))
..
=SUMPRODUCT({0,1,1,0,1},{1,0,0,0,1})
..
{snip}
We take these two arrays and line them up, a visual like so ..
{0,1,1,0,1}
{1,0,0,0,1}
multiply them down
{0*1=0,1*0=0,1*0=0,0*0=0,1*1=1}
{0,0,0,0,1}
{/snip}
=SUMPRODUCT({0,0,0,0,1})
..
1
Now to count all occurances of YUPPER in col A AND all occurances of MAYBE in col B ...
=SUMPRODUCT(--(A1:A5="YUPPER")+(B1:B5="MAYBE"))
I leave the formula audit of this one to you ...
Reply With Quote
