Обща формула
=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)))
Повече критерии
Този подход може да бъде „мащабиран“, за да се справят с повече критерии. Можете да видите пример в това предизвикателство за формула.
