Sum Every 4th column in a range tied to a control cell

[stvn.taylor]

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

 

[T. Valko]

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


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

 

[sgltaylor]

Need more detail on the layout.


Ok, if C1 is January (?) then where is February?


Ok, where are these located?


OK, where are these values?

--
Biff
Microsoft Excel MVP


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

 

[Roger Govier]

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 Format>Cells>Number>Custom> 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

 

[sgltaylor]

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 Format>Cells>Number>Custom> 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










- Show quoted text -

It worked!

Thank you for taking the time to help me with this.

Cheers