SUMPRODUCT to get an average

[Geoff Taylor]

Hi
This formula works fine...
=SUMPRODUCT(--(Data!$N$6:$N$33805=Sheet1!$A13),--(Data!$J$6:$J$33805=Sheet1!$B$8),--(Data!$M$6:$M$33805=Sheet1!$B$7),--(Data!$L$6:$L$33805))

It gives me a total value for all rows that meet the 3 criteria elements. Lovely. Now I would like to find the average of these values and I can't seem to get the syntax correct. Help please.

Thanks

 

[Misca]

Try:
=SUMPRODUCT(--(Data!$N$6:$N$33805=Sheet1!$A13),--(Data!$J$6:$J$33805=Sheet1!$B$8),--(Data!$M$6:$M$33805=Sheet1!$B$7),--(Data!$L$6:$L$33805))/SUMPRODUCT(--(Data!$N$6:$N$33805=Sheet1!$A13),--(Data!$J$6:$J$33805=Sheet1!$B$8),--(Data!$M$6:$M$33805=Sheet1!$B$7))

Actually, you should be able to leave out the last double negative from your original formula: The double negative is used to turn TRUEs and FALSEs to 1s and 0s. The last column should return numeric values anyway or the formula wouldn't make any sense.

 

[barry houdini]

You could use this array formula

=AVERAGE(IF(Data!$N$6:$N$33805=Sheet1!$A13,IF(Data!$J$6:$J$33805=Sheet1!$B$8,IF(Data!$M$6:$M$33805=Sheet1!$B$7, Data!$L$6:$L$33805))))

confirmed with CTRL+SHIFT+ENTER

[or in Excel 2007 or later you could use AVERAGEIFS function]

 

[scottylad2]

=SUMPRODUCT(--(Data!$N$6:$N$33805=Sheet1!$A13),--(Data!$J$6:$J$33805=Sheet1!$B$8),--(Data!$M$6:$M$33805=Sheet1!$B$7),--(Data!$L$6:$L$33805))/ countif same criteria

 

[Geoff Taylor]

Thank you both for the quick responses.