locate a column, sum specific columns to the right of that column

[Beric Dondarrion]

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

 

[Bernard Liengme]

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

 

[Beric Dondarrion]

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.

 

[Max]

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