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