Nested If Statements
in [Functions]
|
Prev: Edit Excel file
Next: VLOOKUP IS ERROR
From: Dave on 6 May 2010 16:02 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.
From: Bernard Liengme on 6 May 2010 16:11 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" <Dave(a)discussions.microsoft.com> wrote in message news:4F460C6A-753C-4CEC-8677-89D9222A4592(a)microsoft.com... > 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. >
From: Dave on 6 May 2010 17:01 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" <Dave(a)discussions.microsoft.com> wrote in message > news:4F460C6A-753C-4CEC-8677-89D9222A4592(a)microsoft.com... > > 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. > > > . >
From: Dave on 6 May 2010 17:05 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" <Dave(a)discussions.microsoft.com> wrote in message > > news:4F460C6A-753C-4CEC-8677-89D9222A4592(a)microsoft.com... > > > 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. > > > > > . > >
From: Ron Rosenfeld on 6 May 2010 20:54
On Thu, 6 May 2010 13:02:01 -0700, Dave <Dave(a)discussions.microsoft.com> 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 |