summing name range in the same column with Dynamic name ranges

J

Jeff

Hi all,
I have a dynamic name range in a column, say colA with name Test from A7
to the end of the column. When I set A5 = sum(test), it always gives me 0.
Any idea why?

Thanks,
 
B

Bob Phillips

Perhaps it is all text values.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
D

Dave Peterson

if you tried
=count(test)
what's returned.

If you see 0, then there are no numbers in that range.

And if you use:
edit|goto
type in Test
and hit enter

do you go to the range you expect?
 
J

Jeff

RagDyer,
test=OFFSET(Sheet1!B7,0,0,COUNTA(Sheet1!$B:$B) -COUNTA(Sheet1!$B$1:$B$6),1)

in cell B5 = SUM(test)

Thanks,
 
R

Ragdyer

Two things !

Take the formula [=Sum(test)] *out* of Column B.

Make the B7 reference *absolute*:

=OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B:$B)-COUNTA(Sheet1!$B$1:$B$6),1)


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Jeff said:
RagDyer,
Click to expand...
st=OFFSET(Sheet1!B7,0,0,COUNTA(Sheet1!$B:$B) -COUNTA(Sheet1!$B$1:$B$6),1)
 
J

Jeff

Ragdyer,
are you impling the sum can't be in the same column as of the name range?
if that's required, i guess i'll hv to use sum(b7:bwhatever) instead?

Thanks,

Ragdyer said:
Two things !

Take the formula [=Sum(test)] *out* of Column B.

Make the B7 reference *absolute*:

=OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B:$B)-COUNTA(Sheet1!$B$1:$B$6),1)


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Jeff said:
RagDyer,
Click to expand...
st=OFFSET(Sheet1!B7,0,0,COUNTA(Sheet1!$B:$B) -COUNTA(Sheet1!$B$1:$B$6),1)
in cell B5 = SUM(test)

Thanks,


----
Click to expand...
Click to expand...
 
R

Ragdyer

Every way I tried it, I get a circular reference, and I get 0 !

Don't you?

If you must have the formula in Column B, you could simply reference a cell
that is beyond any actual possibility of being exceeded.

=Sum(B7:B10000)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Jeff said:
Ragdyer,
are you impling the sum can't be in the same column as of the name range?
if that's required, i guess i'll hv to use sum(b7:bwhatever) instead?

Thanks,

Ragdyer said:
Two things !

Take the formula [=Sum(test)] *out* of Column B.

Make the B7 reference *absolute*:

=OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B:$B)-COUNTA(Sheet1!$B$1:$B$6),1)


--
HTH,

RD

--------------------------------------------------------------------------
Click to expand...
-
Please keep all correspondence within the NewsGroup, so all may benefit
Click to expand...
!
--------------------------------------------------------------------------
Click to expand...
-
=OFFSET(Sheet1!B7,0,0,COUNTA(Sheet1!$B:$B) -COUNTA(Sheet1!$B$1:$B$6),1)
---------------------------------------------------------------------------------------------------------------------------------------------------- Test
from
Click to expand...
Click to expand...
 
Top