Общая формула
=SUMPRODUCT(ISNUMBER(MATCH(rng1,("A","B"),0))*ISNUMBER(MATCH(rng2,("X","Y","Z"),0)))
Резюме
Чтобы подсчитать совпадающие строки с несколькими критериями ИЛИ, вы можете использовать формулу, основанную на функции СУММПРОИЗВ. В показанном примере формула в 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)))
Больше критериев
Этот подход можно «расширить» для обработки большего количества критериев. Вы можете увидеть пример в этой формуле задачи.
