Select a range of cells

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

Jim Thomlinson

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

wx4usa

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

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top