Sumifs Column Selection based on Aother Cell's Value

[stew1988]

This is my first post and I’m aware it’s a bit of a long one. Sorry!

I'm looking to use a sumifs formula (which I have working on a single column) over a range. I've searched extensively online, but can't seem to find a way to do what I want to do - maybe a search terms thing?!?!?

I have an output from a planning system that gives me forecast manhours for multiple projects and departments. Column ‘A’ holds the project code, column ‘B’ holds the department code, row 1 holds the week ending date and the data in the table holds the hours that are forecast to be spent that week against a given project within each department.

I then have a table that I need to populate to allow me to produce a couple of charts for a weekly management meeting. Under each week, there’s a forecast and an actual value. The actual hours are provided from a 3^rd party, so I’m not concerned with populating this at the moment.

I have a sumifs formula set up at the moment that does work, but as the data period moves on it needs to be manually updated, because it references a particular column. Current formula for cell C3 and E3 are as follows:
=SUMIFS('Data'!C:C,'Data'!$A:$A,$B3,'Data'!B:B,$B$1)
=SUMIFS('Data'!D:D,'Data'!$A:$A,$B3,'Data'!B:B,$B$1)
So basically, sum the manhours for weekending 26-Oct-14 / 02-Oct-14 if project = 1 and department = 110.

I’d like to automate the process of determining which column to apply the sumifs formula to so that when week 1 of the table becomes 02-Nov-14 I don’t have to update all of the formulas manually. In other words, I would like the formula to look at cell C1, then match that date to row 1 of the Data tab and find which column to sum.

Thanks!!

 

[ChrisM]

There's a couple ways to do that, here's one:

=SUMIFS(OFFSET($J$4:$J$9,,MATCH(K$16,$J$3:$N$3,0)-1),$I$4:$I$9,$J17)

Where:
J4:J9 is your first column of weekly raw data numbers

K16 is the reporting week you are interested in

J3:N3 is your row of date headers in your raw data

Then I4:I9 and J17 are just my simulated criteria for the rest of the sumif, replace with yours

 

[Fowmy]

Hi,

This is another way:

=SUMPRODUCT(--(Data!$A$2:$A$17=$B3)*--(Data!$B$2:$B$17=$B$1)*INDEX(Data!$C$2:$H$17,,MATCH(C$1,Data!$C$1:$H$1,0)))

 

[stew1988]

Thanks to you both. Really appreciate it. I’ve gone with:

=SUMPRODUCT(--(Data!$A$2:$A$17=$B3)*--(Data!$B$2:$B$17=$B$1)*INDEX(Data!$C$2:$H$17,,MATCH(C$1,Data!$C$1:$H$1,0)))

Works really well and saved me a lot of grief!