Ignore Errors (or Text)

John Walkenbach, Excel 2002 Formulas, http://j-walk.com/ss/books/bookxl21.htm

Here is a way to ignore errors in a range. Let's assume you want to add all numbers in range A1:A10 Enter the below formula into a cell as an array formula (Push Ctrl, Shift and Enter simultaneously)

{=SUM(IF(ISERROR(A1:A10),"",A1:A10))}

This will ignore errors and text.

To ignore text only, use {=SUM(IF(ISTEXT(A1:A10),"",A1:A10))}

You can make your own combinations such as {=MAX(IF(ISERROR(A1:A10),"",A1:A10))} or {=SMALL(IF(ISTEXT(A1:A10),"",A1:A10),2)}