Selecting from a List

  • Thread starter Thread starter Warren Corston
  • Start date Start date
W

Warren Corston

I have a budget listing targeted figures by month. What is
a formula that will return the total to the end of last
month (ie if this month is October, it will return the sum
of Jul-Sep).
 
Warren,
assumed that the budget listing targeted figures by month are in column L
from L2 to L13 this formula will work
=SUM(OFFSET(L2,0,0,MONTH(NOW())-1,1))
HTH
Cecil
 
Hi Warren,

I'm don't understand exactly what you are trying to do...Can you be more
specific?

Thanks,

tim
 
=sumif(A:A,"<10/01/2003",B:B)

as an example. Column A contains dates, column B the amounts to be summed.
 
Cecil, thankyou for this. My month headings are in the
range E2:P2 (Jul-Jun) and the first product's targets are
in E3:P3. How should I rewrite the formula?
Warren
 
Tom, thankyou.
I have tried your suggestion substituting 2:2 for A:A and
3:3 for B:B because my month headings are in the range
E2:P2 (Jul-Jun) and the first product's targets are in
E3:P3. But that didn't work. How should I rewrite your
formula?
Cheers
Warren
 
okay, gather from your replies that this is your set-up

In E2:P2, you have Jul-02, Aug-02, Sep 02 ... Jun-03

In E3:P3, you have the corresponding monthly targets
for the 1st product, for example: $1000, $2000, $2500, etc

Likewise, assume you have the corresponding monthly targets
for the 2nd product in E4:P4, 3rd product in E5:P5, and so on

In the first empty row below all the monthly product targets,
say, in row 50:

Put in F50: =SUM($E3:E3) and copy across F50:P50
(this gives you what you want, ie the cumulative totals
to the end of the preceding month from your start month (Jul-02),
for e.g.: in G50 (Sep-02) will be the total of Jul-02 to Aug-02, and so on)

Select F50:P50, copy down as many rows as you have products

HTH
 
Warren,
sorry for the delay,
you can use
=SUM(OFFSET(E3,0,0,1,MONTH(NOW())-1))
Cecil
 
Are your headings actual dates or just the month names? Assume yes, but
this should work regardless

=SUM(OFFSET($E$3,0,0,1,(MONTH(TODAY())-6)*(MONTH(TODAY())>6)+(MONTH(TODAY())
<6)*(MONTH(TODAY())+6)))
 
If E2 is the month of July as implied by E2:P2 (Jul - Jun), then you would
need something like:

=SUM(OFFSET($E3,0,0,1,(MONTH(TODAY())-6)*(MONTH(TODAY())>6)+(MONTH(TODAY())<
6)*(MONTH(TODAY())+6)))

to add through the current month or to add only up to the month before:

=if(month(today()) =
7,0,SUM(OFFSET($E3,0,0,1,((MONTH(TODAY())-6)*(MONTH(TODAY())>6)+(MONTH(TODAY
())<6)*(MONTH(TODAY())+6))-1)))
 
Thankyou again Cecil. This gives the total of months Jul-
Mar not just Jul-Sep. We're obviously on the right track
but not quite there yet.
Cheers, Warren
 
Tom, thankyou again.
The headings are just the month names. I can change to
MMYY format if necessary.
Your recommendation below gives the total of Jul-Oct, not
Jul-Sep. So we're very close now and need one more tweak
I think.
Cheers
Warren
 
Thankyou Max.
I do indeed have the sheet set up as you say except that
the months are in MMM rather than MMMYY format. Your
suggestion does give the answers but I'm trying to show
the total to end of last month in a blank column "D" so
that no matter when I look at it, it will give the total
to end of the previous month. eg if I look at it this
month, it'll show the total for Jul-Sep and if I look at
it in December it'll show the total for Jul-Nov.
Cecil and Tom have got very close as you will see from my
responses. I'm very grateful to all of you for your
efforts.
Cheers
Warren
 
Back
Top