How to compute an average of numbers in columns that have numbers, text, blanks and NA?

Dear Excel Gurus,

I have a table with a large number of columns. Each column has some cells with numerical values and some cells with text, blanks or #N/A. For each column, I would like to compute the average of the numerical values in it. I can think of a couple of ways to do it (see below), but these ways "do not scale" if I have a large number of columns and a large number of possible numerical values. In the attached example I showed a very small table where I only have 2 columns (Result 1 and Result 2) that I need to summarize and where I know that all the numbers in these columns are integers between 1 and 5.

1) I can set up a pivot table for each column (see "pivots" tab of the attached example). In each pivot I can filter out the non-numerical values in the column and then average the column. This clearly does not scale if I have many columns and/or if I have many different numerical values that can occur in each column (as opposed to the situation where I know that the only values that can occur are, say 1,2,3,4, or 5).
2) Use sumif and countif to compute the sum and the number of numerical values for each column of the table, then compute the average for each column. I show this in rows 2 through 4 of "data" worksheet of the attached example. This clearly does not scale if I have many different numerical values that can occur in the Result 1 and Result 2 (as opposed to the situation where I know that the only values that can occur are, say 1,2,3,4, or 5).

What I really want to do is something like "=COUNTIF(Table1[Result 1], isnumber())" and "=SUMIF(Table1[Result 1], isnumber())" But trying this gives me an error.

Please let me know if there is a good way to do this (preferably without macros or VBA code).

Many thanks!

Studiosacountif sumif isnumber.xlsx

Jakobshavn,
Thank you very much. This works!

I am a bit puzzled what exactly it does. Looks like the if formula constructs a virtual table in computer memory where it applies the if statement element by element to the individual cells of column a. I am still shaky in my understanding of the array formula concept.

Studiosa

Originally Posted by Jakobshavn

If you have a column of "stuff" that contains numbers, text, blanks, errors, and other junk, then the array formula:

=AVERAGE(IF(ISNUMBER(A:A),A:A))

will average the numbers. Array formulas must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key.