You can't, it takes an array formula or a combination of sum and countif but
an array formula demands a range so does countif, there is one way using a
workaround, here's an example using different ranges


=SUMPRODUCT(--(LARGE((B1:B4,D1:E4,G1:G4),ROW(INDIRECT("1:"&COUNT (B1:B4,D1:E4,G1:G4))))<0))

if they all are single cells just put a comma between them, so to get
average of the above range

=SUM(B1:B4,D1:E4,G1:G4)/SUMPRODUCT(--(LARGE((B1:B4,D1:E4,G1:G4),ROW(INDIRECT("1:"&COUNT (B1:B4,D1:E4,G1:G4))))<0))



--
Regards,

Peo Sjoblom


"Keithlearn" wrote in message
...
I am trying to average cells that are non contingous, and also need to
exlcude any zero's in the mix. All the posts I have read count on either
sumproduct or countif both of which don't seem to be able to accept non
contingous cells. Is there something that might be able to help.

An example of the simple average formula is

=AVERAGE(E92,E71,E50,E29,E8)

Thanks,

Keith