-- coerces the boolean values from the tests into 0's (False) and 1's (True).
An alternate way to write the expression would be
=SUMPRODUCT((B1:B1000="Chris")*(C1:C1000="working")*(D1:D1000="laptop"))
I prefer this approach, because it more readily generalizes to more
complicated conditions. "*" corresponds exactly to a logical AND, "+"
corresponds (sort of) to a logical OR, and you can add parentheses
appropriately to control the order of evaluation.
I say that "+" only "sort of" corresponds to OR, because TRUE+TRUE = 2
instead of 1, so you may need to convert back to an array of 0|1 values (such
as wrapping the expression in the SIGN function).
Jerry
"christian_spaceman" wrote:
> Hi,
> Quick question really... I wanted to 'countif ' with several criteria
> and found the following on the mr excel boards:
> =SUMPRODUCT(--(B1:B1000="Chris"),--(C1:C1000="working"),--
> (D1:D1000="laptop"))
> (it counts the rows which have Chris working with a laptop).
> It worked really well, but I've been unable to find a description of
> the -- operator? (neither the help nor google have been able to help
> me so far...)
> Could anyone enlighten me?
> Thanks in advance,
> Chris
>
post_a_reply
| Fri, 23 May 2008 01:02:00 GMT |