# Sumproduct problem

1. ## Sumproduct problem

I have a worksheet with over 3 colums.

column A - product name = PILLI
column b = value of 1 - 50
column c - date = 1 Apr to 31May

i am using the following formula to caluclate the count of PILLI

sumproduct((trim(\$a10:\$a500)="PILLI")*(\$b10:\$b500<=7)*(\$C10:\$C500>=datevalue("4/1/2012")*(\$c10:\$c500<=datevalue("4/30/2012))))

I fid that the formula cannot refer to the specified date period. can anyone help, please.  Register To Reply

2. ## Re: Sumproduct problem

I guess the problem could be in the datevalue function which takes the date format from your computer regional options, make sure that the used format is the same.
To avoid that, use the date function instead: DATE(2012,4,1)
check also that the values in column C are actually stored as dates not text  Register To Reply

3. ## Re: Sumproduct problem

Use this formula.

=SUMPRODUCT((--(TRIM(\$A10:\$A500)="PILLI")),--(\$B10:\$B500<=7),--(\$C10:\$C500>=DATE(2012,4,1)),--(\$C10:\$C500<=DATE(2012,4,30)))  Register To Reply