Підрахувати клітинки, які містять x або y у діапазоні в Excel

Щоб підрахувати клітинки, які містять те чи інше значення, деякі користувачі Excel можуть додати дві функції COUNTIF разом, щоб впоратися з цим, однак цей метод підраховує клітинки з логікою AND (містять як x, так і y) замість логічного OR (містять x або y). Щоб уникнути подвійного підрахунку критеріїв, у цьому посібнику буде показано, як детально застосувати формулу на основі функції SUMPRODUCT для підрахунку кількості клітинок у певному діапазоні, які містять x або y у Excel.

Як підрахувати клітинки, які містять x або y у діапазоні в Excel

Як показано на скріншоті нижче, існує діапазон даних B3: B9, щоб підрахувати кількість клітинок у цьому діапазоні, що містить або "KTE", або "KTO", будь ласка, застосуйте формулу нижче, щоб це зробити.

Загальна формула

=SUMPRODUCT(--((ISNUMBER(FIND("criteria1",rng)) + ISNUMBER(FIND("criteria2",rng)))>0))

Аргументи

Як користуватися цією формулою?

1. Виберіть порожню комірку для виведення результату.

2. Введіть формулу нижче і натисніть на Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат.

=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>0))

Як працюють ці формули?

=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>0))

• 1. FIND(D3,B3:B9): Функція FIND перевіряє, чи існує значення “KTE” у D3 у зазначеному діапазоні (B3: B9), і повертає масив: {1,#VALUE!,#VALUE!,#VALUE!,#VALUE!, 1 ,#ЦІННІСТЬ!}.
  У цьому масиві два одиниці 1 означають, що перша та друга до останньої клітинки у списку B3: B9, що містять значення “KTE”, #VALUE! S представляють значення “KTE”, якого немає в інших клітинках.
• 2. ISNUMBER{1,#VALUE!, #VALUE!, #VALUE!, #VALUE!,1, #VALUE!}: Функція ISNUMBER повертає TURE, якщо номер зустрічі в масиві, і повертає FALSE, якщо помилки зустрічі, і, нарешті, повертає новий масив як {TURE; FALSE; FALSE; FALSE; FALSE; TURE; FALSE}.
• 3. FIND(D4,B3:B9): Ця функція FIND також перевіряє, чи існує значення “KTO” у D4 у зазначеному діапазоні (B3: B9), і повертає масив: {#VALUE!, 1,#VALUE!,#VALUE !, #VALUE !, #ЦІННІСТЬ!,#ЦІННІСТЬ!}.
• 4. ISNUMBER{#VALUE!,1,#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!}: Функція ISNUMBER повертає TURE, якщо номер зустрічі в масиві, і повертає FALSE, якщо помилки зустрічі, і, нарешті, повертає новий масив як {FALSE; TURE; FALSE; FALSE; FALSE; FALSE; FALSE}.
• 5. {TURE;FALSE;FALSE;FALSE;FALSE;TURE;FALSE} + {FALSE;TURE;FALSE;FALSE;FALSE;FALSE;FALSE}: Тут два масиви повертають результат як {1; 1; 0; 0; 0; 1; 0}.
• 6. {1;1;0;0;0;1;0}>0: Тут порівнюється, чи кожне число в масиві більше 0, і повертає результат як {TURE; TRUE; FALSE; FALSE; FALSE; TURE; FALSE}.
• 7. --({TURE;TRUE;FALSE;FALSE;FALSE;TURE;FALSE}): Ці два знаки мінус перетворюють “TRUE” на 1 та “FALSE” на 0. Тут ви отримаєте новий масив як {1; 1; 0; 0; 0; 1; 0}.
• 8. =SUMPRODUCT({1;1;0;0;0;1;0}): Функція SUMPRODUCT підсумовує всі числа в масиві і повертає кінцевий результат як 3 у цьому випадку.

Супутні функції

Пов’язані формули