Dynamic SUMIF formula help please

Hello All

New member here.

I've been struggling trying to get a formula to work for me so I thought I would turn to you pros for some help. My novice/Intermediate skills seem to be tapped out at this point.

In the attached file you will see that I am trying to get a SUMIF formula to change sum ranges based on a column header ie. =SUMIF(Sheet2!$A$2:$A$15,Sheet1!$A2,Change based on Sheet1 column header). Ideally I would be able to copy the formulas in the week end date columns to each other without having to manually change the sumif range column each time I get to a new fiscal month/quarter/year.

The example I have attached is rough but is a snipit of the big picture. Sheet1 is a summary sheet that shows hours by resource code by week end date, fiscal month, fiscal quarter, and fiscal year. With Sheet2 being the raw data table. As you can see Sheet2 has multiple repeated resource codes thus the summary of each resource code on Sheet1.

I apologize if my above question is not clear so please let me know if more detail is needed for you to help.

Thank you very much in advance.Sumif Example.xlsx

Do you mean?

=SUMIF(Sheet2!$A$2:$A$15,Sheet1!$A2,INDEX(Sheet2!$B$2:$N$15,0,MATCH(B$1,Sheet2!$B$1:$N$1,0)))

Are you looking to get the sum for EX1 for 9/24/2013 in sheet1 from the 9/24/2013 column in sheet 2?
if so this will work.
=SUM(SUMIF(Sheet2!$A:$A,Sheet1!$A$2:$A$11,Sheet2!B:B))
then drag down and across.

Assuming that all the date columns match:

Cell B2:

Formula:

Please Login or Register  to view this content.

Copy across and down.

I used SUMIFS because I thought I might do something more complex, and didn't. so you could just use SUMIF if you want.

Formula:

Please Login or Register  to view this content.

You might do yourself a favour and move all the calculated quarterly columns to the end (right hand side). Then you can just drag the formulae across.

I don't think having them in the middle helps the reader particularly, unless you plan to group them ... but it'd still be easier to view the quarterly summary if the columns are together.


Regards, TMS