With a multiplication * you don't need the double unary minus.
--(B1:B3<0)*--(B5:B7="y")
is the same as
(B1:B3<0)*(B5:B7="y")

The double unary minus is there to force an arithmetic operation where you
weren't already doing one, but in your case you are doing the
multiplication.
--
David Biddulph

"Sean Timmons" wrote in message
...
=SUMPRODUCT(--(A1:A3=A10),--(B1:B3<0)*--(B5:B7="y"))+SUMPRODUCT(--(A1:A3=A10),--(C1:C3<0)*--(C5:C7="y"))

Will get you a count for company 1 same formula but change A10 to A11 for
company 2. Merely $ the rest of the formulas as needed to keep your ranges
constant.


"laandmc" wrote:

I am trying to count a certain number of cells based on whether a
condition
is satisfied. I want a formular rather like the sumproduct but
countproduct
instead.

Please see example below.

I want to count for both companies COMP1 and COMP2 how many non zero
cells
there are in the first table, but only when the corresponding cell in the
2nd
table has a "y" in that cell. at the moment I can do this using if
functions
with a countif function in the middle. However I am doing this over a
period
of 3 years so it makes my formular ridiculously long.

A B C
1 JAN FEB
2 COMP 1 1 2
3 COMP 2 1 0
4
5 JAN FEB
6 COMP 1 y
7 COMP 2 y y
8
9 count none zero with "y"
10 COMP 1 1 <--
=IF(B6="y",COUNTIF(B2,"<0"),0)+IF(C6="y",COUNTIF( C2,"<0"),0)
11 COMP 2 1 <--
=IF(B7="y",COUNTIF(B7,"<0"),0)+IF(C7="y",COUNTIF( C7,"<0"),0)

I want a way to do it for example = countproduct(b2:c2,(b6:c6="y")*1))

Can anyone help me?? is there another way to do this?

Laurence