how do you specify the current column in a sum function

M

mr unreliable

hi Group,

I have rows named BEG_INT and END_INT. I am summing a range of rows between
these rows for columns named FY_2001, FY_2002, etc.

The row of sums has (intersection-type) formulas which like this:

=SUM(FY_2001 BEG_INT : END_INT)
=SUM(FY_2002 BEG_INT : END_INT)
=SUM(FY_2003 BEG_INT : END_INT)

While this does work ok, I would prefer to have the same formula extended
across the summation row.

How would I go about defining a named function "CurrentColumn", so that I
could use the same formula across my row of sums?

tiafah, jw
 
H

Harlan Grove

mr unreliable said:
The row of sums has (intersection-type) formulas which like this:

=SUM(FY_2001 BEG_INT : END_INT)
=SUM(FY_2002 BEG_INT : END_INT)
=SUM(FY_2003 BEG_INT : END_INT)

While this does work ok, I would prefer to have the same formula extended
across the summation row.
Click to expand...
....

If you mean you want to use the same formula in all cells so that you could
enter it in the leftmost column then fill it right rather than having to
edit the column component, then try

=SUM(INDIRECT("C:C",0) Beg_Int:End_Int)

INDIRECT("C:C",0) is the current column given in R1C1 addressing syntax. You
could even make this more readable by defining the name __CurrentColumn__
referring to =INDIRECT("C:C",0), then writing the SUM formulas as

=SUM(__CurrentColumn__ Beg_Int:End_Int)
 
J

Jonathan Rynd

=SUM(INDIRECT("C:C",0) Beg_Int:End_Int)

INDIRECT("C:C",0) is the current column given in R1C1 addressing syntax.
Click to expand...

How does Excel know this is what you meant, rather than column C?
 
H

Harlan Grove

How does Excel know this is what you meant, rather than column C?
Click to expand...

This may be a novel concept: read online help for the INDIRECT function,
specifically its second argument. Then it may become clear.
 
S

Sandy Mann

Harlan Grove said:
This may be a novel concept: read online help for the INDIRECT function,
specifically its second argument. Then it may become clear.
Click to expand...

OK I indulged in the novel concept and I see what is happening but wouldn't:

=SUM(INDIRECT("C",0) Beg_Int:End_Int)

work just as well?

Regards

Sandy
 

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

Similar Threads

Sum of values in column A based on 0/1 of column B 2
Sum Column above current cell dynamically using function call 7
Function for summing or counting cells based on a specified fill color 3
Excel 2000 formula to dynamically sum adjacent rows and columns whererows inserted/deleted 3
Sum Of Last Two Entries in Rotating Columns 6
Sum Function When Formula Produces #N/A 2
defining a sum using named rows and columns 3
How do I put a variable column number in the sum() function? 8

Top