Safe sum testing
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

View only
 
 
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
1
noteOKrange occurrencessize (for 4-character range definition)
just numbers
number + text
numbers + text
numbers + num with whitespace
zero and text
numbers with formula
numbers + empty string formula
numbers + non-empty string formula
one number
one text field
num with witespace
just zero
one number and zero
N/A
number with N/A
numbers with N/A
blank and numbers
blank and number
empty + empty formula
empty formula + empty
emptydatedates
date and number
number and date
timetimes
time and number
number and time
rounding 3P
rounding 2P
rounding 1P
rounding 0P
2
desired result5100#N/A#N/A#N/A#N/A500602#N/A500#N/A#N/A0500#N/A#N/A#N/A15006000 or empty
0 or empty
0 or empty
#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A12.010112.010112.010112.0101
3
sum(…)0195100100110041000500602500500000500#N/A#N/A#N/A15006000002018-1-52132-5-122019-7-22436681:314:031:31456.063194412.01012.0112.012.0
4
IF({…}="",, IF(COUNT(…)=0,NA(), IF(COUNTA(FILTER(…, …<>""))=COUNT(…), SUM(…), NA())))
061035100#N/A#N/A#N/A#N/A500602#N/A500#N/A#N/A0500#N/A#N/A#N/A2018-1-52132-5-122019-7-22436681:314:031:31456.063194412.01012.0112.012.0
5
IF(COUNT(…)<1,, IF(COUNT(…)>1,SUM(FILTER(…,ISNUMBER(…))),NA()))04755100#N/A11004100#N/A500602#N/A#N/A#N/A500#N/A15001500#N/A#N/A2132-5-122019-7-2243668#N/A4:031:31456.063194412.01012.0112.012.0
6
if(counta(…)=count(…), sum(…), na())03455100#N/A#N/A#N/A#N/A500#N/A#N/A500#N/A#N/A0500#N/A#N/A#N/A1500600#N/A#N/A02018-1-52132-5-122019-7-22436681:314:031:31456.063194412.01012.0112.012.0
7
if({…}="", "", if(counta(filter(…, …<>""))=count(…), sum(…), na()))05825100#N/A#N/A#N/A#N/A500602#N/A500#N/A#N/A0500#N/A#N/A#N/A2018-1-52132-5-122019-7-22436681:314:031:31456.063194412.01012.0112.012.0
8
if(countifs(…, "<>")=0, "", if(counta(filter(…, …<>""))=count(…), sum(…), na()))05955100#N/A#N/A#N/A#N/A500602#N/A500#N/A#N/A0500#N/A#N/A#N/A1500600#N/A#N/A2018-1-52132-5-122019-7-22436681:314:031:31456.063194412.01012.0112.012.0
9
sum(arrayformula(value(…)))01305100#VALUE!#VALUE!#VALUE!#VALUE!500602#VALUE!500#VALUE!#VALUE!0500#N/A#N/A#N/A1500600000431058487043668436680.063194444440.16875456.0631944456.063194412.0112.011212
10
sum(arrayformula(1*…))0.51255100#VALUE!#VALUE!#VALUE!#VALUE!500602#VALUE!500#VALUE!#VALUE!0500#N/A#N/A#N/A15006000002018-1-52132-5-122019-7-22436681:314:031:31456.063194412.010112.010112.010112.0101
11
if(countifs(…, "<>")=0, "", sum(arrayFormula(1*…)))0.62575100#VALUE!#VALUE!#VALUE!#VALUE!500602#VALUE!500#VALUE!#VALUE!0500#N/A#N/A#N/A1500600002018-1-52132-5-122019-7-22436681:314:031:31456.063194412.010112.010112.010112.0101
12
if(countifs(…, "<>")=0, "", sum(arrayFormula(value(…))))02625100#VALUE!#VALUE!#VALUE!#VALUE!500602#VALUE!500#VALUE!#VALUE!0500#N/A#N/A#N/A150060000431058487043668436680.063194444440.16875456.0631944456.063194412.0112.011212
13
sum(arrayFormula(if(or(…="", exact(…, value(…))), …, na())))04725100#VALUE!#VALUE!#VALUE!#VALUE!500602#VALUE!500#VALUE!#VALUE!0500#N/A#N/A#N/A15006000002018-1-52132-5-122019-7-22436681:314:031:31456.0631944#N/A12.01#N/A#N/A
14
if(counta(filter(…, …<>""))=count(…), sum(…), NA())04635100#N/A#N/A#N/A#N/A500602#N/A500#N/A#N/A0500#N/A#N/A#N/A1500600#N/A#N/A#N/A2018-1-52132-5-122019-7-22436681:314:031:31456.063194412.01012.0112.012.0
15
numbers to sum50001005001000200546500500ddfd5 0000500#N/A#N/A#N/A2018-1-52018-1-52018-1-55631:311:311:314561.0001.001.01.0
16
100fdz6004000fghj10056dd05005005006002014-5-65632018-1-52:324561:3110.00010.0010.010.0
17
dslK5 000200100010001.0101.011.01.0
Loading...
Main menu