Totalling a colum with errors

%3CLINGO-SUB%20id%3D%22lingo-sub-1352968%22%20slang%3D%22en-US%22%3ETotalling%20a%20colum%20with%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1352968%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20calculating%20the%20distances%20between%20points%20but%20the%20formula%20can%20give%20an%20error%20i.e%20%23VALUE!%3C%2FP%3E%3CP%3EHow%20can%20I%20ignor%20this%20return.%20I%20have%20a%20cell%20%3DIF(B9%26gt%3B1%2C%20B9%2C0)%20but%20does%20not%20accept%20an%20error.%3C%2FP%3E%3CP%3ECan%20you%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1352968%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1352993%22%20slang%3D%22en-US%22%3ERe%3A%20Totalling%20a%20colum%20with%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1352993%22%20slang%3D%22en-US%22%3ETo%20handle%20the%20error%3A%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIFERROR(IF(B9%26gt%3B1%2C%20B9%2C0)%2C%22%22)%3CBR%20%2F%3EOr%3CBR%20%2F%3E%3DIFERROR(IF(B9%26gt%3B1%2C%20B9%2C0)%2C0)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1353394%22%20slang%3D%22en-US%22%3ERe%3A%20Totalling%20a%20colum%20with%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1353394%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F644371%22%20target%3D%22_blank%22%3E%40WorkTests44%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EIf%2C%20for%20some%20reason%2C%20you%20need%20to%20see%20the%20errors%2C%20the%20%3CSTRONG%3EAGGREGATE%3C%2FSTRONG%3E%20function%20is%20capable%20of%20totalling%20the%20valid%20numbers%20whilst%20ignoring%20errors%3A%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20AGGREGATE(9%2C%206%2C%20values)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ewhere%20the%20syntax%20is%20%3CSTRONG%3E%3D%20AGGREGATE(function_num%2C%20options%2C%20ref1)%3C%2FSTRONG%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ewith%20%3CEM%3E%3CSTRONG%3Efunction_num%3D%3C%2FSTRONG%3E%3C%2FEM%3E%3CSTRONG%3E9%3C%2FSTRONG%3E%20%5B%3CEM%3Esum%3C%2FEM%3E%5D%20and%20%3CEM%3E%3CSTRONG%3Eoptions%3D%3C%2FSTRONG%3E%3C%2FEM%3E%3CSTRONG%3E6%3C%2FSTRONG%3E%20%5B%3CEM%3Eignore%20errors%3C%2FEM%3E%5D.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted
To handle the error:

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

@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].