Adding variable number of columns

[fabio]

Hi

Using Excel 2003 I want to create a formula which will add a varing number
of columns together depending on the entry in a particular cell, for a
budgeting spreadsheet.

For example if I have a year to date total in the 13th column and I was to
type January in cell A1 I would want the value in the first column to be
displayed in the year to date total (column 13). If I put June in A1 I want
the sum of the first 6 columns to be displayed in the YTD total etc for an
entire year.

I was able to use a nested IF statement last year as there were only 3
months remaining when this job was required. However, my understanding is
that I can't have more than 7 nested IFs in versions prior to Excel 2007 so I
need an alternative approach.

Thanks for any assistance given.

 

[Jacob Skaria]

Refer help on the formula OFFSET()..

If you cannot make it; post sample data and explain a bit more...

 

[Jacob Skaria]

I should have said SUM() OFFSET() combination as below..

=SUM(OFFSET(..,..,..,..))

 

[fabio]

Hi Jacob

Thanks but I can't see how to make the range variable. Example would be:

A B C D E ........... N
1 XXXXX
2 January February March April Total
3 Cost Ctr
4 CC01 1 1 1 1...........
5 CC02 2 2 2 2............

So if I type 'Jan' in A1 the total in N4 should show 1 and N5 2. If I type
in 'Mar' N4 should be 3 and N5 6 etc to cover all 12 months.

I hope that clarifies.

Thanks

 

[Jacob Skaria]

Try the below in cell N4 and copy down as required...

I assume you have text entries in cell A1 and in the range B2:M2....If you
have entered "January", "February" etc; in cell B2 cell A1 also should have
the full entry such as "January" without spaces and not "Jan", "Fen"

=SUM(OFFSET($B4,,,1,MATCH($A$1,$B$2:$M$2,0)))

 

[fabio]

Hi Jacob

Great and thanks for your help. That seems to work just fine. I've put a
validation on B2 to make sure the entry matches the column headers.

 

[Teethless mama]

Non volatile solution:

=SUM(B4:INDEX(B4:M4,MATCH($A$1,$B$2:$M$2,0)))

 

[fabio]

Hi

Thanks for the input. What do you mean non-volatile solution?

 

[Ashish Mathur]

Hi,

Try this formula in cell N4

=sum(B4:index($B2:$M5,row()-1,match($A$1,B$2:M$2,0)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com