[quote=Red Devil]
I "pinched" it from Carbob's sheet
=SUMPRODUCT(--(B2:L2=B3:L3))
As I said in an earlier reply to Bob, when you use the help file in Excel it just shows:
=SUMPRODUCT(Array1,Array2) however Bob's introduction of the -- and = makes the function look for repeats. What I would like to know is what does the -- do?
[/quote]
RD, I asked this same question on
WWW.Mrexcel.com, this is the response I got.
ANSWER........
It's called a double unary minus. Minus minus is the same as plus.
It coerces a TRUE/FALSE boolean into 1/0. The same could be achieved
by multiplying by 1 or adding zero. But -- is in vogue at the moment because
apparently it is a nanosecond or two quicker than the alternatives.
It would more likely be written:
=SUMPRODUCT(($B$2:$B$8=F2)*($D$2:$D$8="X"))
It compares each cell in B2:B8 with F2 and returns an array of TRUE/FALSE values.
Then it compares each cell in D2:D8 with "X" and returns another (equally sized) array
of TRUE/FALSE values. The 2 arrays are multiplied together, and in the process TRUE is
coerced to 1 and FALSE to zero. That results in an array of 1/0 values
(1*1=1, 1*0=0, 0*1=0, 0*0=0). This array is then summed. The effect is a
count of the two conditions being TRUE.