sumproduct question - kind of...

P

Poody

I needed a formula (that I was able to find on this group, btw). The formula
from the gropu was:

=sumproduct(--(B2:B10=1),--(F2:F10="Completed"))

My question is, what does the -- do, and can I find more information about
this in the help files? If it is in help, what is it called, as I haven't
been able to find anything about it - even when looking at the sumproduct
help information.

Thanks,
 
D

daddylonglegs

-- is known as double unary minus, its function in a formula like

=sumproduct(--(B2:B10=1),--(F2:F10="Completed"))

is to convert an array of TRUE/FALSE values to 1/0, e.g.

(B2:B10=1) returns an array like

{TRUE,FALSE,TRUE,TRUE,FALSE,FALSE,TRUE,TRUE,TRUE}

-- coerces this to

{1,0,1,1,0,0,1,1,1}

the same happens with

--(F2:F10="Completed")

SUMPRODUCT then multiplies the two arrays (giving 1 only when both
arrays are 1) and then sums the resultant single array thereby
effectively counting the rows where both conditions are true. The
arrays can be co-erced in other ways, e.g. by adding zero or
multiplying by 1, so you could as easily use

=sumproduct((B2:B10=1)+0,(F2:F10="Completed")*1)
 
Top