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

read sumproduct explained

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--


Gary


"christian_spaceman" wrote in message
ups.com...
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

-- 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

Thanks guys - much appreciated.

Thanks guys - much appreciated.