Workbook and IF,THEN, ELSE
 

Thank you in advance for any responses.

I am trying to make a cell sum a range of cells based on what month i
is according to the system clock. If the clock shows January then SU
g3:j3, if the clock shows February then SUM k3:n3 and so on...
Thank you again

--
Message posted from http://www.ExcelForum.com

Workbook and IF,THEN, ELSE
 

Hi PoolShark!

One way. Try:

=SUM(INDIRECT("R3C"&3+(MONTH(TODAY())*4)&":R3C"&6+ (MONTH(TODAY())*4),0
))

I use INDIRECT with the False second argument to construct a
calculated R1C1 reference.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Workbook and IF,THEN, ELSE
 

Poolshark,

=SUM(OFFSET(G3:J3,0,(MONTH(NOW())-1)*4))

Rob


"poolshark " wrote in message
...
Thank you in advance for any responses.

I am trying to make a cell sum a range of cells based on what month it
is according to the system clock. If the clock shows January then SUM
g3:j3, if the clock shows February then SUM k3:n3 and so on...
Thank you again.


---
Message posted from http://www.ExcelForum.com/

Workbook and IF,THEN, ELSE
 

On Sat, 24 Jan 2004 22:55:49 -0600, poolshark
wrote:

I am trying to make a cell sum a range of cells based on what month it
is according to the system clock. If the clock shows January then SUM
g3:j3, if the clock shows February then SUM k3:n3 and so on...
Thank you again.

You could do this in a single formula, but just to make it clearer
I'll break it into two steps:

First you need to know which column you want to start at. One way to
determine that is through a CHOOSE function. Let's say that you have
that function in cell A1:
=CHOOSE(MONTH(NOW()),6,11,16,21,26,31)

I've typed only the first 6 months because I'm basically lazy, but
you'd put all 12 in. The NOW() function returns the current date. The
MONTH function returns the number of the current month. The CHOOSE
function says "If it's month 1, return 6. If it's month 2, return 11"
and so on. The number that it returns in this case is the column that
you start adding from. (G3 is in column 6, K3 is in column 11; you get
the idea.)

Now you can have the SUM function:
=SUM(OFFSET(A3,0,$A$1):OFFSET(A3,0,$A$1+3))

That is, sum the cells which are offset from cell A3 by:
0 rows; and
However many columns the CHOOSE formula returns to the choose
function's value +3. (In your example you're always adding 4 columns).

This can of course be copied down as far as you want to add rows all
the way down your sheet if you wish.

There are doubtless other ways of doing it, perhaps even some more
elegant, but hey, this'll get the job done...


---------------------------------------------------------
Hank Scorpio
- Yes, yes, I know I've been missing for a while. Long story. And don't
ask what the size of my inbox is at the moment either... 8^
scorpionet who hates spam is at iprimus.com.au (You know what to do.)

Workbook and IF,THEN, ELSE
 

poolshark,
in VBA,
evaluate("=SUM(OFFSET(A1,2,2+MONTH(TODAY())*4,1,4) )")
in worksheet
=SUM(OFFSET(A1,2,2+MONTH(TODAY())*4,1,4))
HTH
Cecil

"poolshark " wrote in message
...
Thank you in advance for any responses.

I am trying to make a cell sum a range of cells based on what month it
is according to the system clock. If the clock shows January then SUM
g3:j3, if the clock shows February then SUM k3:n3 and so on...
Thank you again.


---
Message posted from http://www.ExcelForum.com/