| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Every 4th column in a range tied to a control cell
Hello,
Any help with the attached would be much appreciated. I have a spreadsheet with the months of the year listed in a row starting in cell C1. However, every month has three columns, “2008”, “2007” and “Variance”. In addition, I have a drop down box in cell A1 which displays a month of the year. What I need is a formula that will sum only the 4th column (i.e. the 2008 values) based on the month that is displayed in the drop down box. For example, if the drop down box shows the month of March than the formula will sum the 2008 values for January, February and March only. All ideas are most welcome. Thanks, Steve |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Every 4th column in a range tied to a control cell
Need more detail on the layout.
the months of the year listed in a row starting in cell C1. Ok, if C1 is January (?) then where is February? However, every month has three columns, “2008”, “2007” and “Variance”. Ok, where are these located? sum the 2008 values OK, where are these values? -- Biff Microsoft Excel MVP wrote in message ... Hello, Any help with the attached would be much appreciated. I have a spreadsheet with the months of the year listed in a row starting in cell C1. However, every month has three columns, “2008”, “2007” and “Variance”. In addition, I have a drop down box in cell A1 which displays a month of the year. What I need is a formula that will sum only the 4th column (i.e. the 2008 values) based on the month that is displayed in the drop down box. For example, if the drop down box shows the month of March than the formula will sum the 2008 values for January, February and March only. All ideas are most welcome. Thanks, Steve |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Every 4th column in a range tied to a control cell
On Aug 18, 10:25*pm, "T. Valko" wrote:
Need more detail on the layout. the months of the year listed in a row starting in cell C1. Ok, if C1 is January (?) then where is February? However, every month has three columns, “2008”, “2007” and “Variance”. Ok, where are these located? sum the 2008 values OK, where are these values? -- Biff Microsoft Excel MVP wrote in message ... Hello, Any help with the attached would be much appreciated. I have a spreadsheet with the months of the year listed in a row starting in cell C1. However, every month has three columns, “2008”, “2007” and “Variance”. In addition, I have a drop down box in cell A1 which displays a month of the year. What I need is a formula that will sum only the 4th column (i.e. the 2008 values) based on the month that is displayed in the drop down box. For example, if the drop down box shows the month of March than the formula will sum the 2008 values for January, February and March only. All ideas are most welcome. Thanks, Steve Hi Biff, Responses to your questions are as follows: 1. Cell C1 is equal to Jan, cell F1 is equal to Feb etc. 2. The 3 headings associated with each month (2008, 2007 and Variance) appear on row 2. For example, in cell C1 the heading reads January then in cell C2 the heading reads 2008, D2 reads 2007 and cell E2 reads Variance. 3. The values for 2008 appear on row 3, with the actual results for 2008 appearing in C3, the results for 2007 appear in D3 and the variance between 2008 and 2007 appears in E3. Thank you for taking the time to assist me. Cheers, Steve |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Every 4th column in a range tied to a control cell
Hi
I think you will need to have your data in row 1 as true Excel dates (e.g. 01 Jan 2008) but with a Custom format FormatCellsNumberCustom mmm You will need to repeat the entries in each of cells D1 and E1 In cell F1 enter =DATE(YEAR(C1),Month(C1)+1,1) Copy this formula through G1:AL1 If you don't want the months to show in the extra 2 columns each time, then you could format the cells with Font the same colour as background so it doesn't show. Then, assuming your Dropdown Month required (also in an Excel Date format) is in B1, use the formula =SUMPRODUCT((MOD(COLUMN($C3:$AL3),3)=0)*(MONTH($C$ 1:$AL$1)<=MONTH($A$1))*$C3:$AL3) to get the Values for 2008. Copy down as required. If you want the values for 2007, just amend the MOD(COLUMN($C3:$AL3),3)=0 part to =1, and for the Total of the Variance amend to =2 -- Regards Roger Govier "sgltaylor" wrote in message ... On Aug 18, 10:25 pm, "T. Valko" wrote: Need more detail on the layout. the months of the year listed in a row starting in cell C1. Ok, if C1 is January (?) then where is February? However, every month has three columns, “2008”, “2007” and “Variance”. Ok, where are these located? sum the 2008 values OK, where are these values? -- Biff Microsoft Excel MVP wrote in message ... Hello, Any help with the attached would be much appreciated. I have a spreadsheet with the months of the year listed in a row starting in cell C1. However, every month has three columns, “2008”, “2007” and “Variance”. In addition, I have a drop down box in cell A1 which displays a month of the year. What I need is a formula that will sum only the 4th column (i.e. the 2008 values) based on the month that is displayed in the drop down box. For example, if the drop down box shows the month of March than the formula will sum the 2008 values for January, February and March only. All ideas are most welcome. Thanks, Steve Hi Biff, Responses to your questions are as follows: 1. Cell C1 is equal to Jan, cell F1 is equal to Feb etc. 2. The 3 headings associated with each month (2008, 2007 and Variance) appear on row 2. For example, in cell C1 the heading reads January then in cell C2 the heading reads 2008, D2 reads 2007 and cell E2 reads Variance. 3. The values for 2008 appear on row 3, with the actual results for 2008 appearing in C3, the results for 2007 appear in D3 and the variance between 2008 and 2007 appears in E3. Thank you for taking the time to assist me. Cheers, Steve |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Every 4th column in a range tied to a control cell
On Aug 19, 8:42*am, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote: Hi I think you will need to have your data in row 1 as true Excel dates (e.g.. 01 Jan 2008) but with a Custom format FormatCellsNumberCustom mmm You will need to repeat the entries in each of cells D1 and E1 In cell F1 enter =DATE(YEAR(C1),Month(C1)+1,1) Copy this formula through G1:AL1 If you don't want the months to show in the extra 2 columns each time, then you could format the cells with Font the same colour as background so it doesn't show. Then, assuming your Dropdown Month required (also in an Excel Date format) is in B1, use the formula =SUMPRODUCT((MOD(COLUMN($C3:$AL3),3)=0)*(MONTH($C$ 1:$AL$1)<=MONTH($A$1))*$C*3:$AL3) to get the Values for 2008. Copy down as required. If you want the values for 2007, just amend the MOD(COLUMN($C3:$AL3),3)=0 part to =1, and for the Total of the Variance amend to =2 -- Regards Roger Govier "sgltaylor" wrote in message ... On Aug 18, 10:25 pm, "T. Valko" wrote: Need more detail on the layout. the months of the year listed in a row starting in cell C1. Ok, if C1 is January (?) then where is February? However, every month has three columns, “2008”, “2007” and “Variance”. Ok, where are these located? sum the 2008 values OK, where are these values? -- Biff Microsoft Excel MVP wrote in message .... Hello, Any help with the attached would be much appreciated. I have a spreadsheet with the months of the year listed in a row starting in cell C1. However, every month has three columns, “2008”, “2007” and “Variance”. In addition, I have a drop down box in cell A1 which displays a month of the year. What I need is a formula that will sum only the 4th column (i.e. the 2008 values) based on the month that is displayed in the drop down box. For example, if the drop down box shows the month of March than the formula will sum the 2008 values for January, February and March only. All ideas are most welcome. Thanks, Steve Hi Biff, Responses to your questions are as follows: 1. Cell C1 is equal to Jan, cell F1 is equal to Feb etc. 2. The 3 headings associated with each month (2008, 2007 and Variance) appear on row 2. For example, in cell C1 the heading reads January then in cell C2 the heading reads 2008, D2 reads 2007 and cell E2 reads Variance. 3. The values for 2008 appear on row 3, with the actual results for 2008 appearing in C3, the results for 2007 appear in D3 and the variance between 2008 and 2007 appears in E3. Thank you for taking the time to assist me. Cheers, Steve- Hide quoted text - - Show quoted text - It worked! Thank you for taking the time to help me with this. Cheers |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I sort several columns with numbers tied to company names? | Excel Discussion (Misc queries) | |||
| No range option in format control for combo box | Excel Worksheet Functions | |||
| Microsoft Query with more than one parameter gets tied up | Excel Discussion (Misc queries) | |||
| How do I choose a cell colour, without it being tied to the data? | Excel Discussion (Misc queries) | |||
| How do I prevent tied results when ranking data? | Excel Worksheet Functions | |||
Hybrid Mode
