Maximum number of cells for 'SUM' ?

I have a spreadsheet with 56,314 rows of text labels.

I have a formula in each row off to the right of each text label. The
formula tests for a specific text string contained in the label and records a
1 if true.

At the top of the sheet above this column of formulas, I want to sum the
number of 'TRUE' responses, or 1(s).

My formula is: =SUM(I5:I56,314). But this returns a #NAME? error
message. If I limit the SUM formula to not cover beyond cell I9168, then I
get a correct answer and no error msg.

Is this a limit of Excel's ability to calculate, a limitation imposed by my
RAM resources, or something else ?

You probably have a cell in the range that contains the #NAME? error.

You said you have a formula that returns 1 if TRUE. What does it return if
FALSE?

See if one of these does what you want...

=SUMIF(I5:I56314,"1E100")

=COUNTIF(I5:I56314,1)

--
Biff
Microsoft Excel MVP


"Groybs" wrote in message
...
I have a spreadsheet with 56,314 rows of text labels.

I have a formula in each row off to the right of each text label. The
formula tests for a specific text string contained in the label and
records a
1 if true.

At the top of the sheet above this column of formulas, I want to sum the
number of 'TRUE' responses, or 1(s).

My formula is: =SUM(I5:I56,314). But this returns a #NAME? error
message. If I limit the SUM formula to not cover beyond cell I9168, then I
get a correct answer and no error msg.

Is this a limit of Excel's ability to calculate, a limitation imposed by
my
RAM resources, or something else ?

#NAME? means you have a Function in the range that Excel can't recognize
http://www.ozgrid.com/Excel/formula-errors.htm



--
Regards
Dave Hawley
www.ozgrid.com
"Groybs" wrote in message
...
I have a spreadsheet with 56,314 rows of text labels.

I have a formula in each row off to the right of each text label. The
formula tests for a specific text string contained in the label and
records a
1 if true.

At the top of the sheet above this column of formulas, I want to sum the
number of 'TRUE' responses, or 1(s).

My formula is: =SUM(I5:I56,314). But this returns a #NAME? error
message. If I limit the SUM formula to not cover beyond cell I9168, then I
get a correct answer and no error msg.

Is this a limit of Excel's ability to calculate, a limitation imposed by
my
RAM resources, or something else ?

Just remove the comma from
.. =SUM(I5:I56,314)
ie use: =SUM(I5:I56314)

And if you have no numbers in I1:I4,
you could use the much simpler: =SUM(I:I)
--
Max
Singapore
---


"Groybs" wrote:

I have a spreadsheet with 56,314 rows of text labels.

I have a formula in each row off to the right of each text label. The
formula tests for a specific text string contained in the label and records a
1 if true.

At the top of the sheet above this column of formulas, I want to sum the
number of 'TRUE' responses, or 1(s).

My formula is: =SUM(I5:I56,314). But this returns a #NAME? error
message. If I limit the SUM formula to not cover beyond cell I9168, then I
get a correct answer and no error msg.

Is this a limit of Excel's ability to calculate, a limitation imposed by my
RAM resources, or something else ?

Thank you for your kind and timely reply. I followed your link to the
"Formula Errors" page and it helped me to understand what to look for.

Turns out that my formula resulted in the return of a #NAME? error for in 6
of the 56,314 rows of text. These resulted from the text strings beginning
with a - or a ".".
Probably some truncation of the original string.

Anyway, located them all. Deleted. Everything works fine now. Thanks.

"ozgrid.com" wrote:

#NAME? means you have a Function in the range that Excel can't recognize
http://www.ozgrid.com/Excel/formula-errors.htm



--
Regards
Dave Hawley
www.ozgrid.com
"Groybs" wrote in message
...
I have a spreadsheet with 56,314 rows of text labels.

I have a formula in each row off to the right of each text label. The
formula tests for a specific text string contained in the label and
records a
1 if true.

At the top of the sheet above this column of formulas, I want to sum the
number of 'TRUE' responses, or 1(s).

My formula is: =SUM(I5:I56,314). But this returns a #NAME? error
message. If I limit the SUM formula to not cover beyond cell I9168, then I
get a correct answer and no error msg.

Is this a limit of Excel's ability to calculate, a limitation imposed by
my
RAM resources, or something else ?

Thanks. And a special thanks for the (I:I) tip. I didn't know that one.

"Max" wrote:

Just remove the comma from
.. =SUM(I5:I56,314)
ie use: =SUM(I5:I56314)

And if you have no numbers in I1:I4,
you could use the much simpler: =SUM(I:I)
--
Max
Singapore
---


"Groybs" wrote:

I have a spreadsheet with 56,314 rows of text labels.

I have a formula in each row off to the right of each text label. The
formula tests for a specific text string contained in the label and records a
1 if true.

At the top of the sheet above this column of formulas, I want to sum the
number of 'TRUE' responses, or 1(s).

My formula is: =SUM(I5:I56,314). But this returns a #NAME? error
message. If I limit the SUM formula to not cover beyond cell I9168, then I
get a correct answer and no error msg.

Is this a limit of Excel's ability to calculate, a limitation imposed by my
RAM resources, or something else ?

Thanks for your quick response. My formula returns a "" (blank) if FALSE.
But, it turns out that I had 6 formula cells within the 56,314 rows of
replicated formula where the formula returned a #NAME? error because the text
string it was searching began with either a "." or a "-", so it thought the
text string was a formula.

I deleted all 6 of these errors and the SUM function now works perfectly,
returning a sum of 10,232 positive tests.

But I used the PAGE DOWN key to scroll all 56,314 rows. There's GOT to be a
better way to search for errors.

Thanks again.

"T. Valko" wrote:

You probably have a cell in the range that contains the #NAME? error.

You said you have a formula that returns 1 if TRUE. What does it return if
FALSE?

See if one of these does what you want...

=SUMIF(I5:I56314,"1E100")

=COUNTIF(I5:I56314,1)

--
Biff
Microsoft Excel MVP


"Groybs" wrote in message
...
I have a spreadsheet with 56,314 rows of text labels.

I have a formula in each row off to the right of each text label. The
formula tests for a specific text string contained in the label and
records a
1 if true.

At the top of the sheet above this column of formulas, I want to sum the
number of 'TRUE' responses, or 1(s).

My formula is: =SUM(I5:I56,314). But this returns a #NAME? error
message. If I limit the SUM formula to not cover beyond cell I9168, then I
get a correct answer and no error msg.

Is this a limit of Excel's ability to calculate, a limitation imposed by
my
RAM resources, or something else ?


.

There's GOT to be a
better way to search for errors.


F5 and then Special and choose Errors. Ctrl+F is another way.


--
Regards
Dave Hawley
www.ozgrid.com
"Groybs" wrote in message
...
Thanks for your quick response. My formula returns a "" (blank) if FALSE.
But, it turns out that I had 6 formula cells within the 56,314 rows of
replicated formula where the formula returned a #NAME? error because the
text
string it was searching began with either a "." or a "-", so it thought
the
text string was a formula.

I deleted all 6 of these errors and the SUM function now works perfectly,
returning a sum of 10,232 positive tests.

But I used the PAGE DOWN key to scroll all 56,314 rows. There's GOT to be
a
better way to search for errors.

Thanks again.

"T. Valko" wrote:

You probably have a cell in the range that contains the #NAME? error.

You said you have a formula that returns 1 if TRUE. What does it return
if
FALSE?

See if one of these does what you want...

=SUMIF(I5:I56314,"1E100")

=COUNTIF(I5:I56314,1)

--
Biff
Microsoft Excel MVP


"Groybs" wrote in message
...
I have a spreadsheet with 56,314 rows of text labels.

I have a formula in each row off to the right of each text label. The
formula tests for a specific text string contained in the label and
records a
1 if true.

At the top of the sheet above this column of formulas, I want to sum
the
number of 'TRUE' responses, or 1(s).

My formula is: =SUM(I5:I56,314). But this returns a #NAME? error
message. If I limit the SUM formula to not cover beyond cell I9168,
then I
get a correct answer and no error msg.

Is this a limit of Excel's ability to calculate, a limitation imposed
by
my
RAM resources, or something else ?


.