Counting using multiple criteria

[Shawn]

I need an array formula that will do the following. Count (not sum) the
number of cells in column C that are above the value of 0 if the adjacent
cells in Column A are equal to a value of 1.

 

[TomPl]

=sumproduct(--(A1:A65536=1),--(C1:C65536>0))

 

[Shawn]

What is the significance of the "--"s? Also, what if I want to change the
formula as follows?
=sumproduct(--(A1:A65536=B1),--(C1:C65536>0)) as opposed to
=sumproduct(--(A1:A65536=1),--(C1:C65536>0))

 

[Dave Peterson]

First, you can't use the entire column in xl2003 and older. So adjust your
ranges accordingly.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html