Formula problem

[Helen]

I have an array formula which doesn't seem to work, and I think it might be because I am trying to include the AND function in it as I am trying to look up 3 different things.

{=SUM(IF(FORD!$A$90:$A$141='SUMMARY-with changes'!A8;IF(FORD!$E$90:$E$141=AND($D$6;$E$6;$F$6);FORD!$J$90:$J$141;0);0))}

Is there something wrong with my formula?

Helen

 

[Guest]

Helen,

What are your three criteria's you're looking for?

 

[Helen]

Hello,

I have 4 years 1999-2002. I want to say if the project decsription = a cell, and if a range of dates (1999-2001) = cell range, then sum the amounts.

I just thought that you must be able to add the AND function to sum what I require?

 

[Aladin Akyurek]

On 2002-03-11 10:12, Helen wrote:
Hello,

I have 4 years 1999-2002. I want to say if the project decsription = a cell, and if a range of dates (1999-2001) = cell range, then sum the amounts.

I just thought that you must be able to add the AND function to sum what I require?

Helen,

A few approaches are possible to compute a multiconditional sum, one being formula-based:

An example would be:


=SUMPRODUCT((A2:A10=E1)*(B2:B10>=E2)*(B2:B10<=E3),C2:C10)

This "non-array" formula sums C-cells whose corresponding A-cells meet the condition in E1 and B-cells which are between E2 and E3 inclusive (such as dates or just years).

Aladin