If you need to do mathematical calculations on cells that may contain numbers or text, then you can use the IFERROR function to ignore the text.

Many of Excel’s functions (eg SUM and AVERAGE) ignore text within their range.

However Excel will return the #VALUE! error if you try to add, subtract, multiply or divide by a cell containing text.

To get around the problem you can use the IFERROR function to substitute a zero for the text. If A1 and B1 may contain text the following formula will add up the values in them even if one has a text entry.

=IFERROR(A1*1,0)+IFERROR(B1*1,0)

Multiplying by 1 within the IFERROR function will not affect the value, but it will return the #VALUE! error if a text entry is present. Multiplying by 1 will also convert a text number into a value. The IFERROR function handles all of Excel’s error messages and in this case, returns a zero if an error is encountered. If no error is encountered the value from A1 or B1 is used.

The IFERROR function is only available in Excel 2007 and later versions. In Excel 2003 and earlier versions the solution is a slightly longer formula

=IF(ISERROR(A1*1),0,A1)+IF(ISERROR(B1*1),0,B1)