Can you post the formula that gives you a #VALUE error....and indicate which cells might contain text?
In general you could probably use a formula like
=IF(ISERR(Your_formula),"",Your_formula)
....although you might be able to simplify that if only certain cells can contain text.
Alternatively you can sum C1:C10, while ignoring errors with
=SUM(SUMIF(C1:C10,{">0","<0"}))