TRY THIS :

=SUMPRODUCT( ($F$52:$F$111=$E$5) * ($K$52:$K$111) ) / SUMPRODUCT(
($F$52:$F$111=$E$5) * (ISNUMBER($K$52:$K$111) ) )



"Mifty" a écrit dans le message de groupe
de discussion : ...
Hi,

I'm using the following to average data in K if value in F = value in E5.

=SUMPRODUCT(($F$52:$F$107=$E$5)*$K$52:$K$107)/SUMPRODUCT(--($F$52:$F$107=$E$5))

I'm intending to use the same formula to calculate averages in columns I
to
N in about 45 sheets all with the same layout and different data.

Column F is gender and I to N contain a series of datapoints (some of
which
are missing).
I've tried as far as possible to sort the data so that the missing points
are at the bottom of rows and then I alter the range in the formula to
suit.

I'm on sheet 5 now and I'm stumped, L109 to N111 and K108 have no data but
there is data in K109 to K111.

I've tried adding a second range to the formula but not having much luck
(although I'm guessing I'm lucky to have got to sheet 5 without this
problem).

Hoping some kind soul can help :-)

Cheers
Mifty

--
Mifty