Adding variable number of columns

F

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.
 
J

Jacob Skaria

Refer help on the formula OFFSET()..

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

Jacob Skaria

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

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

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
 
J

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)))
 
F

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top