Общая формула
=SUMPRODUCT((rng1=crit1)*ISNA(MATCH(rng2,crit2,0)))
Резюме
Для подсчета с использованием нескольких критериев, в том числе логики НЕ для одной из нескольких вещей, вы можете использовать функцию СУММПРОИЗВ вместе с функциями ПОИСКПОЗ и ISNA.
В показанном примере формула G8 имеет следующий вид:
=SUMPRODUCT((gender=F4)*ISNA(MATCH(group,G4:G5,0)))
Где «пол» - именованный диапазон C4: C12, а «группа» - именованный диапазон D4: D12.
Примечание. MATCH и ISNA позволяют легко масштабировать формулу для обработки большего количества исключений, поскольку вы можете легко расширить диапазон, включив дополнительные значения «НЕ».
Объяснение
Первое выражение в SUMPRODUCTS проверяет значения в столбце C, Gender, на значение в F4, «Male»:
(gender=F4)
Результатом является массив ИСТИННЫХ ЛОЖНЫХ значений, подобных этому:
(ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ)
Где TRUE соответствует "Male".
Второе выражение внутри SUMPRODUCTS проверяет значения в столбце D, Group, на соответствие значениям в G4: G5, «A» и «B». Этот тест обрабатывается с помощью MATCH и ISNA следующим образом:
ISNA(MATCH(group,G4:G5,0))
Функция ПОИСКПОЗ используется для сопоставления каждого значения в названном диапазоне «группа» со значениями в G4: G5, «A» и «B». В случае успешного совпадения ПОИСКПОЗ возвращает число. Если ПОИСКПОЗ не удается, ПОИСКПОЗ возвращает # Н / Д. Результатом является такой массив:
(1; 2; # Н / Д; 1; 2; # Н / Д; 1; 2; # Н / Д)
Поскольку значения # N / A соответствуют «не A или B», ISNA используется для «переворота» массива на:
(ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА)
Теперь ИСТИНА соответствует «не А или Б».
Внутри SUMPRODUCT два результата массива умножаются вместе, что создает единый числовой массив внутри SUMPRODUCT:
SUMPRODUCT((0;0;1;0;0;1;0;0;0))
СУММПРОИЗВ затем возвращает сумму 2, представляющую «2 самца не в группе A или B».
