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.

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.
.

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?