AverageIFS (or even SUMIFS/COUNTIFS) on large data set

In your formula some of the references are single column and some of them are multi column refer the classification for details…

Average Range = Sheet2'!$D$3:$J$1973 = Multi Column
Condition Applied to = 'Sheet2'!$D$3:$J$1973,">0" = Multi Column
Condition Applied to = 'Sheet1'!D$2:J$2,'Shee1'!D$2 = Multi Column
Condition Applied to = 'Sheet1'!$B$3:$B$505,'Sheet1'!$B3 = Single Column
Condition Applied to ='2013'!$C$3:$C$505,'Sheet1'!$C3 = Single column

For your info Averageif won’t work with Closed Workbooks.

What is a closed workbook? How is that different than any other workbook?

The Sumif(s),Countif(s),Averageif(s) will only work with the external references when the file is kept open. When the file is not in open then the above formula’s won’t work and you need to keep the source workbooks open to get the result.

Is it inappropriate in the syntax of the formula to mix multi-column and single column?

Yes Sumif(s) and Averageif(s) will match the conditions on single column alone and the resulting column should also single.

I have no idea what you mean by refer to the classification.

I just differentiated your references in detail with Columns Count and I called that detail as classification.

I think I found a solution using a pivot table, but it doesn't ignore zeros. Better than nothing, I guess.

We can achieve it with formula itself but we need clear picture.

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.