Totalling a colum with errors

Copper Contributor

I am calculating the distances between points but the formula can give an error i.e #VALUE!

How can I ignor this return. I have a cell =IF(B9>1, B9,0) but does not accept an error.

Can you help

2 Replies
To handle the error:

=IFERROR(IF(B9>1, B9,0),"")
Or
=IFERROR(IF(B9>1, B9,0),0)

@WorkTests44 

If, for some reason, you need to see the errors, the AGGREGATE function is capable of totalling the valid numbers whilst ignoring errors:

= AGGREGATE(9, 6, values)

where the syntax is = AGGREGATE(function_num, options, ref1) 

with function_num=9 [sum] and options=6 [ignore errors].