This example will show you how to create an array formula that counts the number of errors in a range.
- We use functions IF (If and ISERROR (ISERROR) for error checking:
=IF(ISERROR(A1),1,"")=ЕСЛИ(ЕОШИБКА(A1);1;"")Explanation: Function IF (IF) returns 1 if an error is encountered. If not, an empty string.
- To calculate errors, add a function COUNT (COUNT) and replace A1 of range A1: C3:
=COUNTIF(IF(ISERROR(A1:C3),1,""))=СЧЁТ(ЕСЛИ(ЕОШИБКА(A1:C3);1;"")) - Let’s finish with a click Ctrl + Shift + Enter.
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: {1;””;1;””;””;””;””;””;1}.
- This array of constants is used as an argument to the function COUNT (COUNT), giving a result of 3.
- To count specific errors, use the function COUNTIF (COUNTIF). For example, to count the number of cells containing an error # DIV / 0! (#DIV/0!), use the formula:
=COUNTIF(A3:C3,"#DIV/0!")=СЧЁТЕСЛИ(A3:C3;"#ДЕЛ/0!")