Help with dynamic sum formula

[Guest]

Hi,
I'm hoping someone could please help me.

I have a spreadsheet setup which has on one sheet columns of data that
represent data for a particular month. The column heading is setup to advance
forward a month depending on the point in time during the year ( eg in August
2005 the first column heading is set at Sep-05. In Sep-05 the first column
heading will become Oct-05 and so on).

I want to populate each of the columns with data that looks at another
worksheet with information arranged in columns defined by a particular month.
The month headings in this sheet are fixed and do not change.

I can't simply put a sum formula in the first sheet though as the applicable
column the formula looks at will change depending on the month. (ie. The
applicable sum formula may be in column B one month but need to be in column
A the following month).

I realise I can simply move the formulas one column to the left each change
in month however I was hoping to have the sheet function automatically.

I have a hunch an array formula may help but I am not that skilled in them.

Hopefully my question makes sense. Can someone help please?


Thanks,

Adam Wood

 

[Richard Buttrey]

Hi,
I'm hoping someone could please help me.

I have a spreadsheet setup which has on one sheet columns of data that
represent data for a particular month. The column heading is setup to advance
forward a month depending on the point in time during the year ( eg in August
2005 the first column heading is set at Sep-05. In Sep-05 the first column
heading will become Oct-05 and so on).

I want to populate each of the columns with data that looks at another
worksheet with information arranged in columns defined by a particular month.
The month headings in this sheet are fixed and do not change.

I can't simply put a sum formula in the first sheet though as the applicable
column the formula looks at will change depending on the month. (ie. The
applicable sum formula may be in column B one month but need to be in column
A the following month).

I realise I can simply move the formulas one column to the left each change
in month however I was hoping to have the sheet function automatically.

I have a hunch an array formula may help but I am not that skilled in them.

Hopefully my question makes sense. Can someone help please?


Thanks,

Adam Wood

You can put a formula on sheet 1, and don't need to use array
formulae. There are no doubt other solutions, but try this one.

Assuming your information is in cells C1:E10 on sheet2, and that row 1
contains the month name headings, give the range C1:E10 the name
"data", and C1:E1 the name "months" .

On sheet1 Assuming the column heading is in A6 and that A6 is in the
same format as the month headings on sheet2, enter the following in A7

=INDEX(data,ROW()-5,MATCH(A$6,months))

Then copy this down column A to A15.

You'll have to change the "-5" bit to match your layout. This is the
difference in this example, between the "6" of A6, and the row "1" of
the range C1:E1 "months" range on sheet2

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Guest]

Richard,
Thanks for your response.
I have tried your suggestion however the problem I have is that I need to
sum several rows in the "data" range once the column is selected. I can't
seem to get the INDEX to do this?

Appreciate the assistance.

Adam Wood

 

[Richard Buttrey]

Adam,

OK, what are the rules for identifying which rows to sum?

If you'd like to offer a small example I'll have another look.

Rgds

Richard,
Thanks for your response.
I have tried your suggestion however the problem I have is that I need to
sum several rows in the "data" range once the column is selected. I can't
seem to get the INDEX to do this?

Appreciate the assistance.

Adam Wood

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Guest]

I've seen no activity here for some time so maybe you've lost interest. If
not, it's possible to use a form of the INDEX function which returns a
specified column of a range. If the range to be summed is a contiguous range
of cells in the column, then simply apply the SUM function to the returned
column.

For example, assume your data is in Sheet 2, cells A1:L20, with the month
headers in A1:L1. As the previous responder suggested, it's a little neater
if you define a name "months" which in this case would refer to
=Sheet2!$A$1:$L$1

On Sheet 1, assume your column headers are also in row 1, and that in some
cell in column A you want a formula to sum the values in rows 10 through 20
of the appropriate column in sheet 2 (with header matching the header in
Sheet 1 cell A1). Use:

=SUM(INDEX(Sheet2!$A$10:$L$20,,MATCH(A$1,months,0)))

If the cells to be summed do not lie in a contiguous range, then you can
resort to a SUMPRODUCT formula. For example, if you want to sum the values
in rows 10, 12, 15, 17, and 20 of the appropriate column, you could use

=SUMPRODUCT(INDEX(Sheet2!$A$10:$L$20,,MATCH(A$1,months,0))*(ROW($10:$20)={10,12,15,17,20}))