0

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.

There is a link to document with some test cases: https://docs.google.com/spreadsheets/d/1YTFS1OnUOh_unreO01QTq0GSDzUAczK1d6oo0a26-dU/edit?usp=drivesdk

2

this meets all your requested scenarios:

=IF(COUNT(G5:G10)<1,,
 IF(COUNT(G5:G10)>1,SUM(FILTER(G5:G10,ISNUMBER(G5:G10))),NA()))

x

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())))

x

  • 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 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 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 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 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 at 15:04
0

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.

Advantages:

  • It provides more descriptive error messages than just N/A.
  • It does not require repeating the range definition.
  • It is quite readable, although it might not be clear why you have decided to go this way over plain sum(range).
  • It is quite short.

I have tried to resolve this last limitation (accepting date/time) by comparing A1 with value(A1) or 1*A1 using EXACT(A1, value(A1)) or something similar. This might be the way to go in theory, but virtually any kind of formatting (fixed decimal places, thousands separator, currency name) can break it, even in trivial cases. Also, I haven't succeeded with arrayFormula there.

So, I am answering the question myself. If you have any better solution, you are welcome.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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