Select a range of cells

  • Thread starter Thread starter wx4usa
  • Start date Start date
W

wx4usa

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.
 
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...
 
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...
Click to expand...

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?
 
Back
Top