make a sumifs formula more dynamic? maybe using index match somehow?

[mistatasty]

Hi,

I have a sumifs formula to sum number of hours according to project name, month, and year.
current formula is: =SUMIFS('Labor Hours Report'!$AB:$AB,'Labor Hours Report'!$T:$T,Hours!D$227,'Labor Hours Report'!$U:$U,Hours!D$228,'Labor Hours Report'!$H:$H,Hours!$A230)

Labor Hours Report column AB is where the hour numbers are, Labor Hours Report column T is where the years are listed, Hours row 227 is the year it needs to match, Labor Hours report column U is the month, Hours row 228 is the month it needs to match, and Labor Hours report column H is the project name, and Hours row 230 is the name it needs to match.

I would like to make this formula more dynamic, and not summing based on the specific columns and rows matching, but rather lookup and sum the values based on the actual names, year, and month (like how a lookup or index match function might work). The reason for this is that the column headers on the labor Hours report may change and thus mess up my sumifs formula.

If anyone knows how to accomplish this??

 

[Fluff]

If you turn the data into a Table then you can use structured references. Structured references in Excel tables

 

[mistatasty]

If you turn the data into a Table then you can use structured references. Structured references in Excel tables

I think this could work. The data is already in table form with headers denoting the "Year", "Total Hours", "Month".

 

[mistatasty]

I think this could work. The data is already in table form with headers denoting the "Year", "Total Hours", "Month".

If you turn the data into a Table then you can use structured references. Structured references in Excel tables

do you know how i could implement that into a sumifs formula on a different worksheet than the data table?

 

[Fluff]

It's all in the link I posted.