If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 ? |
#2
|
|||
|
|||
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 ? |
#3
|
|||
|
|||
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 ? |
#4
|
|||
|
|||
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 ? |
#5
|
|||
|
|||
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 ? |
#6
|
|||
|
|||
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 ? |
#7
|
|||
|
|||
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 ? . |
#8
|
|||
|
|||
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 ? . |
Thread Tools | |
Display Modes | |
|
|