# Safe SUM() in Google Sheets

When I use sum in Google Sheets, it:

• sums all numeric cells from the range (which is exactly what I want
• silently ignores all empty non-numeric cells from the range (which is usually what I want, though it can sometimes be too smart)
• silently ignores all non-empty non-numeric cells from the range. This is usually not what I want. Whenever I try to sum such a text, I would appreciate a failure. Generally, a failure is preferred to an incorrect result.

How do I get rid of the last property of the SUM function? I have tried count/counta for this, but it looks too hacky and it seems to have some undesired effects in edge cases like all values in the range being "". Is there any better way to do it?

Also, this starts being quite complex. The ideal solution would be as easy as possible. While those metrics do not fully cover maintainability and readability, they provide some objective hints:

1. Minimize the number of occurrences of the range, ideally to just one. The rationale is simple: When changing the range, I would like to change it at as few places as possible.
2. Minimize the count of characters (but not at any cost): Google Sheets is not well designed for long formulas.

this meets all your requested scenarios:

``````=IF(COUNT(G5:G10)<1,,
IF(COUNT(G5:G10)>1,SUM(FILTER(G5:G10,ISNUMBER(G5:G10))),NA()))`````` # UPDATE v2.0:

``````=IF({O7:O}="",,
IF(COUNT(O7:O)=0,NA(),
IF(COUNTA(FILTER(O7:O,O7:O<>""))=COUNT(O7:O),SUM(O7:O),NA())))`````` • I have added it to the table and adjusted the range (B7:B and so on). While it passes all the tests on the screenshot, it fails some further tests I have added later. For example, it fails in cases of just one number. – v6ak Jan 3 '19 at 8:02
• I know it is an edge case, but it still might happen. BTW, sum is defined even for empty list. Moreover, there are some more scenarios where it fails, e.g., “numbers + text”. – v6ak Jan 3 '19 at 8:27
• Thank you. This both passes all the tests and seems structurally correct. Note that I have tried a simpler variant without `IF(COUNT(…)=0,NA()` which also seems OK and is simpler. Is there any reason for this part? My simplified variant is `"if({…}="""", """", if(counta(filter(…, …<>""""))=count(…), sum(…), na()))"`. BTW, thank you for the array construction by curly braces, I did not know that and it seems to be the key missing part. – v6ak Jan 3 '19 at 11:45
• Hmm, the simpler variant seems to be wrongly pasted and I probably cannot edit the comment. It is `if({…}="", "", if(counta(filter(…, …<>""))=count(…), sum(…), na()))`. And I also have added it to the table. – v6ak Jan 3 '19 at 12:08
• This is already handled by the counta(filter(…))=count(…) condition. So, those columns are N/A even without this condition. – v6ak Jan 3 '19 at 15:04

After some research, I have realized that we can just multiply it by 1. The (almost) ultimate and quite simple solution is:

``````sum(arrayFormula(1*…))
``````

It currently passes all my tests except that it does not fail for date/time – it tries to convert the number instead. This seems to be the last limitation. I don't believe this is easy to resolve, as Google Sheets consider this date/time as a kind of number.

• It is quite readable, although it might not be clear why you have decided to go this way over plain `sum(range)`.