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:
- 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.
- 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
Best Answer
this meets all your requested scenarios:
UPDATE v2.0: