Мультиусловный подсчет со сложными условиями

Question

У меня есть 4 столбца и много строк. Мне нужна формула для подсчета количества строк, соответствующих следующим критериям:

1. содержит определенный текст в столбце A
2. содержит определенный текст в столбце B
3. содержит значение 0 либо в столбце C, либо в столбце D (если оба значения равны 0 в одной строке, которые считаются одним результатом).

Пример: считать, если столбец A - "2015", столбец B - "Лондон", столбец C ИЛИ столбец D - 0

Answer 1

Вы можете использовать SUMPRODUCT для выполнения условных подсчетов. Хотя формулу немного сложнее понять, чем COUNTIFS , она позволяет вам гораздо более гибко настраивать условия. Вот пример формулы, где таблица в A1:D100.

=SUMPRODUCT((A1:A100="textA")*(B1:B100="textB")*(C1:C100*D1:D100=0))

Разбивая это:
SUMPRODUCT будет суммировать массив значений. Массив может быть определен в функции и не требует специальной записи массива.

Каждый фактор в выражении представляет собой условие. Если условие выполнено, значение коэффициента равно 1. Если оно не выполнено, коэффициент равен 0. В результате каждый продукт в массиве будет равен либо 1, либо 0. Те, которые равны 1, соответствуют строкам, в которых выполняются все условия.

Условия:
(A1:A100="textA") Это верно (1), если значением ячейки в столбце A является текст "textA".

(B1:B100="textB") Это верно (1), если значением ячейки в столбце B является текст "textB".

(C1:C100*D1:D100=0) Здесь используется арифметический трюк для сравнения ИЛИ. Произведение C и D будет 0, только если один из двух (или оба) равен 0.

Сумма всех этих продуктов равна вашему условному количеству.