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

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

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

Задача

Найдем Количество ящиков с фруктом Яблоки И с ограничением по остатку на складе >=4 И <=90.

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

Решение

Подсчет можно реализовать несколькими формулами:

  • =СУММЕСЛИМН(B2:B13;A2:A13;D2;B2:B13;">="&E2;B2:B13;"<="&F2) 
  • =СУММПРОИЗВ(--(A2:A13=D2);--(B2:B13>=E2);(B2:B13)*(B2:B13<=F2))
  • =СУММ((A2:A13=D2)*(B2:B13<=F2)*(B2:B13>=E2)*(B2:B13))формула массива;
  • =СУММ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2)*(B2:B13<=F2);B2:B13))формула массива;
  • =БДСУММ(A1:B13;B1;D14:F15) – в диапазоне D14:F15 содержится диапазон с критериями.

При задании критериев для текстовых значений можно воспользоваться подстановочными знаками *, ?. Например, когда в столбце фрукты представлены различные варианты партий с яблоками: яблоки свежие; импортные яблоки; испанские яблоки Джонатан и просто, яблоки, то можно в качестве критерия задать ="*яблоки*", что позволит выбрать все значения со словом яблоки. Или, если в качестве критерия задать ="яблоки*", то это позволит выбрать все значения начинающиеся со слова яблоки (яблоки свежие; яблоки).
Разбор подобных критериев с подстановкой приведен в статье Сложение чисел с единственным критерием. В том же примере приведен критерий с учетом РЕгиСТра.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (1 оценка)

Комментарии

Евгений1985

Как посчитать количество уникальных фруктов (не зависит от вида), которых на складе больше 20 ящиков + добавить еще какой-нибудь критерий (например, дата поставки), т.е. подсчет уникальных с несколькими критериями в других столбцах?

Creator

Про подсчет уникальных текстовых значений изложено в статье Подсчет Уникальных ТЕКСТовых значений в MS EXCEL

Честно говоря мне трудно понять, что значит "уникальных фруктов (не зависит от вида)". Можете прислать файл на creator@excel2.ru

Яндекс.Метрика