Как подсчитать количество ячеек с ошибками в диапазоне Excel

В этом руководстве показано, как подсчитать количество ячеек, содержащих ошибки любого типа (например, #Н/Д, #ЗНАЧ! или #ДЕЛ/0!), в заданном диапазоне Excel.

Подсчёт ячеек с ошибками с помощью функции СУММПРОИЗВ
Подсчёт ячеек с ошибками с помощью функции СУММ

Подсчёт ячеек с ошибками с помощью функции СУММПРОИЗВ

Как показано на снимке экрана ниже, чтобы подсчитать количество ячеек с ошибками в диапазоне B3:B9, используйте функцию СУММПРОИЗВ вместе с функцией ISERR.

Универсальная формула

=SUMPRODUCT(--ISERROR(range))

Аргументы

Как использовать эту формулу?

1. Выберите пустую ячейку, например D3.

2. Введите в неё приведённую ниже формулу и нажмите Enter, чтобы получить результат.

=SUMPRODUCT(--ISERROR(B3:B9))

Как работают эти формулы?

=SUMPRODUCT(--ISERROR(B3:B9))

• 1. ISERROR(B3:B9): Функция ISERROR возвращает ИСТИНА, если ячейка в диапазоне B3:B9 содержит ошибку, и ЛОЖЬ — в противном случае. В результате получается массив {ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ}, указывающий на наличие ошибок только во второй и пятой ячейках диапазона.
• 2. --{FALSE;TURE;FALSE;FALSE;TURE;FALSE;FALSE}: Эти два знака «минус» преобразуют значение «ИСТИНА» в 1, а «ЛОЖЬ» — в 0, в результате чего вы получите новый массив: {0;1;0;0;1;0;0}.
• 3. SUMPRODUCT{0;1;0;0;1;0;0}Функция СУММПРОИЗВ (SUMPRODUCT) суммирует все числа в массиве и возвращает итоговый результат — 2.

Подсчёт ячеек с ошибками с помощью функции СУММ

Функция СУММ также позволяет суммировать ячейки с ошибками в заданном диапазоне. Для этого выполните следующие действия.

Универсальная формула

{=SUM(--ISERROR(range))}

Аргументы

Примечание:Эту формулу нужно вводить как формулу массива. Если после ввода вокруг неё автоматически появятся фигурные скобки — значит, формула массива создана успешно.

Как использовать эту формулу?

1. Выберите пустую ячейку, например D3.

2. Введите в неё приведённую ниже формулу, а затем одновременно нажмите клавиши Ctrl+Shift+Enter, чтобы получить результат.

=SUM(--ISERROR(B3:B9))

Как работают эти формулы?

=SUM(--ISERROR(B3:B9))

• 1. ISERROR(B3:B9): Функция ISERROR возвращает значение ИСТИНА, если ячейка в диапазоне B3:B9 содержит ошибку, и ЛОЖЬ — в противном случае. В итоге она возвращает массив {ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ}, что означает наличие ошибок только во второй и пятой ячейках диапазона.
• 2. --{FALSE;TURE;FALSE;FALSE;TURE;FALSE;FALSE}: Эти два знака «минус» преобразуют «ИСТИНА» в 1, а «ЛОЖЬ» — в 0. В результате вы получите новый массив: {0;1;0;0;1;0;0}.
• 3. SUM{0;1;0;0;1;0;0}Функция СУММ (SUM) суммирует все числа в массиве и возвращает итоговый результат 2.

Связанные функции

Связанные формулы