How to get average across sheets with #N/A in data?

[Goldcar400]

Hello

How to get the average() function to work across sheets with #N/A in data? The result is in sheet cc3 and data are in sheets aa1 and bb2. The data is in non contiguous cells, one cell ranges.

See attached file.

Regards
Tony

 

[pecoflyer]

Not verified
=AGGREGATE(1,6,'aa1'!C7,'aa1'!C9,'aa1'!E9,'bb2'!C7,'bb2'!C9,'bb2'!E9)

 

[Goldcar400]

Thanks pecoflyer! It works now.