| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select a range of cells
I have a sales budget. Row 2 across is Budgeted sales for each month
January - December. I need to know how to return a sum of budgeted sales to date. For example, if I am looking at a budget report, I need a formula return just columns Jan-Aug budgeted sales if I am reporting budgeted YTD sales thru August based on a cell containing the August date. ie. Budgeted sales. Select Month: August. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Select a range of cells
Here is one way
=SUM(OFFSET(B2,0,0,1,MATCH(B1,$B$1:$M$1,0))) This formula assumes that Your values to total are in B2:M2 Your titles Jan - Dec are in B1:M1 Your period to total to (Aug) is in B1 Note that this function uses offset which is volatile and as such comes with extra calculation overhead. If you end up with thousands of this type of formula you will notice slow recalculations... -- HTH... Jim Thomlinson "wx4usa" wrote: I have a sales budget. Row 2 across is Budgeted sales for each month January - December. I need to know how to return a sum of budgeted sales to date. For example, if I am looking at a budget report, I need a formula return just columns Jan-Aug budgeted sales if I am reporting budgeted YTD sales thru August based on a cell containing the August date. ie. Budgeted sales. Select Month: August. . |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Select a range of cells
On Dec 23, 1:32*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: Here is one way =SUM(OFFSET(B2,0,0,1,MATCH(B1,$B$1:$M$1,0))) This formula assumes that Your values to total are in B2:M2 Your titles Jan - Dec are in B1:M1 Your period to total to (Aug) is in B1 Note that this function uses offset which is volatile and as such comes with extra calculation overhead. If you end up with thousands of this type of formula you will notice slow recalculations... -- HTH... Jim Thomlinson "wx4usa" wrote: I have a sales budget. Row 2 across is Budgeted sales for each month January - December. I need to know how to return a sum of budgeted sales to date. For example, if I am looking at a budget report, *I need a formula return just columns Jan-Aug budgeted sales if I am reporting budgeted YTD sales thru August based on a cell containing the August date. ie. Budgeted sales. Select Month: August. . Is there a way to add a MATCH function (Like in the last part of the formula) to the start month so I can have a range such as May-August? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use xldown to select a range of cells | Excel Discussion (Misc queries) | |||
| When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
| Select a range but only sum the cells in a certain colour? | Excel Worksheet Functions | |||
| Select Range of Cells | Excel Discussion (Misc queries) | |||
| select a range using "cells()" | Excel Worksheet Functions | |||
Linear Mode
