Formula that adjusts to number of entries in column

Question

In short:
I have 3 columns. Column A is Sheet Name, Column B is Individual's Name, Column 3 is number of instances.

I want to count the number of instances the person's name from column B occurs in a dynamic list of worksheets in column A.

More details:
I have a workbook with many worksheets. Some of worksheets begin with the name "Test". I want to search each worksheet that begin with "Test" and find how many times a name (from column B) occurs.

I have two parts to this.

A macro lists all of the worksheets that begin with "Test" and puts them in column A.
A formula in column C goes into each sheet in column A and finds the number of instances the name in Column B occurs.

The issue is new tabs that begin with "Test" can be added.

How do I make the formula dynamic and search for all of column A?

Sub WorksheetLoop()
    Dim WS_Count As Integer
    Dim I As Integer
    Dim R As Integer
    ' Set WS_Count equal to the number of worksheets in the active
    ' workbook.
    WS_Count = ActiveWorkbook.Worksheets.Count
    R = 2
    ' Begin the loop.
    For I = 1 To WS_Count
        If (InStr(1, ThisWorkbook.Worksheets(I).Name, "Test")) > 0 Then
            ThisWorkbook.Worksheets("Master").Cells(R, 1) = ActiveWorkbook.Worksheets(I).Name
            R = R + 1
        End If
    Next I
End Sub

Formula:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$2:$A5&"'!A1:EE2000"),B2))

tigeravatar tigeravatar · Accepted Answer · 2018-07-24T17:23:52

Here's one way to do it:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$2:INDEX($A:$A,MAX(2,ROWS($A:$A)-COUNTBLANK($A:$A)))&"'!A1:EE2000"),B2))

Alternately, you could create a Dynamic Named Range to reference your sheet names in column A. In Excel, go to Formulas -> Name Manager -> New -> Set the name to listSheetNames and set the Refers To to be: =$A$2:INDEX($A:$A,MAX(2,ROWS($A:$A)-COUNTBLANK($A:$A)))

Now that you have a dynamic named range for your list of sheet names, you can instead have your formula look like this: =SUMPRODUCT(COUNTIF(INDIRECT("'"&listSheetNames&"'!A1:EE2000"),B2))

Formula that adjusts to number of entries in column

1 Answers