If and Sumproduct

[ronnomad]

I have about 250 pieces of equipment that are serviced periodically every 3,
6 or 12 months. Each piece is located in a specific department and I want to
identify the number of pieces serviced in a defined time frame by department.

Column C contains service dates; column E contains the department names and
all columns between A & E have headings. The other columns have info not
related to the query. K1 & K2 contain the beginning and end dates for the
query.

The formula I wrote:
=IF(E$1:E$250="Research",SUMPRODUCT(C$1:C$250>=K$1)*(C$1:C$250<=K$2),0)
returns the value of 0. What have I done wrong?

Thanks,

Ron

 

[PCLIVE]

May be this:

=SUMPRODUCT(--(E$1:E$250="Research"),--(C$1:C$250>=K$1),--(C$1:C$250<=K$2))

 

[ronnomad]

Perfect! Thanks.

May be this:

=SUMPRODUCT(--(E$1:E$250="Research"),--(C$1:C$250>=K$1),--(C$1:C$250<=K$2))