Обща формула
=SUMPRODUCT((rng1=crit1)*ISNA(MATCH(rng2,crit2,0)))
Обобщение
За да броите с множество критерии, включително логика за НЕ едно от няколко неща, можете да използвате функцията SUMPRODUCT заедно с функциите MATCH и ISNA.
В показания пример формулата в G8 е:
=SUMPRODUCT((gender=F4)*ISNA(MATCH(group,G4:G5,0)))
Където "джендър" е наименуваният диапазон C4: C12, а "групата" е наименуваният диапазон D4: D12.
Забележка: MATCH и ISNA позволяват на формулата да се мащабира лесно, за да обработва повече изключения, тъй като можете лесно да разширите обхвата, за да включите допълнителни стойности "НЕ".
Обяснение
Първият израз вътре в SUMPRODUCTS тества стойности в колона C, Пол, спрямо стойността във F4, "Мъж":
(gender=F4)
Резултатът е масив от TRUE FALSE стойности като този:
(TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE)
Където TRUE съответства на "Мъж".
Вторият израз вътре в SUMPRODUCTS тества стойности в колона D, Group, спрямо стойностите в G4: G5, "A" и "B". Този тест се обработва с MATCH и ISNA по следния начин:
ISNA(MATCH(group,G4:G5,0))
Функцията MATCH се използва за съвпадение на всяка стойност в посочения диапазон "група" спрямо стойности в G4: G5, "A" и "B". Когато мачът успее, MATCH връща число. Когато MATCH не успее, MATCH връща # N / A. Резултатът е масив като този:
(1; 2; # N / A; 1; 2; # N / A; 1; 2; # N / A)
Тъй като # N / A стойности съответстват на "не A или B", ISNA се използва за "обръщане" на масива към:
(FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE)
Сега TRUE съответства на "не A или B".
Вътре в SUMPRODUCT, двата резултата от масива се умножават заедно, което създава един цифров масив вътре в SUMPRODUCT:
SUMPRODUCT((0;0;1;0;0;1;0;0;0))
След това SUMPRODUCT връща сумата 2, представляваща „2 мъже, които не са в група A или B“.
