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

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

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

Задача

Найдем строки, в которых в столбце Фрукты значится Яблоки ИЛИ строки с остатком на складе не менее 12 (ящиков). Затем найдем сумму ящиков на складе в отобранных строках.

Решение

Общее количество ящиков с наименованиями фруктов Яблоки =89. Кроме того, в расчет нужно включить партии с остатком на складе >12 ящиков (но не яблок, т.к. мы их уже учли). Это только одна партия груш с количеством ящиков =98. Подсчет можно реализовать множеством формул, приведем только несколько:

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

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