Sum column data based on dynamic header

[y2gabs]

Hi there,

I figured this would be easy but seem to be stumped. I have a large data table with various dates representing header row (column headers). What I would like to do is sum a column based on a date selected. For example:

July 1 | July 5 | July 9
4 | 7 | 22
3 | 6 | 34

If I have a separate worksheet with a cell called "July 1", I'd like the cell beside that cell to sum all of the values in the column on the data sheet with column header "July 1".

I've tried various iterations of Match and Index but really seem to be stumped. Any help would be huge!

Thanks!

P.S. many apologies if this already exists in another thread. I've done some extensive searching but may be getting my queries wrong.

 

[AlphaFrog]

Try something like this...

=SUM(INDEX(Sheet1!A2:D4,0,MATCH(A1, Sheet1!A1:D1,0)))

Sheet1!A2:D4 = Columns of Data to sum

Sheet1!A1:D1 = column headers

A1 = date to match in column headers

 

[BiocideJ]

This should do it. In this example, all of the data and formulas are on the same sheet, however, it shouldn't be any problem at all to change the reference to your sheet.

Excel 2007

	A	B	C	D	E	F	G
1	7/1/2012	7/5/2012	7/9/2012
2	4	7	22			5-Jul	13
3	3	6	34

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula G2 =SUM(OFFSET(A2:A65000,0,MATCH(F2,1:1,0)-1)) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Just make sure the data type is the same in your column headers (A1:C1) as it is in your lookup value (F2).
i.e. both text, both dates, etc.