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.

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.

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.

.

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.

.

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

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
.

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

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.