sum a range based on criteria in rowdata and column name

[L Davis]

Suppose I have some data that looks something like:
A B C D E F G
H
1 Category Site Sep1 Sep2 Sep3 Oct1 Oct2 Oct3
2 Train GB 10 5 4 12
14
3 Meeting STL 10 10
10
4 Train STL 5 5 5
5 Meeting GB 14 4 10
15

I need a formula that can ask a question like: How many hours of meeting
activity are happening in GB during the month of October?

Assuming the real data includes a column for every weekday over a span of a
year or more, and needs to be summed by both week and year, or any date
range. The variables may be stored in a cell on another worksheet such as
which Site and which activity and which month the answer is needed for. I've
tried playing around with sumif, sumproduct, and a few other things - the
problem I keep running up against is the fact that the range I need to sum is
more than one column, I won't necessarily know how many columns I need to sum
(for month anyways, week is always 5,) etc.

Anyone have any ideas?

 

[Bob Phillips]

=SUMPRODUCT((A2:A12="Meeting")*(MONTH(C1:G1=10)*(C2:G12)))

 

[L Davis]

I have tried this formula, and I receive an answer of 0. Also, the dates in
row one continue on indefinitely (I must account for every day through the
end of next year.) and the number of columns in the range I want to sum may
change.

 

[Bob Phillips]

You just extend the columns to all possible. Do you have real dates in row
1?

 

[L Davis]

So I created the simplified version of my problem to try the formula listed
below exactly. The dates in row one are real dates.

This formula solves one of the two problems I was having - it is able to
find a sum of a range that extends past just one column based on more than
one criteria in each row. If I do the following in a worksheet: (Assuming
range A1:F6)

Type Site Sep 1 Sep 2 Oct 1 Oct 2
Train GB 10 10 10 10
Train STL 20 20 20 20
Train GB 10 10 10 10
Meet GB 20 20 20 20
Meet STL 20 20 20 20


And then I use the formula:
SUMPRODUCT((a2:a6="Train")*(b2:b6="GB")*(month(c1:f1=10))*(c2:f6)) I get the
result of 80, when the result I'm looking for is 40.

So...this formula solves the problem of needing to sum a range that spans
over multiple columns, and would give me the answer I'm looking for...IF I
can tell it the correct range to look in.

Which brings me to problem two: identifying a dynamic range. If I can
somehow combine this formula with another one that identifies which range
contains the column headers for the month(or week or day, which will always
be in order) it will do exactly what I am looking to do... So I know I will
need to somehow use INDEX or MATCH or HLOOKUP to identify what the range
should be, and then use that range for last portion of the SUMPRODUCT
formula.

Can anyone help with that?

 

[L Davis]

Apparently, the only part of this formula that really didn't work was
identifying the month. Once I insert a row above the column headers, and use
a simple =MONTH(c1) for each column, and then used the exact same formula
without the Month(xx)=10, I simply named the range=10, then the formula
worked exactly the way I needed it to!

Thanks a bunch for the help, Bob!