Сложение с несколькими критериями в EXCEL (Часть 4.Два Условия (ИЛИ и И))

Произведем подсчет строк таблицы, удовлетворяющих сразу трем критериям, которые образуют Условие ИЛИ и Условие И, затем просуммируем числовые значения, этих строк. Например, в таблице с перечнем Фруктов и их количеством на складе, отберем строки, в которых в столбце Фрукты значится Персики ИЛИ Яблоки, причем с остатком на складе не менее 10 (ящиков), затем просуммируем ящики в этих строках.

В качестве исходной таблицы возьмем таблицу с двумя столбцами: текстовым « Фрукты » и числовым « Количество на складе » (См. файл примера ).

Задача

Найдем число партий товара с двумя наименованиями фруктов, например, персики ИЛИ яблоки, с одновременным ограничением по остатку на складе >=10 . Затем просуммируем ящики у отобранных позиций.

Решение

Для наглядности, строки в таблице, удовлетворяющие критериям, выделяются Условным форматированием с правилом =ИЛИ(И($A2=$D$2;$B2>=$F$2);И($A2=$E$2;$B2>=$F$2))

Сложение можно реализовать множеством формул (результат - 77 ящиков), приведем несколько:

• Формула =СУММЕСЛИМН(B2:B13;A2:A13;D2;B2:B13;">="&F2)+СУММЕСЛИМН(B2:B13;A2:A13;E2;B2:B13;">="&F2) подсчитывает сначала количество ящиков у партий Яблоки с остатком >=10, затем количество ящиков у партий Персики , также с остатком >=10. В случае, если пользователь введет в ячейках D2 и E2 одинаковые наименования фруктов, формула вернет удвоенный результат. Формула учитывающая этот случай приведена в файле примера и на рисунке выше.
• Формула = СУММПРОИЗВ(((A2:A13=D2)+(A2:A13=E2))*(B2:B13>=F2)*(B2:B13)) Знак умножить (*) соответствует Условию И, а знак +  соответствует Условию ИЛИ. Внимание! При совпадении условий, например, персики ИЛИ персики, формула также дает удвоенный результат. Необходимо, либо включить проверку, либо запретить ввод одинаковых условий.
• Для понимания работы формулы массива = СУММ(((A2:A13=D2)+(A2:A13=E2))*(B2:B13>=F2)*(B2:B13)) не забывайте выделять интересующие части формулы в Строке формул и нажимать клавишу F9 . Внимание! При совпадении условий, например, персики ИЛИ персики, формула также дает удвоенный результат.
• Формула массива = СУММ(ЕСЛИ(((A2:A13=D2)+(A2:A13=E2))*(B2:B13>=F2);B2:B13)) представляет еще один вариант многокритериального подсчета значений. Формула дает правильный результат, даже при совпадении условий.
• Формула =БДСУММ(A1:B13;B1;D14:E16) требует предварительного создания таблицы с условиями. Заголовки этой таблицы должны в точности совпадать с заголовками исходной таблицы. Размещение условий в разных строках соответствует Условию ИЛИ, в одной строке – Условию И.