Загальна формула
=SUMPRODUCT(ISNUMBER(MATCH(rng1,("A","B"),0))*ISNUMBER(MATCH(rng2,("X","Y","Z"),0)))
Резюме
Для підрахунку відповідних рядків з кількома критеріями АБО можна використовувати формулу на основі функції SUMPRODUCT. У наведеному прикладі формула в F10 має вигляд:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,("A","B"),0))* ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0)))
Ця формула повертає кількість рядків, де перший стовпець A або B, а другий стовпець X, Y або Z.
Пояснення
Працюючи зсередини, кожен критерій застосовується з окремою конструкцією ISNUMBER + MATCH. Для генерації підрахунку рядків у першому стовпці, де значенням є A або B, ми використовуємо:
ISNUMBER(MATCH(B5:B11,("A","B"),0)
MATCH генерує масив результатів, який виглядає так:
(1;2;#N/A;1;2;1;2)
і ISNUMBER перетворює цей масив у цей масив:
(TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE)
Для генерації підрахунку рядків у стовпці два, де значенням є X, Y або Z, ми використовуємо:
ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0))
Потім MATCH повертає:
(1;2;3;3;#N/A;1;2)
і ISNUMBER перетворює на:
(TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE)
Ці два масиви множаться разом усередині SUMPRODUCT, який автоматично перетворює TRUE FALSE значення на 1 і 0 як частину математичної операції.
Отже, для візуалізації кінцевий результат виводиться так:
=SUMPRODUCT((1;1;0;1;1;1;1)*(1;1;1;1;0;1;1)) =SUMPRODUCT((1;1;0;1;0;1;1)) =5
З посиланнями на клітинки
У наведеному вище прикладі використовуються жорстко закодовані константи масиву, але ви також можете використовувати посилання на комірки:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,E5:E6,0))*ISNUMBER(MATCH(C5:C11,F5:F7,0)))
Більше критеріїв
Цей підхід можна "розширити", щоб врахувати більше критеріїв. Ви можете побачити приклад у цій формулі.
