SUMIF functionality in a 3D reference

bmont

New Member
Joined
May 28, 2013
Messages
14
Office Version
  1. 2016
Platform
  1. MacOS
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):
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,666
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 28, 2013
Messages
14
Office Version
  1. 2016
Platform
  1. MacOS
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?

VBA Code:
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,666
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 28, 2013
Messages
14
Office Version
  1. 2016
Platform
  1. MacOS
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?

VBA Code:
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,666
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1Sheet name containsSheet
2Result
37Index
4Start sheetSheet12
5End sheetSheet33
6
7Range to SumA1
8
9Sheet listSheet12
10Sheet21
11Sheet33
12Sheet44
Sheet4
Cell Formulas
RangeFormula
A3A3=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:E5E4=SHEET(INDIRECT(D4&"!A1"))
E9:E12E9=IF(ISNUMBER(SEARCH($D$1,D9)),SHEET(INDIRECT(D9&"!A1")),"")
 
Last edited:
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,118
Messages
5,640,216
Members
417,131
Latest member
Seanr19871

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top