Maximum number of cells for 'SUM' ?

[Groybs]

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 ?

 

[T. Valko]

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)

 

[ozgrid.com]

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

 

[Max]

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)

 

[Groybs]

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.

 

[Groybs]

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

 

[Groybs]

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.

 

[ozgrid.com]

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


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