Сложение с несколькими критериями в 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 содержится диапазон с критериями.

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