If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
sum a range based on criteria in rowdata and column name
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? |
#2
|
|||
|
|||
sum a range based on criteria in rowdata and column name
=SUMPRODUCT((A2:A12="Meeting")*(MONTH(C1:G1=10)*(C 2:G12)))
-- __________________________________ HTH Bob "L Davis" wrote in message ... 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? |
#3
|
|||
|
|||
sum a range based on criteria in rowdata and column name
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" wrote: =SUMPRODUCT((A2:A12="Meeting")*(MONTH(C1:G1=10)*(C 2:G12))) -- __________________________________ HTH Bob "L Davis" wrote in message ... Suppose I have some data that looks something like: A B C D E F G 1 Category Site Sep1 Sep2 Oct1 Oct2 Oct3 2 Train GB 10 5 4 12 3 Meeting STL 10 10 4 Train STL 5 5 5 5 Meeting GB 14 4 10 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? |
#4
|
|||
|
|||
sum a range based on criteria in rowdata and column name
You just extend the columns to all possible. Do you have real dates in row
1? -- __________________________________ HTH Bob "L Davis" wrote in message ... 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" wrote: =SUMPRODUCT((A2:A12="Meeting")*(MONTH(C1:G1=10)*(C 2:G12))) -- __________________________________ HTH Bob "L Davis" wrote in message ... Suppose I have some data that looks something like: A B C D E F G 1 Category Site Sep1 Sep2 Oct1 Oct2 Oct3 2 Train GB 10 5 4 12 3 Meeting STL 10 10 4 Train STL 5 5 5 5 Meeting GB 14 4 10 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? |
#5
|
|||
|
|||
sum a range based on criteria in rowdata and column name
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? "Bob Phillips" wrote: You just extend the columns to all possible. Do you have real dates in row 1? -- __________________________________ HTH Bob "L Davis" wrote in message ... 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" wrote: =SUMPRODUCT((A2:A12="Meeting")*(MONTH(C1:G1=10)*(C 2:G12))) -- __________________________________ HTH Bob "L Davis" wrote in message ... Suppose I have some data that looks something like: A B C D E F G 1 Category Site Sep1 Sep2 Oct1 Oct2 Oct3 2 Train GB 10 5 4 12 3 Meeting STL 10 10 4 Train STL 5 5 5 5 Meeting GB 14 4 10 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? |
#6
|
|||
|
|||
sum a range based on criteria in rowdata and column name
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! "L Davis" wrote: 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? "Bob Phillips" wrote: You just extend the columns to all possible. Do you have real dates in row 1? -- __________________________________ HTH Bob "L Davis" wrote in message ... 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" wrote: =SUMPRODUCT((A2:A12="Meeting")*(MONTH(C1:G1=10)*(C 2:G12))) -- __________________________________ HTH Bob "L Davis" wrote in message ... Suppose I have some data that looks something like: A B C D E F G 1 Category Site Sep1 Sep2 Oct1 Oct2 Oct3 2 Train GB 10 5 4 12 3 Meeting STL 10 10 4 Train STL 5 5 5 5 Meeting GB 14 4 10 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? |
Thread Tools | |
Display Modes | |
|
|