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
|
|||
|
|||
locate a column, sum specific columns to the right of that column
Tricky one here (I think):
I have a drop down list of Labor Periods on a cover sheet. On a separate data sheet I have: Period 1 Period 2 ... Units Cost Hours Units Costs Hours The oldest Labor Period is the farthest to the right. I need a formula that will find the Labor Period selected from a drop down (DV) and sum the values to the right of that Labor Period. So if Labor Period 5 is selected, I want to add up the hours for Labor Periods 5,4,3,2,1. Any help would be lovely. Beric |
#2
|
|||
|
|||
locate a column, sum specific columns to the right of that column
I think we need to be told about how you data is set out. Remember, we
cannot see you data and we have no real idea of what the project is. Please give us more details so we can help best wishes -- Bernard Liengme http://people.stfx.ca/bliengme Microsoft Excel MVP "Beric Dondarrion" wrote in message ... Tricky one here (I think): I have a drop down list of Labor Periods on a cover sheet. On a separate data sheet I have: Period 1 Period 2 ... Units Cost Hours Units Costs Hours The oldest Labor Period is the farthest to the right. I need a formula that will find the Labor Period selected from a drop down (DV) and sum the values to the right of that Labor Period. So if Labor Period 5 is selected, I want to add up the hours for Labor Periods 5,4,3,2,1. Any help would be lovely. Beric |
#3
|
|||
|
|||
locate a column, sum specific columns to the right of that col
Alright.
I have 2 sheets. 1 is a cover sheet, 1 is a data entry sheet. On the cover sheet, I have a (Data validation) drop down menu that references a column (E) that contains Time Periods for labor, i.e. "2/14/09 to 2/28/09". On that same sheet, starting in column F, I have 3 columns: Units, Cost, Hours. These 3 columns are repeated about 50 times. Listed above each set of 3 columns is a labor period. So, I want a cell on the cover sheet to be able to sum all the past data up to the date of the selected labor period. Assume the project began on 1/1/09, and "2/14/09 to 2/28/09" is selected, I'd want a cell to sum the amount of hours for 3 labor periods (each LP is 2 weeks long). Please let me know if you need further info. I'm trying to use SUMIF with a MATCH function nested within an INDIRECT function. I'm playing with R1C1 to try and get the match value to correspond to a column for the range in the formula. "Bernard Liengme" wrote: I think we need to be told about how you data is set out. Remember, we cannot see you data and we have no real idea of what the project is. Please give us more details so we can help best wishes -- Bernard Liengme http://people.stfx.ca/bliengme Microsoft Excel MVP "Beric Dondarrion" wrote in message ... Tricky one here (I think): I have a drop down list of Labor Periods on a cover sheet. On a separate data sheet I have: Period 1 Period 2 ... Units Cost Hours Units Costs Hours The oldest Labor Period is the farthest to the right. I need a formula that will find the Labor Period selected from a drop down (DV) and sum the values to the right of that Labor Period. So if Labor Period 5 is selected, I want to add up the hours for Labor Periods 5,4,3,2,1. Any help would be lovely. Beric . |
#4
|
|||
|
|||
locate a column, sum specific columns to the right of that col
Here's my response as posted in your other, later thread (believe you were
still to resolve the same issues that you posted here) ... -------------- Let's say you have col data in groups of 3 cols, with headers in row2 (eg: Period1, Period2, etc), where each header is placed only in the leftmost cell for the group, eg: Period1 Period2 9 88 76 6 99 11 1 22 44 5 88 23 etc To sum the col data corresponding to a specific header (eg: Period1), you could use* these triplets: =SUM(OFFSET(A:A,,MATCH("Period1",2:2,0)-1)) =SUM(OFFSET(A:A,,MATCH("Period1",2:2,0))) =SUM(OFFSET(A:A,,MATCH("Period1",2:2,0)+1)) *placed to avoid circular ref errors, of course The 1st will return the leftmost col's sum for Period1's group, the 2nd returns the middle col's sum, and the 3rd returns the rightmost col's sum. All 3 expressions are similar, where we adjust the OFFSET's col param returned by the MATCH (that's the: +1,-1 bits) to suit the particular col to "grab" for the sum. Instead of hardcoding "Period1" in the expressions, you could of course point to another cell/DV returning the col header for flexibility. Useful? thump the YES below -- Max Singapore --- |
Thread Tools | |
Display Modes | |
|
|