Average with multiple criteria in Excel 2003

[kamedonski]

Hi,

Please look at this formula:

=SUMPRODUCT(--(B1:B10=A1),--(C1:C10=A2)+(C1:C10=A3),D1:D10)

I would like to make same criteria averaging D1:D10 (IF B1:B10 matches A1, C1:C10 matches A2 or A3, average D1:D10. Tried with:

{=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,AVERAGE(IF(B1:B10=A1,IF(OR(C1:C10=A2,C1:C10=A3),D1:D10,"")))))} but it doesn't work nested with OR.

Many thanks in advance,
Filip

 

[gsistek]

Try:
=(SUMPRODUCT(--(B1:B10=A1),--(C1:C10=A2),D1:D10)+SUMPRODUCT(--(B1:B10=A1),--(C1:C10=A3),D1:D10))/(SUMPRODUCT(--(B1:B10=A1),--(C1:C10=A2))+SUMPRODUCT(--(B1:B10=A1),--(C1:C10=A3)))

 

[Andrew Poulsom]

Did you try with + instead of OR like in your SUMPRODUCT formula?

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,AVERAGE(IF(B1:B10=A1,IF(((C1:C10=A2)+(C1:C10=A3)),D1:D10,"")))))

 

[kamedonski]

Thanks very much!