Tolerant average (ignore #NA, etc.)

Question

I want to calculate the average over a range (B1:B12 or C1:C12 in the figure), excluding:

1. Cells not being numeric, including Empty strings, Blank cells with no contents, #NA, text, etc. (B1+B8:B12 or C1+C8:C12 here).
2. Cells for which corresponding cells in a range (A1:A12 here) have values outside an interval ([7,35] here). This would further exclude B2:B3 or C2:C3. At this point, cells in column A may contain numbers or have no contents.

I think it is not possible to use any built-in AVERAGE-like function. Then, I tried calculating the sum, the count, and divide. I can calculate the count (F2 and F7), but not the sum (F3), when I have #N/A in the range, e.g.

How can I do this?

Notes:

1. Column G shows the formulas in column F.
2. I cannot filter and use SUBTOTAL.
3. B8:C8 contain Blank cells with no contents, B9:C9 contain Empty strings.
4. I am looking for (non-user defined) formulas, i.e., non-VBA.

Answer 1

From https://stackoverflow.com/a/30242599/2103990:

Providing you are using Excel 2010 and above the AGGREGATE function can be optioned to ignore all errors.

=AGGREGATE(1, 6, A1:A5)