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 ?

Answer: Maximum number of cells for 'SUM' ?
 

The #NAME? error message you are receiving is likely due to the fact that you are using a comma instead of a colon to specify the range of cells you want to sum.

To sum the range of cells from I5 to I56314, you should use the following formula: =SUM(I5:I56314).

1. Excel has a maximum number of cells that it can handle, which is determined by the version of Excel you are using and the amount of memory available on your computer. However, this limit is typically in the millions of cells, so it is unlikely that you are hitting this limit with your spreadsheet.
2. It is possible that your computer's memory resources are being strained by the large number of cells and formulas in your spreadsheet, which could be causing the error message you are seeing. You may want to try closing other programs and freeing up memory on your computer to see if that helps.
3. Additionally, you could try breaking your spreadsheet up into smaller sections to make it more manageable.

Maximum number of cells for 'SUM' ?
 

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 ?

Maximum number of cells for 'SUM' ?
 

#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 ?

Maximum number of cells for 'SUM' ?
 

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 ?

Maximum number of cells for 'SUM' ?
 

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 ?

Maximum number of cells for 'SUM' ?
 

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 ?

Maximum number of cells for 'SUM' ?
 

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 ?


.

Maximum number of cells for 'SUM' ?
 

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 ?


.