I would like to make the sum of a column which might also contain cells which are not parseable as a number (strings, empty, etc). These cells should just be ignored.

I've tried:


but it doesn't work because it also seems to exclude cells which actually contain valid numbers.

Is there a better way of performing a summation, excluding none parseable values?

  • 3
    =sum(A1:A500). Sum will ignore all cells that are not parseable as number. – JPV Jan 13 '15 at 21:40
  • @JPV: it will not ignore cells with @VALUE! . but with the "<>0" this is fixed. – clamp Jan 14 '15 at 17:35

Let me add my comment as an answer... :-)

You should be okay just using =SUM(), because sum() simply ignores non-numeric values.

  • actually my problem is that some cells contain an error resulting from another bad calculation. so they show #VALUE! but this can be fixed by using SUMIF instead of SUM – clamp Jan 14 '15 at 17:34
  • 1
    I usually wrap the other calculation in an IFERROR() so that errors are not shown. Then SUM() should work. – JPV Jan 14 '15 at 21:24

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.