Sum a range with errors in Excel

This example will show you how to create an array formula that sums a range with errors.

1. To check for errors, use the functions IF (If and ISERROR (EOSHIBKA).

   =IF(ISEERROR(A1),0,A1)

   =ЕСЛИ(ЕОШИБКА(A1);0;A1)

   Explanation: Function IF (IF) returns 0 if an error is found. If not, the value of the cell.

2. To summarize the range with errors, add a function SUM (SUM) and replace A1 on A1: A7.

   =SUM(IF(ISERROR(A1:A7),0,A1:A7))

   =СУММ(ЕСЛИ(ЕОШИБКА(A1:A7);0;A1:A7))

   

3. Let’s finish with a click Ctrl + Shift + Enter.

   =SUM(IF(ISERROR(A1:A7),0,A1:A7))

   =СУММ(ЕСЛИ(ЕОШИБКА(A1:A7);0;A1:A7))

   

Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. They do not need to be entered by yourself. They will disappear when you start editing the formula.

Explanation:

• Range (array of constants) created with a function IF (IF) is stored in Excel memory, not in worksheet cells.
• The array of constants looks like this: {0;5;4;0;0;1;3}.
• This array of constants is used as an argument to the function SUM (SUM), giving a result of 13.