Calculate a Sum Across all sheets with multiple criteria

dfransen1

New Member
Joined
May 5, 2017
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,496
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
DEFGHI
1CompanyAlexaSiriCortanaJeeves
2a2100
3b0000
4c11860
5d0000
Sheet1
Cell Formulas
RangeFormula
F2:I5F2=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:

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

New Member
Joined
May 5, 2017
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 5, 2017
Messages
10
Office Version
  1. 365
Platform
  1. Windows
1652709281584.png


this might be a better example
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,496
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:

1652722501457.png
 
Solution

dfransen1

New Member
Joined
May 5, 2017
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Thank you Eric!
Works like a charm, I really appreciate your assistance!
 

Forum statistics

Threads
1,176,138
Messages
5,901,574
Members
434,906
Latest member
Prabhu_Churi

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