SUMPRODUCT formula with changing number of worksheets

Question

I have an Excel workbook with multiple worksheets and I would like to use SUMPRODUCT formula to sum values from all worksheets with name Page 1, Page 1(2), Page 1(3), Page 1(4) etc.:

=SUMPRODUCT(SUMIF(INDIRECT("'"&D2:D4&"'!B11:B100"),$B3,INDIRECT("'"&D2:D4&"'!E11:E100")))

The problem is that the number of Page 1 worksheets is different every time and I need to update &D2:D4& every time manually. Is there any way I could automate it so I don't need to change the range manually?

Variatus Variatus · Accepted Answer · 2017-09-11T09:24:57

Please paste the code below in the ThisWorkbook code sheet of the workbook in which you have your SUMPRODUCT formula. Then adjust the code to comply with the comments I added into it (especially with regard to the name of the worksheet on which you have the range D2:D4.

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    SetPageRange
End Sub

Private Sub SetPageRange()

    ' this procedure creates a named range "Pages" which you can
    ' use in your formula instead of "D2:D4"
    ' =SUMPRODUCT(SUMIF(INDIRECT("'"& Pages &"'!B11:B100"),$B3,INDIRECT("'"& Pages &"'!E11:E100")))


    Const WsName As String = "Page"             'Page, Page (1), Page(x)
                                                ' change to "Page 1" if that is what you need

    Dim Wb As Workbook
    Dim MasterWs As Worksheet
    Dim Arr() As String
    Dim i As Integer
    Dim Ws As Worksheet

    ' the Active workbook is not necessarily the workbook containing this code !!
    Set Wb = ActiveWorkbook
    ReDim Arr(1 To 20)                          ' maximum number of sheets you expect
                                                ' this number of rows must be available
                                                ' below D2 in the sheet where your
                                                ' formula resides

    For Each Ws In Wb.Worksheets
        ' this collects all sheets whose name starts with "Page"
        If InStr(1, Ws.Name, WsName, vbTextCompare) = 1 Then
            i = i + 1
            Arr(i) = Ws.Name
        End If
    Next Ws

    ' Change the name "Sheet5" to the name of the sheet where your
    ' SUMPRODUCT formula resides and where you currently have D2:D4
    ' if it isn't in the ActiveWorkbook, then where is it?
    Set MasterWs = Wb.Worksheets("Sheet5")
    With MasterWs.Cells(2, "D")             ' this is where the names will be written
                                            ' from D2 down (20 rows, as set above)
        .Resize(UBound(Arr)).Value = Application.Transpose(Arr)
        Wb.Names.Add Name:="Pages", RefersTo:="=" & .Resize(i).Address(True, True, xlA1, True)
    End With
End Sub

Save the workbook as macro-enabled (xlsm format) Now, this code will run automatically when you save the workbook. I imagined that you would open it, import a number of "Page 1" sheets and then want to make your totals. So, now you will have to save it first. The range D2:D4 (D2:D20 in my code) will be adjusted automatically.

If you don't like the automation, delete the Workbook_BeforeSave procedure entirely (or place an apostrophe at the beginning of each of its lines). You can run the SetPageRange procedure manually by placing the cursor anywhere in it and pressing F5. You can get it to be available for being run from Excel's worksheet interface by changing the attribute Private to Public.

The code will create a named range named "Pages" and you will need to change your formula to point to the named range (the size and content of which the code manipulates) instead of the present "D2:D4". The revised formula is included in the code's comments above.

SUMPRODUCT formula with changing number of worksheets

1 Answers