Average over more sheets with criteria

Please post your sample.

Conditional Calculations in 3D are, generally speaking, both complex and inefficient.

Based purely on your sample file:

Please Login or Register  to view this content.

The inline array can be changed to a range containing the sheet names you wish to sum... note though the range used must always contain valid sheet names else you will get a #REF! error.

The above is a Volatile SUMPRODUCT and as such should

a) be used with "lean" ranges (ie avoid referencing unneeded rows)

b) be used in moderation

I found the following:

Please Login or Register  to view this content.

Where Z1:Z5 have the sheet names.

edit: DonkeyOte gave the exact same solution. Picked it up from: http://www.mcgimpsey.com/excel/threedsumif.html

so I turned of automatic update and let users update at the end (by saving or pressing F9).

this is an option if you control the sheet yourself. When controlled by others this will lead to errors.

In your case you require these kind of VERY DIFFICULT and nested formula's as your data is scattered. Maybe go back to the drawing table and find a way to structure the data. Then these kind of formula's are no longer required.

See the signature of DonkeyOte and look at the Volatile link. This might shine some light on 'why is my WB so slow'

Slow files are invariably caused by over use of Arrays (incl. Sumproduct) and Volatile functions ... worse still combinations of the two (like the above)... a file with lots of Volatiles that relies heavily on Volatile action functionality like Filtering etc will grind things to a halt quite nicely...
(you can chuck conditional formatting in the mix for good measure)

Please try to keep threads to one question where at all possible.

If you want to conduct conditional MIN/MAX in 3D then realistically you're looking at

a) UDF (VBA)

b) installing Laurent Longre's morefunc.xll and utilising the THREED function

General rule of thumb in these scenarios is to calculate each sheet individually in a table of sorts and then work from the resulting table to calculate the "3D-result"

If you've listed your sheets for use in the 3D SUMIF then you already have that list... the rest should be pretty trivial
ie use standard MIN/MAX/AVG arrays in conjunction with INDIRECT using the sheet name as differentiating item -- given you say performance of your file is already horrendous the above will make little difference.