Average same cells on different sheets ignoring any containing text

Should be simple but can't find the answer.

"Summary" ws cell J6 has to show the average value of cells J6 in five other sheets, ignoring any that are not numbers, as users may leave them blank or type "N/A" or something else.

if the five cells were in the same sheet (e.g. J6:J10, with J8 being "N/A" text, and the four others totalling 10), the formula "=Average(J6:J10)" ignores the "non number" in J8 and gives the result as 2.5

Tried various approaches, but can't see the syntax when the cells to add are on different sheets?

All answers appreciated gratefully as ever

Ochimus

Good evening TMS,

Appreciate the prompt response and suggestion.

I put "1%" into Sheet 2, and 4% into Sheet 5 and left the other three blank.

Your formula in the Summary sheet generated a "#Name" error.

Changed sheet 6 to "N/A" and got same error message

Ochimus

Actual workbook Sum Cells.xlsx attached, with all extraneous cols and rows redacted.

If it works in J6 it will work in every other column and range I need.

Ochimus

TMS,

Your approach using the sheet names rather than the sheet numbers did the trick,

I hadn't noticed originally that the "week1" sheet had no space when I received the file, but as my original attempts used the sheet numbers it would have been academic anyway.

Slightly intrigued as to why that approach didn't work, but life is too short. . .

Ochimus