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

mistatasty

New Member
Joined
Jul 21, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 21, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,618
Office Version
  1. 365
Platform
  1. Windows
It's all in the link I posted.
 

Forum statistics

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

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