Formula not adding correct figure

[jimboy]

I want this formula to add up the lines that meet this criteria, but it only adds them up if all the lines meet the creteria, can anyone help?
{=SUM(IF(AND($I$1=A1:A29,$J$1>B1:B29,$J$1<C1:C29),D1:D29))}

 

[Aladin Akyurek]

On 2002-08-28 08:24, jimboy wrote:
I want this formula to add up the lines that meet this criteria, but it only adds them up if all the lines meet the creteria, can anyone help?
{=SUM(IF(AND($I$1=A1:A29,$J$1 > B1:B29,$J$1< C1:C29),D1:D29))}

Try:

=SUMPRODUCT(($I$1=A1:A29)*($J$1 > B1:B29)*($J$1< C1:C29),D1:D29)

AND returns a scalar value, not an array-constant which formulas operating on arrays need.

The formula above is entered normally.
This message was edited by Aladin Akyurek on 2002-08-28 08:38