Загальна формула
=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".
