Calculate a Sum Across all sheets with multiple criteria

[dfransen1]

Hello,

I have not found a solution yet that works.
I have a workbook where I am making a master sheet that calculates hours worked. On the master sheet there is a list of companies found in D2:D44 and an associate name in F1 , G1 H1 and I4. In cells F2:F44 (mirrored in G-I), I would like a formula that will search through all of the sheets where the company and associate are both found and return a total of hours worked.
On the sheets being searched through, the company name is in cell B4, the associate's name is in range A30:A34 and the hours worked are in range G30:G34.
All of the sheet names vary but I have entered in a "Start" and "End" sheet to help group all the sheets together.
I appreciate any and all help

 

[Eric W]

Getting totals from multiple sheets is tricky. There are ways, but they are complicated, and usually require having a list of the sheets in question somewhere on your master sheet. I'd recommend a UDF (User Defined Function) to get your totals. Assuming your master sheet looks like this:

Book1
	D	E	F	G	H	I
1	Company		Alexa	Siri	Cortana	Jeeves
2	a		2	1	0	0
3	b		0	0	0	0
4	c		11	8	6	0
5	d		0	0	0	0
Sheet1

Cell Formulas
Range	Formula
F2:I5	F2	=compemphrs($D2,F$1)

We can build a UDF as follows: Open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Paste the following code in the window that opens:

Public Function CompEmpHrs(CoName As String, EmpName As String) As Double
Dim s As Variant, CheckIt As Boolean

    Application.Volatile
    CompEmpHrs = 0
    CheckIt = False
    For Each s In Worksheets
        Select Case s.Name
            Case "Start"
                CheckIt = True
            Case "End"
                Exit Function
            Case Else
                If CheckIt Then
                    If s.Range("B4") = CoName Then
                        CompEmpHrs = CompEmpHrs + WorksheetFunction.SumIf(s.Range("A30:A34"), EmpName, s.Range("G30:G34"))
                    End If
                End If
        End Select
    Next s

End Function

Go back to your master sheet and enter the formula as shown.

Let us know how this works.

 

[dfransen1]

Good Morning,
Thank you for the coding. When I put all of it in my results are #NAME?
I did create a list of all sheet names in column K on the master sheet. The sheet names will always be random and never set. See example below.

MASTER
9601​
9601 (2)
9601 (3)
9601 (4)
9601 (5)
9601 (6)
9601 (7)
9601 (8)
60847-947T20 (2)
60833TVA (2)
60822TLA
60829-929TLA (8)

 

[dfransen1]

this might be a better example

 

[Eric W]

If you're good with a UDF, let's try to get that to work. Even with a list of the sheets, the UDF will be easier than formulas. The #NAME error means that Excel can't find the function. First thing to check is that the formula on the sheet has the same name as the UDF: CompEmpHrs. A 1-letter typo will cause the error. If the name is the same, we need to check if the UDF is on the right code sheet. If you followed the instructions above, it should be on a code sheet named Module1:

 

[dfransen1]

Thank you Eric!
Works like a charm, I really appreciate your assistance!