Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default Nested If Statements

Hoping someone can help.

I have a sheet that has 12 months of data going across 12 columns. Each
column has a heading for each month i.e 1, 2 ,3 ... There are multiple rows
of data. I need a formula that will sum the columns of data based on the
month that is entered in cell A1. For example, if I enter 5 in cell A1, I
want the YTD formula to add up only the first 5 months of data.

Thanks in advance for any help.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 563
Default Nested If Statements

My worksheet has 1,2,,3...12 in B1:M1
In the 20 rows below I have data
In A1 I enter a number, say 5
In A2 I have =SUMPRODUCT((B1:M1<=A1)*B2:M21)
This sum all the data for the columns having number 1 thru 5 in row 1
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Dave" wrote in message
...
Hoping someone can help.

I have a sheet that has 12 months of data going across 12 columns. Each
column has a heading for each month i.e 1, 2 ,3 ... There are multiple
rows
of data. I need a formula that will sum the columns of data based on the
month that is entered in cell A1. For example, if I enter 5 in cell A1, I
want the YTD formula to add up only the first 5 months of data.

Thanks in advance for any help.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default Nested If Statements

That worked great for summing up all the data in the range.

One more question. I also need to have a total for each row of data. What
formula would I need to use on each row to total each row based on the month
keyed into cell A1?


"Bernard Liengme" wrote:

My worksheet has 1,2,,3...12 in B1:M1
In the 20 rows below I have data
In A1 I enter a number, say 5
In A2 I have =SUMPRODUCT((B1:M1<=A1)*B2:M21)
This sum all the data for the columns having number 1 thru 5 in row 1
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Dave" wrote in message
...
Hoping someone can help.

I have a sheet that has 12 months of data going across 12 columns. Each
column has a heading for each month i.e 1, 2 ,3 ... There are multiple
rows
of data. I need a formula that will sum the columns of data based on the
month that is entered in cell A1. For example, if I enter 5 in cell A1, I
want the YTD formula to add up only the first 5 months of data.

Thanks in advance for any help.

.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default Nested If Statements

No need to respond. I figured it out.

Thanks again for your help.



"Dave" wrote:

That worked great for summing up all the data in the range.

One more question. I also need to have a total for each row of data. What
formula would I need to use on each row to total each row based on the month
keyed into cell A1?


"Bernard Liengme" wrote:

My worksheet has 1,2,,3...12 in B1:M1
In the 20 rows below I have data
In A1 I enter a number, say 5
In A2 I have =SUMPRODUCT((B1:M1<=A1)*B2:M21)
This sum all the data for the columns having number 1 thru 5 in row 1
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Dave" wrote in message
...
Hoping someone can help.

I have a sheet that has 12 months of data going across 12 columns. Each
column has a heading for each month i.e 1, 2 ,3 ... There are multiple
rows
of data. I need a formula that will sum the columns of data based on the
month that is entered in cell A1. For example, if I enter 5 in cell A1, I
want the YTD formula to add up only the first 5 months of data.

Thanks in advance for any help.

.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Nested If Statements

On Thu, 6 May 2010 13:02:01 -0700, Dave wrote:

Hoping someone can help.

I have a sheet that has 12 months of data going across 12 columns. Each
column has a heading for each month i.e 1, 2 ,3 ... There are multiple rows
of data. I need a formula that will sum the columns of data based on the
month that is entered in cell A1. For example, if I enter 5 in cell A1, I
want the YTD formula to add up only the first 5 months of data.

Thanks in advance for any help.



I assume you want to sum everything that is in one row:

=SUM(OFFSET(B2,,,1,A1))

Change B2 to the cell that has the data for January in the row of choice.

--ron


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default Nested If Statements

Thanks Ron,

Works like a charm



"Ron Rosenfeld" wrote:

On Thu, 6 May 2010 13:02:01 -0700, Dave wrote:

Hoping someone can help.

I have a sheet that has 12 months of data going across 12 columns. Each
column has a heading for each month i.e 1, 2 ,3 ... There are multiple rows
of data. I need a formula that will sum the columns of data based on the
month that is entered in cell A1. For example, if I enter 5 in cell A1, I
want the YTD formula to add up only the first 5 months of data.

Thanks in advance for any help.



I assume you want to sum everything that is in one row:

=SUM(OFFSET(B2,,,1,A1))

Change B2 to the cell that has the data for January in the row of choice.

--ron
.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Nested If Statements

On Fri, 7 May 2010 14:14:01 -0700, Dave wrote:

Thanks Ron,

Works like a charm


Glad to help. Thanks for the feedback.
--ron
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default Nested If Statements

Hi,

Try this

=sumif($B2:$N2,$A$1,$B3:$N3)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dave" wrote in message
...
Hoping someone can help.

I have a sheet that has 12 months of data going across 12 columns. Each
column has a heading for each month i.e 1, 2 ,3 ... There are multiple
rows
of data. I need a formula that will sum the columns of data based on the
month that is entered in cell A1. For example, if I enter 5 in cell A1, I
want the YTD formula to add up only the first 5 months of data.

Thanks in advance for any help.

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
Help with Nested IF Statements Ken Excel Worksheet Functions 4 December 8th 09 07:41 PM
Nested IF statements Karrisac15 New Users to Excel 1 July 19th 07 08:06 PM
Nested if statements - is there a better way? masterbaker Excel Worksheet Functions 3 July 25th 06 04:59 PM
Nested If Statements Jasmine Excel Worksheet Functions 2 January 26th 06 04:47 PM
Nested IF statements maacmaac Excel Discussion (Misc queries) 0 January 23rd 06 10:22 PM


All times are GMT +1. The time now is 10:29 AM.

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"