# 2D array across sheets and columns

#### jlonn

##### New Member
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>
Code:
``=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.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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?

Replies
1
Views
336
Replies
4
Views
168
Replies
11
Views
384
Replies
0
Views
51
Replies
4
Views
137

1,203,076
Messages
6,053,395
Members
444,661
Latest member
liamoohay

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back