SUMIF functionality in a 3D reference

[bmont]

What I think I know about 3D references is that Excel looks at the 3D referenced sheets within a sequence of sheet index numbers, low to high, irrespective of tab name. Move a sheet out of that sequence and it's removed from the 3D reference; add a sheet within that sequence and it gets added to the reference. Similar behavior to adding or removing a row in a summed range in a plain-vanilla 2D reference.

I need to sum a bunch of tabs and would like it to run like a SUMIF function that's filtering for a string in the tab names, a la:
SUM A1 across [tabStart to tabEnd] IF tab name includes string "foo"

Sadly, SUMIF isn't available for 3D references, per the makers of Excel (list of 3D-eligible functions about halfway down the page):

Create a 3-D reference to the same cell range on multiple worksheets - Microsoft Support

Use a 3-D reference in Excel to reference several worksheets, such as when you consolidate budget data from different departments in your organization.

support.microsoft.com

 

[jasonb75]

Another caveat is that such functions only work in simple format, as soon as you start adding arrays / criteria they will fail as well.

To use criteria of any kind, you will need to list all of the individual sheet names in a range of cells.

 

[bmont]

Another caveat is that such functions only work in simple format, as soon as you start adding arrays / criteria they will fail as well.

To use criteria of any kind, you will need to list all of the individual sheet names in a range of cells.

Thanks for your response @jasonb75. Per your suggestioin, I've set up a procedure to list out sheet names, code below. Is this the sort of thing you had in mind?

How do you envision using the list of tab for criteria in 3D references?

Option Explicit

Sub RefreshTabIndex()

    Dim ws As Worksheet
    Dim rgIndex As Range
    Dim c As Range
    
    Set rgIndex = Range("TabIndex")     ' https://www.thespreadsheetguru.com/blog/the-vba-guide-to-named-ranges

    
    ' This sub will be triggered by user or VBA action that creates or reposition a tab
    ' Example: https://www.extendoffice.com/documents/excel/2695-excel-lock-freeze-worksheet-tab.html
                    
                    Dim i As Integer
                    i = 0
                    
                    For Each c In rgIndex
                        For Each ws In Worksheets
                            If ws.Name = c.Value Then
                                ' Do nothing
                            Else
                                i = 1
                                Exit For
                            End If
                        Next ws
                    Next c
                    If i = 0 Then Exit Sub
            
    ' erase current list plus a wide margin.
    Dim rgAnchor As Range
    Set rgAnchor = Range("TabIndexAnchor")
    Range(rgAnchor, rgAnchor.Offset(200)).Clear
    
    ' Make an index of tabs
    Dim x As Integer
    Dim rgIndexAnchor As Range
    Dim DiscloseHidden As String
    
    x = 0
    
    Set rgIndexAnchor = Range("TabIndexAnchor")
    
            For Each ws In Worksheets
                    If ws.Name = "Index" Then
                            GoTo NextIteration      ' bc first tab is named "Index"
                    End If
                    
                    If ws.Visible = xlSheetHidden Then
                            DiscloseHidden = "(Hidden)"
                    Else
                            DiscloseHidden = ""
                    End If
                    
                    Range("TabIndexAnchor").Offset(x, 0) = ws.Name & " " & DiscloseHidden
                    x = x + 1
                     
NextIteration:
            Next ws
    
    ' Detect the list's range
    Set rgIndex = Sheets("Index").Range(rgIndexAnchor, rgIndexAnchor.Offset(x - 1, 0))
    
    ' This appears to overwrite the existing named reference
    ThisWorkbook.Names.Add Name:="TabIndex", RefersTo:=rgIndex
 
End Sub

 

[jasonb75]

within a sequence of sheet index numbers, low to high, irrespective of tab name.

Do you need to take this part into consideration, or did you only use it as a reference to your understanding of the process?

If sheet position is relevant, could you confirm if you have the =SHEET([value]) worksheet function in your version of excel (and if you are still using 2016?)

 

[bmont]

Thanks for your response @jasonb75. Per your suggestioin, I've set up a procedure to list out sheet names, code below. Is this the sort of thing you had in mind?

How do you envision using the list of tab for criteria in 3D references?

Option Explicit

Sub RefreshTabIndex()

    Dim ws As Worksheet
    Dim rgIndex As Range
    Dim c As Range

    Set rgIndex = Range("TabIndex")     ' https://www.thespreadsheetguru.com/blog/the-vba-guide-to-named-ranges


    ' This sub will be triggered by user or VBA action that creates or reposition a tab
    ' Example: https://www.extendoffice.com/documents/excel/2695-excel-lock-freeze-worksheet-tab.html
                
                    Dim i As Integer
                    i = 0
                
                    For Each c In rgIndex
                        For Each ws In Worksheets
                            If ws.Name = c.Value Then
                                ' Do nothing
                            Else
                                i = 1
                                Exit For
                            End If
                        Next ws
                    Next c
                    If i = 0 Then Exit Sub
        
    ' erase current list plus a wide margin.
    Dim rgAnchor As Range
    Set rgAnchor = Range("TabIndexAnchor")
    Range(rgAnchor, rgAnchor.Offset(200)).Clear

    ' Make an index of tabs
    Dim x As Integer
    Dim rgIndexAnchor As Range
    Dim DiscloseHidden As String

    x = 0

    Set rgIndexAnchor = Range("TabIndexAnchor")

            For Each ws In Worksheets
                    If ws.Name = "Index" Then
                            GoTo NextIteration      ' bc first tab is named "Index"
                    End If
                
                    If ws.Visible = xlSheetHidden Then
                            DiscloseHidden = "(Hidden)"
                    Else
                            DiscloseHidden = ""
                    End If
                
                    Range("TabIndexAnchor").Offset(x, 0) = ws.Name & " " & DiscloseHidden
                    x = x + 1
                 
NextIteration:
            Next ws

    ' Detect the list's range
    Set rgIndex = Sheets("Index").Range(rgIndexAnchor, rgIndexAnchor.Offset(x - 1, 0))

    ' This appears to overwrite the existing named reference
    ThisWorkbook.Names.Add Name:="TabIndex", RefersTo:=rgIndex

End Sub

Do you need to take this part into consideration, or did you only use it as a reference to your understanding of the process?

Interesting question. When I wrote this, it was just to reflect my understanding.

Now that you ask, however, seems like it make sense to pursue tab re-ordering as the solution path. I think this is what you're thinking: group the tabs and then 3D reference them, i.e., VBA to re-order tabs into groups based on a string in the tab name, then detect the min and max sheet index numbers of tabs per group, and then employ a 3D reference that cites those min and max values as the bookends of the 3D reference. My naming of relevant tabs goes like: TabType_GroupID_MemberID.

If sheet position is relevant, could you confirm if you have the =SHEET([value]) worksheet function in your version of excel (and if you are still using 2016?)

Indeed, I have =SHEET([value]) and still using 2016.

 

[jasonb75]

Took a while to figure this one out, hopefully the way that the sheet is laid out will make sense. I'm thinking that a UDF might be a more desirable option, but I'm not sure if they can be coded with a 3D reference. In theory it should be possible, but so far, reality is having other ideas.

On my test sheet, sheets 1 to 3 contained the values of 2, 1 and 5 respectively in A1. The index numbers next to the sheet list show the order of the sheets has been changed to Sheet2, Sheet1, Sheet3, Sheet4. Only the values of 2 (from sheet1) and 5 (from sheet3) have been summed, the 1 from sheet 2 has been ignored. In the original sheet order it was included.

As you are using excel 2016, I believe that you will need to array confirm the formula in A3 with Ctrl Shift Enter in order for it to work correctly.

sum sheets.xlsx
	A	B	C	D	E
1			Sheet name contains	Sheet
2	Result
3	7				Index
4			Start sheet	Sheet1	2
5			End sheet	Sheet3	3
6
7			Range to Sum	A1
8
9			Sheet list	Sheet1	2
10				Sheet2	1
11				Sheet3	3
12				Sheet4	4
Sheet4

Cell Formulas
Range	Formula
A3	A3	=SUMPRODUCT(IFERROR(SUMIF(INDIRECT("'"&IF($E$9:$E$12>=$E$4,IF($E$9:$E$12<=$E$5,$D$9:$D$12))&"'!"&$D$7),"<>0"),0))
E4:E5	E4	=SHEET(INDIRECT(D4&"!A1"))
E9:E12	E9	=IF(ISNUMBER(SEARCH($D$1,D9)),SHEET(INDIRECT(D9&"!A1")),"")