To be picky, I think you can live without the second set of double unary
minus.
I think you could change
=SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726))/SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726<0))
to
=SUMPRODUCT(--(A1:A726="Manchester"),M1:M726)/SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726<0))

But your version has the advantage that if the "numbers" in column M are
text that looks like numbers it will still work, whereas the shorter version
wouldn't.
Conversely, however, if the "blanks" in column M are "formula blanks" such
as ="", the shorter version of the formula will work whereas the longer one
won't.
--
David Biddulph

"Gary''s Student" wrote in message
...
=SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726))/SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726<0))

--
Gary''s Student - gsnu200909


"Pumpkin Pie" wrote:

Hello smart people

I have a list of survey names in A3:A726 and numbers in M3:M726.
Column M also contains 0 and blanks.

I am doing a summary table on the next sheet so I need a formula to show
if
it is 'SurveyName="Manchester" then average 'Autumn 09'!M3:M726 (ignoring
zeros and blanks)

Any help would be greatly appreciated as this is beyond my knowledge.

Many thaks

Paula