1

I am trying to sum a column of 50938 values in Excel 2007 but keep receiving a "#VALUE!" error. The cells in the column do contain formulas. I also tried to sum the column after copying and pasting values but received the same error.

  • There is a limit to the size of the numbers themselves. You may be exceeding the maximum number size if these are large numbers. Do you know how large the result will be or a rough average value for the cells? – GuitarPicker Apr 27 '15 at 13:21
  • The average value for these cells is about 20. – user441656 Apr 27 '15 at 13:25
  • 1
    Welcome to Super User! More details are needed. Please tell us what you have researched and attempted to resolve this. You may want to take a look at Excel specifications and limits. Also, take a minute to read How to Ask to improve your question. – CharlieRB Apr 27 '15 at 13:32
  • 1
    Have you checked to make sure that none of the formulas evaluate to an error? An easy way to do this is to add an autofilter and then filter for items containing #. – Engineer Toast Apr 27 '15 at 13:40
  • Make sure you don't have any spaces in the column. Excel won't SUM() if you do. – skub Apr 27 '15 at 13:43
1

With regard to limits on the SUM() function, I filled ALL of column A with the value 20 and =sum(A:A) calculated properly.

If there are any errors in column A or text values, then the array formula:

=SUM(IF(ISNUMBER(A:A),A:A))

should work.

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.