Probably basic query

[Dave350z]

Hi folks,
First time post, but I need a bit of help with a query I have, I thought I was reasonably ok at excel, but this one has stumped me a little....

The below is a basic example

A B C D E F
1 Name Jan Feb Mar Apr May
2 Product 1 10 10 10 10 10


I have this for a twelve month period, but I want to put a number/month in say A5, that will total the sales for product 1.

So if I put 3 in A5, it will total the first three months (30), or if I put in 5 it will total the first five months (50)

I am at a loss as to how to do it, any help will be much appreciated!

Thanks

Dave

 

[kidwispa]

If you are adding the number of columns you want to put in A5, put the formula below exactly as it is written in B5 and it will calculate what you want...

=SUM(OFFSET($B$2,,,,$A$5))

 

[bertie]

Hi and welcome to the forum.

To sum a range of cells we use the Sum() function.
i.e., =sum(B2:D2)
But in your case the end range, "D2" is dynamic based on the value in cell A5.

We can use the Offset() function to determine the number of columns to sum.

Sheet1

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-weight:bold; ">Name</td><td style="font-weight:bold; ">Jan</td><td style="font-weight:bold; ">Feb</td><td style="font-weight:bold; ">Mar</td><td style="font-weight:bold; ">Apr</td><td style="font-weight:bold; ">May</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>Product</td><td style="text-align:right; ">10</td><td style="text-align:right; ">10</td><td style="text-align:right; ">10</td><td style="text-align:right; ">10</td><td style="text-align:right; ">10</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="color:#ffffff; ">x</td><td style="color:#ffffff; ">x</td><td style="color:#ffffff; ">x</td><td style="color:#ffffff; ">x</td><td style="color:#ffffff; ">x</td><td style="color:#ffffff; ">x</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="color:#ffffff; ">x</td><td style="color:#ffffff; ">x</td><td style="color:#ffffff; ">x</td><td style="color:#ffffff; ">x</td><td style="color:#ffffff; ">x</td><td style="color:#ffffff; ">x</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:right; ">5</td><td>
</td><td style="color:#ffffff; ">x</td><td style="color:#ffffff; ">x</td><td style="color:#ffffff; ">x</td><td style="color:#ffffff; ">x</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:right; ">50</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>A6</td><td>=SUM(B2:OFFSET(B2,0,A5-1))</td></tr></tbody></table></td></tr></tbody></table>

 

[Dave350z]

Thanks a million folks!!! Worked a treat!