2D array across sheets and columns

[jlonn]

I have an Excel Workbook with 50 Sheets, all containing a table with the same format. I am trying to sum certain quantities across a certain range of cells across all sheets </SPAN>(provided certain criteria are met)</SPAN>. So you could say that I am trying to sum the elements in a 2D array, where one dimension lies inside the sheets and the other dimension stretches across the sheets.</SPAN></SPAN>

If I did this for one sheet only, the conditional sum I wanted to compute would look like this:</SPAN></SPAN>

=SUMIFS(D22:D30,B22:B30,"=FZJ",C22:C30,"=2012")</SPAN></SPAN>

The question is: how do I extend this to sum over all 50 sheets? The ranges D22:D30, B22:B30 and C22:C30 need to be extended by something (containing also the range Sheet1:Sheet50), but what is the syntax for this? </SPAN></SPAN>

I am using Excel 2010 where SUMIFS is available, but the principle of the question would apply to any function regardless of the version.

 

[GDH]

I think there might be a 3D SUMIF function that can be used if you download MOREFUNC. I know there is a COUNTIF.3D so I hope there is a similar option for SUMIF?