Selecting from a List

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

 

[Cecilkumara Fernando]

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

 

[Tim Otero]

Hi Warren,

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

Thanks,

tim

 

[Tom Ogilvy]

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

as an example. Column A contains dates, column B the amounts to be summed.

 

[Warren Corston]

Cecil, thankyou for this. My month headings are in the
range E22 (Jul-Jun) and the first product's targets are
in E33. How should I rewrite the formula?
Warren

 

[Warren Corston]

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
E22 (Jul-Jun) and the first product's targets are in
E33. But that didn't work. How should I rewrite your
formula?
Cheers
Warren

 

[Max]

okay, gather from your replies that this is your set-up

In E22, you have Jul-02, Aug-02, Sep 02 ... Jun-03

In E33, 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 E44, 3rd product in E55, 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 F5050
(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 F5050, copy down as many rows as you have products

HTH

 

[Cecilkumara Fernando]

Warren,
sorry for the delay,
you can use
=SUM(OFFSET(E3,0,0,1,MONTH(NOW())-1))
Cecil

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

If E2 is the month of July as implied by E22 (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)))

 

[warren Corston]

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

 

[Warren Corston]

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

 

[Warren Corston]

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

 

[Warren Corston]

Tom, that's it. Perfect!

Thankyou for your help.

 

[Max]

Thanks for the feedback, Warren!

Glad you got the solution you wanted from Tom O.

cheers.