Help. Don't know why this array formula does not work.

Hello,

I have three columns with data: I, U, and Y. Here are a couple of lines of data from these fields:

I U Y
02-01-001 5 4
02-02-002 22 1

I have this formula so that an average is calculated on the values in Y if conditions are met in fields I and U:

{=AVERAGE(IF(AND(raw!$I$2:raw!$I$1000="02-02-002",raw!$U$2:raw!$U$1000=22),raw!$Y$2:raw!$Y$1000))}
So, if I=02-02-002 and U=22, I should get an averge, in this case with just one record, 1.

It is not returning this value. Instead it is returning 0. Can someone find why this is not working? Thank you.

BTW, I currently have column I formatted to be text. If I remove the quotes around 02-02-002 from the formula above, it automatically converts it to 2-2-2. I figured the easiest thing is to make the field a text field.

Frank

=SUMPRODUCT(--(I2:I1000="02-02-002"),--(U2:U1000),Y2:Y1000)/SUMPRODUCT(--(I2:I1000="02-02-002"),--(U2:U1000=22))

Hope that helps

Thank you for your help, but that is not doing what I need at all. I get a value in the thousands. Please let me know if there is something else I can try.

Just to clarify, I have been using the formula below without problem. What I want to do to this formula is add another condition based on column I. I took my best shot at it using AND, but maybe I did not do it correctly.

This formula works fine and give me the value I expect given the sample data I provided above. It returns the value 1.

{=AVERAGE(IF(raw!$U$2:raw!$U$1000=5,raw!$Y$2:raw!$Y$1000))}

I would just like to add the column I condition. Thanks.