Формула для функции СУММЕСЛИ с несколькими условиями в Excel
Один из способов использования двух или несколько условий в функции СУММЕСЛИ основан на добавлении или вычитании результатов нескольких функций СУММЕСЛИ. Если два условия касаются одного и того же диапазона значений, данный метод является весьма эффективным. Если же диапазоны разные, то данный способ существенно усложняется, так как необходимо следить за тем, чтобы в итоговых расчетах не учитывать повторно те же самые значения.
Функция СУММЕСЛИ и несколько условий в одном диапазоне Excel
Допустим нам необходимо суммировать числовые значения в границах дат от 23-го и до 29-го июня включительно. Начальные и конечные даты введены в соответствующих ячейках дополнительной таблицы для составления запросов выборки из исходной таблицы. Ниже на рисунке изображены исходные значения и условия отбора значений для суммирования:
Описанная данная техника суммирования по нескольким условиям с помощью функции СУММЕСЛИ основана на вычитании. В данной формуле первая функция СУММЕСЛИ возвращает сумму значений, соответствующих датам раньше конечной даты в ячейке E3 или равной этой дате (в данном случаи =29-е Июня). Оператор «меньше» объединен символом амперсант (&) со ссылкой на ячейку E2 (начальной даты).
Когда формула содержала бы только первую функцию =СУММЕСЛИ(A2:A12;"<="&E3;B2:B12), тогда итоговый результат составил = 5721.
Но нам необходимо получить сумму чисел в границах между 25-ым и 29-ым числом Июня месяца включительно, а не между 20-ым и 29-ым. Для этого необходимо вычесть из первого итогового результата 5721,00 сумму чисел раньше даты 25-го Июня – 3 726,50.
Данного результата мы добиваемся с помощью повторного использования такой же функции СУММЕСЛИ, но с немного измененным вторым аргументом СУММЕСЛИ(A2:A12;"<"&E2;B2:B12).
Теперь проверим разницу вычислений двух частей формулы с помощью вычитания:
Суммирование числовых значений, соответствующих датам раньше конечной даты (29-го Июня и равной ей) с последующим вычитанием от данного результата суммы всех числовых значений, относящихся к датам раньше от начальной (25-го Июня) позволяет получить сумму всех чисел в границах соответствующим датам заданного периода в таблице составления запроса выборки.
Пример функции СУММЕСЛИМН с несколькими условиями в Excel
Если вы пользуетесь версией Excel 2010-го года или более новой, то для данного решения задачи лучше воспользоваться специальной функцией СУММЕСЛИМН для суммирования значений с несколькими условиями. Она вычислит такой же правильный результат, но выполнит его быстрее при меньших затратах системных ресурсов. Кроме того, новая функция более интуитивно понятная, чем выше описанная техника вычитания итоговых сумм. Формула функции СУММЕСЛИМН:
Она выборочно суммирует только те значение ячеек в диапазоне B2:B12, которые выполняют условия определенных в двух парах аргументов. Первое условие "<="&E3 – является идентичным условию первой функции СУМЕСЛИ из предыдущего примера. Второе условие ">="&E2 – ограничивает вычисление исключив все числа до начальной даты.
Пример функции СУММПРОИЗВ для суммы диапазона по нескольким условиям
В старых и в новых версиях Excel, можно воспользоваться функцией СУММПРОИЗВ. Ее эффективность выше чем, техника в первом примере, но уступает как по производительности, так и по быстродействию более новой функции СУММЕСЛИМН. Ее формула выглядит следующим образом:
Как видно на рисунке, мы получили аналогичный результат вычисления. Как работает СУММПРОИЗВ в данной формуле детально описано в статье: Формулы суммирования по нескольким условиям в Excel.
Данные вычисления основаны на умножении логических значений и числовых в столбце B2:B12. Результаты вычислений в первых двух логических выражений возвращают логические значения ИСТИНА=1 и ЛОЖЬ=0. Если хоть один из множителей будет равен нулю, то и результат = 0. Например, как читает данная формула первую строку:
ИСТИНА*ЛОЖЬ*876,50=0
то есть:
1*0*876,50=0
Если же оба логические выражения возвращают значение ИСТИНА, тогда текущая ячейка столбца B с показателем продаж умножается на единицы, а затем суммируются все найденные значения. Для наглядного примера сформируем таблицу как видит формула СУММПРОИЗВ текущую ситуацию:
Несмотря на то что функция СУММПРОИЗВ старая, не такая быстрая и удобная как СУММЕСЛИМН, она будет всегда поддерживаться новыми версиями Excel для возможности открытия файлов старых версий без вычислительных ошибок в формулах. Поэтому стоит разобраться с ее принципами работы.
