Summing data across multiple monthly columns and sheets where criteria is met.

Anita C.

I am trying to get a total for each location across multiple sheets. Each sheet has a location column and Aug-24 to Dec-26 (one column per month). So, location of employees with their work hours in rows in the monthly columns. I'm trying to get the total hours per location on a separate metric sheet.

So start with:

Location Employee Name, Aug-24, Sep-24, …. Dec-26.

Now need the total hours per location (not monthly but a grand total). I do also have 5 sheets with data to reference).

I can pull in data for one month per location but cannot seem to pull in all and sum it up. Is there anyway to do this without having to add each month separately in the formula?

tried the following with sum and collect (but only lets me reference one column in the FTE range — won't add if multiple columns).

=SUM(COLLECT({Canada East FTE Summary by Discipline FTE}, {Canada East FTE Summary by Discipline Part Range 9}, Location@row))

and tried Sumifs formula as well.. but won't work unless I reference each month column separately.. i.e. adding more sumifs formulas.

=SUMIFS({Canada East FTE Summary by Discipline Part FTE}, {Canada East FTE Summary by Discipline dec locat}, location@row)

Is there anything that allows me to reference all monthly column rows and add them up for each location?

Locations are HO, SR, QP etc. So I need a total FTE for each.