Сложение с множественным выбором в EXCEL

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

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

Задача

Найдем строки, в которых в столбце Фрукты значатся Яблоки ИЛИ Апельсины ИЛИ Груши . Затем найдем сумму ящиков на складе в отобранных строках. Критерии (без повторов) будем вводить в ячейки Е5 , Е6 и Е7 (на рисунке ниже выделены желтой заливкой).

Для наглядности, строки в таблице, удовлетворяющие критериям, выделяются Условным форматированием с правилом =ИЛИ($A5=$E$5;$A5=$E$6;$A5=$E$7)

Решение

Общее количество ящиков с наименованиями фруктов Яблоки ИЛИ Апельсины ИЛИ Груши =150. Подсчет можно реализовать множеством формул, приведем только несколько:

• Очевидная формула =

  =СУММЕСЛИ(A5:A16;E5;B5:B16)+ СУММЕСЛИ(A5:A16;E6;B5:B16)+ СУММЕСЛИ(A5:A16;E7;B5:B16)

  представляет сумму трех однокритериальных условий.

• Формула =СУММПРОИЗВ(((A5:A16=E5)+(A5:A16=E6)+(A5:A16=E7))*(B5:B16))
• Формула =БДСУММ(A4:B16;B4;D16:D19) требует предварительного создания таблички с условиями. Заголовок этой таблички должен в точности совпадать с заголовком исходной таблицы, по которому производится отбор строк ( см. статью БДСУММ() ). Размещение условий в разных строках соответствует Условию ИЛИ, см. диапазон D16:E19 .

Альтернативное решение

Данную задачу можно решить с помощью Автофильтра и функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() (см. файл примера лист2)

1. В ячейку В3 введите формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B5:B16) Формула суммирует только те значения в диапазоне B5:B16 , которые отобраны в данный момент Автофильтром (если Автофильтр не применен, то формула суммирует все значения диапазона).

2. Примените Автофильтр к исходной таблице, выделив заголовок и нажав CTRL+SHIFT+L

3. Выберите для столбца Фрукты нужные наименования:

4. Нажмите ОК

Как видим, суммарное количество ящиков для фруктов Яблоки, Апельсины, Груши как и для предыдущего решения =150.

Примечание : О сложении с множественными критериями можно прочитать в этом разделе Сложение с несколькими критериями .