Общая формула
=SUMPRODUCT(--NOT(ISERROR(range)))
Резюме
Чтобы подсчитать количество ячеек, которые не содержат ошибок, вы можете использовать функции ISERROR и NOT, заключенные в функцию SUMPRODUCT. В показанном примере формула E5 выглядит так:
=SUMPRODUCT(--NOT(ISERROR(B5:B14)))
Объяснение
В этом примере цель состоит в том, чтобы подсчитать количество ячеек в диапазоне, которые не содержат ошибок.
Работая изнутри, мы сначала используем функцию ISERROR для всего диапазона:
ISERROR(B5:B14) // check all 10 cells
Поскольку в диапазоне B5: B14 десять ячеек, ISERROR возвращает массив с десятью результатами, подобными этому:
(FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Здесь каждое значение ИСТИНА указывает значение ячейки, которое является ошибкой. Поскольку цель состоит в подсчете ячеек, которые не содержат ошибок, мы обращаем эти результаты с помощью функции НЕ:
NOT((FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE))
который возвращает:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Обратите внимание, что каждое значение ИСТИНА теперь соответствует ячейке, которая не содержит ошибки. Этот массив теперь имеет правильный формат: значения TRUE указывают на ячейки без ошибок, значения FALSE указывают на ячейки с ошибками.
Поскольку SUMPRODUCT работает только с числовыми данными, следующим шагом будет преобразование значений TRUE и FALSE в их числовые эквиваленты, 1 и 0. Мы делаем это с двойным отрицательным знаком (-):
--(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Результирующий массив выглядит так:
(1;0;1;1;1;0;1;1;1;0)
Наконец, СУММПРОИЗВ суммирует элементы в этом массиве и возвращает итог, который в примере равен числу 3:
=SUMPRODUCT((1;0;1;1;1;0;1;1;1;0)) // returns 7
Функция ISERR
Как и функция ISERROR, функция ISERR возвращает TRUE, если значение является ошибкой. Разница в том, что ISERR игнорирует ошибки # N / A. Если вы хотите подсчитывать ячейки, которые не содержат ошибок, и игнорировать ошибки # N / A, вы можете заменить ISERROR на ISERROR:
=SUMPRODUCT(--NOT(ISERR(B5:B14))) // ignore #N/A
СУММ вариант
Вы также можете использовать функцию СУММ для подсчета ошибок. Структура формулы такая же:
=SUM(--NOT(ISERROR(B5:B14)))
Примечание: это формула массива, и ее необходимо вводить с помощью Ctrl + Shift + Enter, кроме Excel 365.
