Загальна формула
=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)
Тут кожне значення TRUE вказує на значення комірки, яке є помилкою. Оскільки метою є підрахунок клітинок, які не містять помилок, ми повертаємо ці результати за допомогою функції NOT:
NOT((FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE))
який повертає:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Зверніть увагу, що кожне значення TRUE тепер відповідає комірці, яка не містить помилки. Цей масив тепер у правильному форматі - значення 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)
Нарешті, SUMPRODUCT підсумовує елементи в цьому масиві і повертає загальну суму, яка в прикладі є числом 3:
=SUMPRODUCT((1;0;1;1;1;0;1;1;1;0)) // returns 7
Функція ISERR
Як і функція ISERROR, функція ISERR повертає TRUE, коли значення є помилкою. Різниця полягає в тому, що ISERR ігнорує # N / A помилок. Якщо ви хочете підрахувати комірки, які не містять помилок, і проігнорувати # N / A помилки, ви можете замінити ISERR на ISERROR:
=SUMPRODUCT(--NOT(ISERR(B5:B14))) // ignore #N/A
Опція SUM
Ви також можете використовувати функцію SUM для підрахунку помилок. Структура формули однакова:
=SUM(--NOT(ISERROR(B5:B14)))
Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter, за винятком Excel 365.
