Загальна формула
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
Резюме
Для підрахунку клітинок, які містять x або y, ви можете використовувати формулу на основі функції SUMPRODUCT. У наведеному прикладі формула в комірці F5 має вигляд:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
Це рішення для одноклітинної формули, пояснено нижче. Також можна використовувати більш просту формулу на основі допоміжного стовпця, також пояснювану нижче.
Пояснення
Підраховуючи клітинки з "АБО логікою", потрібно бути обережним, щоб не подвоїти рахунок. Наприклад, якщо ви підраховуєте комірки, які містять "abc" або "def", ви не можете просто скласти дві функції COUNTIF, оскільки ви можете подвоїти клітинки, які містять як "abc", так і "def".
Одноклітинний розчин
Для однієї формули ви можете використовувати SUMPRODUCT з ISNUMBER + FIND. Формула в F5:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Ця формула базується на формулі, яка пояснюється тут, яка розміщує текст усередині комірки:
ISNUMBER(FIND("abc",B5:B11)
Якщо йому задано діапазон комірок, цей фрагмент поверне масив значень TRUE / FALSE, по одному значенню для кожної комірки діапазону. Оскільки ми використовуємо це двічі (один раз для "abc" і один раз для "def"), ми отримаємо два масиви.
Далі ми додаємо ці масиви разом (з +), що створює новий одиничний масив чисел. Кожне число в цьому масиві є результатом додавання значень TRUE та FALSE у вихідних двох масивах разом. У наведеному прикладі отриманий масив виглядає так:
(2;0;2;0;1;0;2)
Нам потрібно скласти ці числа, але ми не хочемо подвоювати рахунок. Отже, нам потрібно переконатися, що будь-яке значення, більше нуля, просто враховується один раз. Для цього ми примушуємо всі значення до TRUE або FALSE з "> 0", а потім до 1/0 з подвійним від'ємним (-).
Нарешті, SUMPRODUCT повертає суму всіх значень у масиві.
Розчин допоміжної колони
Завдяки допоміжному стовпчику для перевірки кожної комірки окремо проблема є менш складною. Ми можемо використовувати COUNTIF з двома значеннями (надаються як "константа масиву"). Формула в C5 така:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
COUNTIF поверне масив, що містить два елементи: рахунок для "abc" і рахунок для "def". Щоб запобігти подвійному підрахунку, ми додаємо елементи, а потім примушуємо результат до TRUE / FALSE за допомогою "> 0". Нарешті, ми перетворюємо значення TRUE / FALSE в 1 і 0 з подвійним від’ємником (-).
Кінцевий результат - 1 або 0 для кожної комірки. Щоб отримати підсумок для всіх комірок у діапазоні, просто підсумуйте допоміжний стовпець.
