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

#### mistatasty

##### New Member
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??

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### mistatasty

##### New Member
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

##### MrExcel MVP, Moderator
It's all in the link I posted.

Replies
2
Views
180
Replies
9
Views
130
Replies
1
Views
123
Replies
4
Views
121
Replies
4
Views
301

1,147,571
Messages
5,741,887
Members
423,693
Latest member
Excelquestion35

### 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?

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