Calculating Sumifs Across Multiple Tabs; allowing Tabs to change or be added/deleted

I am trying to calc employee hours in a summary tab across multiple "project" tabs. The criteria is to lookup the name across multiple tabs (30+), but allow the user to modify the number of tabs, add/delete, rename, etc. Also, the "project" tabs cannot require that the employee name is always in the same cell (but the range of cells is structured). I have used macro SUMIF3D2, it worked well. However, it appears that this macro is causing instability in the excel file, causing it to crash.

My question is whether a simplified equation exists that can do the same thing exists? I have used varying ideas with SUMPRODUCT, INDIRECT, DYNAMIC range, but each requires the number of tabs to remain static, i need this to be variable. For instance, the following: =SUMPRODUCT(SUMIF(INDIRECT("'"&PROJECTS2&"'!"&"B:B"),B5,INDIRECT("'"&PROJECTS2&"'!"&"H:H"))), works but only if the tab range "PROJECTS2" remains fixed. As new project tabs are added by user, I need the equation to automatically include the new tab, or allow to add/delete tabs. Only the Macro seems to accommodate all this criteria, but some users are experiencing volatility. Can you help?

Sorry, this is my first post and just getting the hang of it. Hope it goes well...

Attached is working file, the tab "util" is the relevant sheet for the question thanks

Many thanks. Is there a solution that does not require a VBA? An equation that will allow the sheets to vary?

I could still use some help on this problem. Let me start again. I'd like to simplify my approach without using a macro. In attached sheet "UTILIZ", in column B, I have employee names. In Col F, I want to return the total hours that employee worked in Jan for all the PROJECTS between tab 'START' to 'END", where employee name is in range of B30-B59, and hours are recorded in H30-H59 (for Jan). I'm trying to get an equation that will read each PROJECT tab between START:END, sum the hours for that particular employee, and return to UTIL Col F. I tried this approach:

=SUMPRODUCT(SUMIF(INDIRECT("'"&START&"'!"&":"&"'"&END&"'!"&"B:B"),B5,INDIRECT("'"&START&"'!"&":"&"'"&END&"'!"&"H:H")))

but it gives me a #NAME? return, which I assume is due to the fact excel is unable to understand START:END in the indirect approach.

I also grouped the current tab names under name range "PROJECTS2" and applied: =SUMPRODUCT(SUMIF(INDIRECT("'"&PROJECTS2&"'!"&"B:B"),B5,INDIRECT("'"&PROJECTS2&"'!"&"H:H")))

This works fine, except the TAB name range does not allow the user to add new PROJECT tabs in the future, the name range must remain static for this equation to work. The goal of this approach is to allow PROJECT tabs to be added without changing the UTILIZ tab equation.

Any additional help would be greatly appreciated. BTW I have Office 365, but I am not advanced enough to use Power Query.

M - Thanks very much for feedback. Firstly, a worker has same internal raw rates but different billing rates across projects (depends on client), billing and collection is done separately (AJERA), this is not intended as a collection/billing tool rather a mgt forecasting tool to estimate future profit on each project, which rolls up to divisional performance (in the future).

Background: The PROJECT sheets are no doubt over complicated. The goal of this workbook is to allow a div mgr (5 DIV USERS) to assess (estimate) the profitability of a division building the results of each project (causing the undesirable, multiple PROJECT tabs), therefore, the Project tabs have three sets of table to enable an individual project to be analyzed for the sole purpose of profitability, namely, schedule of revenue/invoicing (monthly), cost of labor (we are a labor only business), then the cost of subconsultant (outside labor costs). Granted, the subconsultant (outside) costs can certainly be moved out of these tabs, but I left them in to complete the net costs per project.

Since the sum of all PROJECT tabs allows the div mgr to evaluate DIV profit in the future (DIV SUMMARY - not shown), it is nice to keep each Project data together in a unified tab - it helps tracking accuracy over time. However, this is creating my problem, since I cannot estimate now the number of future projects (TABS) or names as they will be added in the future.

This is basically a forecasting mgt tool, we don't use it for actual billing purposes. But it has helped the mgr's get control on predicted schedule of revenue & costs at the project level, which is critical for us. The structure can be improved and streamlined, I am reviewing your suggestions, but the goal is the same.

So, this all works ok for us, but I wanted to add a feature that would capture the labor hours across all the projects - ie UTILIZ tab (and new projects (tabs) added in the future - which is my current problem in the indirect approach). However, at the moment, with 5 div mgrs each with their own workbook, I wanted to complete the operation of UTILIZ more efficiently. The macro (module1), as you can see, is doing the job...but at the cost of processing time.

Summary, i'd like to keep the basic structure the same for now and capture the total labor hours across projects (to check over/under utilization quickly) with a better approach. Perhaps there is not one. It appears that INDIRECT will not work with 3D arrays for a SUMIF application. Is there another workaround other than manhandling the data in each tab, then vlookup in the UTILIZ summary??

Please let me know if you have any suggestions. I'll look at rebuilding the entire structure later on. Best regards - C