Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 28
Default Adding variable number of columns

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Adding variable number of columns

Refer help on the formula OFFSET()..

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

--
Jacob (MVP - Excel)


"fabio" wrote:

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.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Adding variable number of columns

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

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

--
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

Refer help on the formula OFFSET()..

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

--
Jacob (MVP - Excel)


"fabio" wrote:

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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 28
Default Adding variable number of columns

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" wrote:

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

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

--
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

Refer help on the formula OFFSET()..

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

--
Jacob (MVP - Excel)


"fabio" wrote:

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.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Adding variable number of columns

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

--
Jacob (MVP - Excel)


"fabio" wrote:

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" wrote:

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

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

--
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

Refer help on the formula OFFSET()..

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

--
Jacob (MVP - Excel)


"fabio" wrote:

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.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 28
Default Adding variable number of columns

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.

"Jacob Skaria" wrote:

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

--
Jacob (MVP - Excel)


"fabio" wrote:

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" wrote:

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

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

--
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

Refer help on the formula OFFSET()..

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

--
Jacob (MVP - Excel)


"fabio" wrote:

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.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default Adding variable number of columns

Non volatile solution:

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



"fabio" wrote:

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.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 28
Default Adding variable number of columns

Hi

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



"Teethless mama" wrote:

Non volatile solution:

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



"fabio" wrote:

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.

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default Adding variable number of columns

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

"fabio" wrote in message
...
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" wrote:

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

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

--
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

Refer help on the formula OFFSET()..

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

--
Jacob (MVP - Excel)


"fabio" wrote:

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sum number of columns based on variable value rt10516 Excel Discussion (Misc queries) 4 November 8th 07 02:47 PM
Macro - Using a variable number of columns in a Range stumped Excel Discussion (Misc queries) 2 October 3rd 06 08:13 PM
Adding variable number of blanks ringnab Excel Discussion (Misc queries) 2 July 12th 06 09:29 PM
adding variable number of columns confused Excel Worksheet Functions 5 September 23rd 05 10:09 AM
Chart based on variable number of columns Scott Hamilton Charts and Charting in Excel 2 June 5th 05 03:36 PM


All times are GMT +1. The time now is 04:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"