try:

=SUM(INDIRECT(ADDRESS(ROW(C2),COLUMN(C2),1,TRUE) & ":" &
ADDRESS(C14+1,COLUMN(C2),1,)))

"Khoshravan" wrote:

I am trying to produce the upper reference (C2) and lower reference C(11) of
my array by use of Address, row and column functions and use them in SUM
function.
It works when I put them separately in different cells and use concatenate
and indirect to combine them as follows:

C14=COUNTA(C2:C11) = 9
C16=ADDRESS(ROW(C2),COLUMN(C2),1,TRUE) = the result is $C$2
C17=ADDRESS(C14+1,COLUMN(C2),1,) = the result is $C$10
C18=CONCATENATE(C16,":",C17)
C19=SUM(INDIRECT(C18)) = Gives the result of SUM(C2:C11)

When I try to substitute above functions in one cell as follows:
C20=SUM(ADDRESS(ROW(C2),COLUMN(C2),1,TRUE):ADDRESS (C14+1,COLUMN(C2),1,))
It says my formula has errors. What is the error and how can I solve it?
Isnt it allowed to use combination of functions for references?
--
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan