2D array across sheets and columns


New Member
Mar 26, 2013
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>
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.


Board Regular
May 1, 2011
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?

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...