Суммесли excel что это
Функция СУММЕСЛИ
Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазон, соответствующие указанному критерию. Предположим, например, что в столбце с числами необходимо просуммировать только значения, превышающие 5. Для этого можно использовать следующую формулу: =СУММЕСЛИ(B2:B25;»> 5″)
Это видео — часть учебного курса Сложение чисел в Excel.
При необходимости условия можно применить к одному диапазону, а просуммировать соответствующие значения из другого диапазона. Например, формула =СУММЕСЛИ(B2:B5; «Иван»; C2:C5) суммирует только те значения из диапазона C2:C5, для которых соответствующие значения из диапазона B2:B5 равны «Иван».
Если необходимо выполнить суммирование ячеек в соответствии с несколькими условиями, используйте функцию СУММЕСЛИМН.
Важно: Функция СУММЕ ЕСЛИ возвращает неправильные результаты, если она используется для совпадения строк длиной более 255 знаков или строки #VALUE!.
Синтаксис
СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])
Аргументы функции СУММЕСЛИ описаны ниже.
Диапазон — обязательный аргумент. Диапазон ячеек, оцениваемых на соответствие условиям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые и текстовые значения игнорируются. Выбранный диапазон может содержать даты в стандартном формате Excel (см. примеры ниже).
Например, условия могут быть выражены как 32, «>32», B5, «3?», «яблок*», «*
Важно: Все текстовые условия и условия с логическими и математическими знаками необходимо заключать в двойные кавычки ( «). Если условием является число, использовать кавычки не требуется.
Примеры использования функции СУММЕСЛИ в Excel с несколькими условиями
Суммировать в программе Excel умеет, наверное, каждый. Но с усовершенствованной версией команды СУММ, которая называется СУММЕСЛИ, существенно расширяются возможности данной операции.
По названию команды можно понять, что она не просто считает сумму, но еще и подчиняется каким-либо логическим условиям.
СУММЕСЛИ и ее синтаксис
Функция СУММЕСЛИ позволяет суммировать ячейки, которые удовлетворяют определенному критерию (заданному условию). Аргументы команды следующие:
Получается, что у функции всего 3 аргумента. Но иногда последний может быть исключен, и тогда команда будет работать только по диапазону и критерию.
Как работает функция СУММЕСЛИ в Excel?
Рассмотрим простейший пример, который наглядно продемонстрирует, как использовать функцию СУММЕСЛИ и насколько удобной она может оказаться при решении определенных задач.
Имеем таблицу, в которой указаны фамилии сотрудников, их пол и зарплата, начисленная за январь-месяц. Если нам нужно просто посчитать общее количество денег, которые требуется выдать работникам, мы используем функцию СУММ, указав диапазоном все заработные платы.
Но как быть, если нам нужно быстро посчитать заработные платы только продавцов? В дело вступает использование функции СУММЕСЛИ.
Получилось 92900. Т.е. функция автоматически проработала список должностей, выбрала из них только продавцов и просуммировала их зарплаты.
Аналогично можно подсчитать зарплаты всех менеджеров, продавцов-кассиров и охранников. Когда табличка небольшая, кажется, что все можно сосчитать и вручную, но при работе со списками, в которых по несколько сотен позиций, целесообразно использовать СУММЕСЛИ.
Функция СУММЕСЛИ в Excel с несколькими условиями
Если к стандартной записи команды СУММЕСЛИ в конце добавляются еще две буквы – МН (СУММЕСЛИМН), значит, подразумевается функция с несколькими условиями. Она применяется в случае, когда нужно задать не один критерий.
Синтаксис с использованием функции по нескольким критериям
Аргументов у СУММЕСЛИМН может быть сколько угодно, но минимум – это 5.
И так далее. В зависимости от количества критериев, число аргументов может увеличиваться в арифметической прогрессии с шагом 2. Т.е. 5, 7, 9.
Пример использования
Предположим, нам нужно подсчитать сумму заработных плат за январь всех продавцов-женщин. У нас есть два условия. Сотрудник должен быть:
Значит, будем применять команду СУММЕСЛИМН.
Итог: все продавцы-женщины в январе получили в сумме 51100 рублей.
СУММЕСЛИ в Excel с динамическим условием
Функции СУММЕСЛИ и СУММЕСЛИМН хороши тем, что они автоматически подстраиваются под изменение условий. Т.е. мы можем изменить данные в ячейках, и суммы будут изменяться вместе с ними. Например, при подсчете заработных плат оказалось, что мы забыли учесть одну сотрудницу, которая работает продавцом. Мы можем добавить еще одну строчку через правую кнопку мыши и команду ВСТАВИТЬ.
У нас появилась дополнительная строчка. Сразу обращаем внимание, что диапазон условий и суммирования автоматически расширился до 15 строки.
Копируем данные сотрудника и вставляем их в общий перечень. Суммы в итоговых ячейках изменились. Функции среагировали на появление в диапазоне еще одного продавца-женщины.
Аналогично можно не только добавлять, но и удалять какие-либо строки (например, при увольнении сотрудника), изменять значения (заменить «январь» на «февраль» и подставить новые заработные платы) и т.п.
В этом руководстве объясняется различие между функциями СУММЕСЛИ (SUMIF) и СУММЕСЛИМН (SUMIFS) с точки зрения их синтаксиса и использования, а также приводятся примеры формул для суммирования значений с несколькими критериями в Excel 2016, 2013, 2010, 2007, 2003 и ниже.
Как известно, Microsoft Excel предоставляет множество функций для выполнения различных расчетов с данными. Мы уже рассмотрели СУММЕСЛИ, которая суммирует числа, соответствующие указанным критериям. Теперь пришло время перейти к расширенной версии этой функции СУММЕСЛИМН, которая позволяет найти сумму по нескольким условиям.
Как работает СУММЕСЛИМН?
При помощи СУММЕСЛИМН можно найти сумму величин, для которых есть много условий. Она появилась впервые в MS Excel 2007, поэтому вы можете использовать ее во всех современных версиях программы.
По сравнению с СУММЕСЛИ, синтаксис СУММЕСЛИМН немного сложнее:
СУММЕСЛИМН(диапазон_суммирования, диапазон_условия1, условие1, [диапазон_условия2, условие2],…)
Первые 3 аргумента являются обязательными, а вот дополнительные диапазоны и связанные с ними условия являются необязательными.
условие1— обязательное первое условие, которое должно быть выполнено. Вы можете предоставить его в виде числа, логического выражения, ссылки, текста или другой функции Excel. Например, вы можете использовать такие критерии, как 10, «> = 10», A1, «яблоко» или СЕГОДНЯ().
Важно! Функция СУММЕСЛИМН работает с логикой «И». Это означает, что число в диапазоне суммирования учитывается, только если оно удовлетворяет всем указанным критериям (все требования соблюдаются для этой ячейки).
1. Порядок аргументов
Аргументы применяются по-разному. В частности, диапазон_сумирования является 1-м параметром в СУММЕСЛИ, но является третьим в СУММЕСЛИМН.
На первый взгляд может показаться, что Microsoft намеренно усложняет процесс обучения для своих пользователей. Однако при ближайшем рассмотрении вы увидите причины этого. Дело в том, что этот диапазон является необязательным в СУММЕСЛИ. Если вы его опустите, то нет никаких проблем, ваша формула будет складывать в диапазоне поиска (первый параметр).
В СУММЕСЛИМН он, напротив, очень важен и обязателен, и поэтому и стоит первым. Вероятно, ребята из Microsoft подумали, что после добавления 10- й или 100- й пары диапазон/критерий кто-то может забыть указать диапазон для суммирования:)
2. Диапазон суммирования и область критериев должны быть одинакового размера
В функции СУММЕСЛИ эти аргументы не обязательно должны иметь одинаковую размерность. Достаточно указать начальную точку. В СУММЕСЛИМН они должны содержать одинаковое количество строк и столбцов.
Выражение =СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F22;I3) вернет сообщение об ошибке #ЗНАЧ!, так как второй параметр поиска (F2:F22) не совпадает по размеру с остальными (E2:E21) и (C2:C21).
Хорошо, хватит стратегии (т.е. теории), давайте перейдем к тактике (к примерам).
Суммирование с множеством условий.
Имеются данные о заказах и продаже шоколада. Подсчитаем итог совершённых продаж по молочному шоколаду. То есть, у нас два требования: должно совпадать наименование товара и в колонке «Выполнен» должно быть указано «Да».
Первым аргументом мы указываем диапазон суммирования E2:E21, а затем попарно – диапазон условия и само условие для него.
В C2:C21 будем искать слово «молочный» с любым его вхождением. То есть, до и после него могут быть еще любые другие символы.
В F2:F21 ищем «Да», то есть отметку о том, что заказ выполнен.
Если ОБА эти требования выполняются, то такой заказ нам подходит, и его стоимость мы учтём.
Как видите, у нас найдено 2 совпадения, в которых был продан молочный шоколад.
Использование операторов сравнения.
Рассчитаем по покупателю «Красный» стоимость заказов, в которых было более 100 единиц товара. Как видим, здесь нужно использовать и текстовый, и числовой критерий.
Критерии можно записать в саму формулу, и выглядеть это будет так:
Но более рационально использовать ссылки, как это и сделано на рисунке:
Примечание. Обратите внимание, что в формулах логические выражения с операторами сравнения всегда должны быть заключены в двойные кавычки («»).
Синтаксис, а также работа с числами, текстом и датами у этой функции точно такие же, как и СУММЕСЛИ. Поэтому рекомендую обратиться к нашему предыдущему материалу о условном суммировании.
А как еще можно решить нашу задачу?
Способ 2. Используем функцию СУММПРОИЗВ.
Разберем подробнее, как работает СУММПРОИЗВ():
Результатом вычисления B2:B21=$I$12 является массив
ИСТИНА означает соответствие кода покупателя условию, т.е. слову Красный. Массив этот можно увидеть, выделив в строке формул B2:B21=$I$12, а затем нажав F9.
И в результате логический массив превратится в массив чисел <0:1:0:0:0:0:0:0:0:0:0:0:1:0:0:0:1:0:0:0>.
Результатом вычисления D2:D21>I13 является массив
ИСТИНА соответствует ограничению «количество больше 100». Здесь мы также применяем двойное отрицание, чтобы преобразовать логические переменные в числа.
Результатом поэлементного умножения этих трех массивов является <0:23210:0:0:0:0:0:0:0:0:0:0:0:0:0:0:15840:0:0:0>. Суммируем эти произведения и получаем 39050.
Способ 3. Формула массива.
И еще один вариант расчета – применим формулу массива. В I14 запишем:
Не забудьте в конце нажать комбинацию клавиш CTRL+SHIFT+ENTER, чтобы обозначить это выражение как формулу массива. Фигурные скобки в начале и в конце программа добавит автоматически. Вновь получим результат 39050.
Способ 4. Автофильтр.
Еще один альтернативный вариант – применение автофильтра. Для этого преобразуйте диапазон данных A1:F21 в «умную» таблицу. Напомню, что для этого в меню «Главная» выберите «Форматировать как таблицу». После этого добавьте в нее строку итогов (вкладка «Конструктор») и установите необходимые фильтры.
Без всяких формул итог по отфильтрованным строкам будет определён.
Как СУММЕСЛИМН работает с датами?
Если вы хотите отобрать и сложить какие-то показатели в определенном временном интервале на основе текущей даты, используйте функцию СЕГОДНЯ() в ваших ограничениях, как это показано ниже.
Следующая формула суммирует числа в столбце D, если соответствующая дата в столбце А попадает в последние 7 дней, включая сегодняшний день (предполагается, что сегодня 7 февраля):
При анализе отчетов и других данных вам часто может потребоваться суммировать данные, соответствующие пустым или непустым клеткам таблицы.
А теперь давайте посмотрим, как вы можете использовать формулу СУММЕСЛИМН с «пустыми» и «непустыми» ячейками для реальных данных.
По покупателю «Красный» рассчитаем количество товара в невыполненных заказах. Для этого в столбце B ищем соответствующее название клиента, а в F – пустую ячейку. Если оба требования совпадают, складываем количество товара из столбца D.
Каждое из этих выражений дает верный результат – 144 единицы в заказе от 4 февраля.
Сумма нескольких условий.
А теперь давайте рассчитаем общую стоимость выполненных заказов по двум товарам.
Если мы просто добавим второй критерий в I3, и вместо I2 используем область I2:I3, то расчет будет неверным, поскольку в C2:C21 будем искать товар, в названии которого есть И «черный», И «молочный» одновременно. Ведь таких просто нет.
Поэтому первый вариант расчета таков:
Просто складываем выполненные заказы сначала по первому, а затем по второму наименованию.
Второй вариант: используем элемент массива критериев и функцию СУММ.
Как видите, результаты получены одинаковые. Выбирайте способ, который будет для вас проще и понятнее.
Excel-plus
Пошаговые инструкции, как использовать функции, формулы и другие инструменты табличного редактора Microsoft Excel
Функция СУММЕСЛИ в MS Excel. Варианты данной функции с описанием и примерами.
Функция СУММЕСЛИ в MS Excel. Варианты данной функции с описанием и примерами.
Функция СУММЕСЛИ Excel.
Ниже приведены варианты функции СУММЕСЛИ Excel. Описания, пошаговый алгоритм использования в зависимости от варианта функции, с примерами использования и иллюстрациями.
СУММЕСЛИ следует использовать в тех случаях, когда Вам необходимо суммировать значения в диапазоне, которые соответствует указанному Вами критерию.
Возможные варианты данной функции:
Отдельная статья описывает функцию СУММЕСЛИ Excel. Точное совпадение. Частичное совпадение. С текстовыми критерий. Содержит описание, примеры и иллюстрации. Статья находиться по этой ссылке: Функция СУММЕСЛИ в MS Excel. Точное совпадение. Частичное совпадение. Инструкция и примеры.
Самый простой и понятный способ вызвать данную функцию, это нажать в правом верхнем углу, рядом с кнопкой ∑, стрелку вниз (Рис. 1).
Появиться список функций. Как правило, это наиболее часто используемые функции. В этом списке уже может быть нужная Вам функция СУММЕСЛИ. В нашем случае ее там нет. Тогда выбираем пункт Другие функции. Он находиться внизу списка (Рис. 2).
Появляется диалоговое окно – Вставка функции. Самое верхнее, это поле для поиска нужной функции. Вводим название функции, выбираем ее в результатах поиска и нажимаем ОК (Рис. 3).
Появляется диалоговое окно — Аргументы функции (Рис. 4).
В нем есть три ключевых поля для заполнения:
Что бы выбрать нужный диапазон нажимаете на стрелочку в конце строки диапазона и выбираете нужные Вам ячейки с помощью мыши. Можно сразу выбрать нужный диапазон, не нажимая на стрелочку, но сначала нужно поставив курсор на нужную строку в диалоговом окне. Можно прописать диапазон вручную. Результат от этого не зависит. После того как все готово, нажимает ОК (Рис. 5).
Далее рассмотрим варианты использования данной функции на конкретных примерах, что бы понять, как они работают.
Напоминаю, что перед тем, как вызывать функцию, нужно выбрать ячейку, в которой должно появиться искомое значение, результат применения функции.
Сумма, если больше, Сумма, если меньше. СУММЕСЛИ.
Знаки, которые используются: >, 500»)
Напротив каждого примера стоит число от 100 до 1300. Нам нужно просуммировать, только значения, которые больше числа 500.
Вызываем функцию, как описано выше и выбираем нужный нам диапазон в строке – Диапазон (Е3;Е27). Это столбец со значениями чисел от 100 до 1300.
Вводим критерий. В строке Критерий, пишем: >500, вручную. Можно скопировать значение критерия с самой таблицы и добавить в ручную только знак >. Кавычки появятся автоматически (Рис. 7).
Нажимаем ОК. Напротив ячейки с словом ИТОГО, появилась сумма чисел больше 500 (Рис. 8)
Сумма, если меньше.
Формула в ячейке, как пример, выглядит вот так:
Сумма, если равно.
Формулы в ячейках, как пример, выглядят вот так:
В столбце D, есть четыре ячейки с Пример 1 (выделенные красным шрифтом). Наша задача суммировать значения, которые стоят только напротив Пример 1. Вызываем функцию СУММЕСЛИ. В Диапазоне выбираем нужный диапазон (столбец D) (D3:D27).
В Критерий, вручную пишем Пример 1 или копируем с таблицы. По этому критерию будет осуществляться поиск нужных ячеек в диапазоне (D3:D27).
В Диапазон_суммирования выбираем диапазон ячеек с значениями (столбец Е), которые будут суммироваться (E3:E27). Суммироваться будут те ячейки, которые будут отвечать нашему критерию и находиться напротив ячеек с значением Пример 1. Нажимаем ОК (Рис. 9).
Так же можно использовать данный вариант функции, когда из списка значений (цифр), нужно суммировать только цифры равные определенному значению. Например, в диапазоне значений (E3:E27), нам нужно суммировать только значения равные числу 100 (выделенные красным шрифтом). В строке Критерий указываем необходимое значений, после знака =. В нашем случае это цифра 100. Диапазон_суммирования не заполняем. (Рис. 10).
Получаем искомую сумму, в нашем случае это число 400.
Сумма, если не равно.
Формулы в ячейках, как пример, выглядят вот так:
Рассмотрим на примере, когда нам нужно суммировать все значений, кроме тех, которые находятся напротив ячейки с Пример 1.
Выбираем в Диапазон ячейки, содержащие наш критерий (D3:D27) – Пример 1
В Критерий прописываем: <> Пример 1. Получаем сумму всех значений (диапазон E3:E27), кроме тех, которые стоят напротив Пример 1 (диапазон (D3:D27)).
В Диапазон_суммирования выбираем ячейки, значения которых нужно суммировать (E3:E27). Нажимаем ОК. Получаем нужную нам сумму. (Рис. 11).
Или нам нужно суммировать только значение не равные числу 100.
Тогда в строке Диапазон указываем нужный нам диапазон для суммирования (E3:E27).
В Критерий пишем <>100.
Диапазон_суммирования не заполняем.
Нажимаем ОК. Получаем сумму всех ячеек, кроме тех, где значение равно числу 100. (Рис. 12).
Сумма если больше или равно, Сумма если меньше или равно. СУММЕСЛИ.
Знаки, которые используются: >=, =500″).
Рассмотрим на примере. Нам нужно посчитать сумму значений в столбце (E3:E27). Но суммировать нужно только те числа, которые равны или больше значения числа 500. По стандартному алгоритму вызываем функцию СУММЕСЛИ.
В строке Диапазон выбираем диапазон ячеек, которые нужно просуммировать (E3:E27).
В Критерий пишем:>=500.
Диапазон_суммирования не заполняем.
Нажимаем ОК. Получаем сумму значений равною числу 500, плюс все, что больше этой цифры в заданном диапазоне (E3:E27) (Рис. 13).
Сумма если меньше или равно
Формула в ячейке, как пример, выглядит вот так:
7 примеров использования формулы СУММЕСЛИ в Excel с несколькими условиями
В таблицах Excel можно не просто находить сумму чисел, но и делать это в зависимости от заранее определённых критериев отбора. Мы рассмотрим, как правильно применить функцию СУММЕСЛИ (Sumif) в таблицах Excel. Начнем с самых простых случаев, как можно использовать при этом знаки подстановки, назначить диапазон суммирования, работать с числами, текстом и датами. Особо остановимся на том, как использовать сразу несколько условий. И, конечно, мы применим новые знания на практике, рассмотрев несложные примеры.
Хорошо, что функция СУММЕСЛИ одинакова во всех версиях MS Excel, с 2016 по 2003 год. Еще одна приятная новость: если вы потратите некоторое время на ее изучение, вам потребуется совсем немного усилий, чтобы понять другие «ЕСЛИ»-функции, такие как СУММЕСЛИМН, СЧЕТЕСЛИ, СЧЕТЕСЛИМН и т.д.
Как пользоваться СУММЕСЛИ в Excel – синтаксис
Её назначение – найти итог значений, которые удовлетворяют определённым требованиям.
Синтаксис функции выглядит следующим образом:
=СУММЕСЛИ(диапазон, критерий, [диапазон_суммирования])
Диапазон – это область, которую мы исследуем на соответствие определённому значению.
Критерий – это значение или шаблон, по которому мы производим отбор чисел для суммирования.
Значение критерия может быть записано прямо в самой формуле. В этом случае не забывайте, что текст нужно обязательно заключать в двойные кавычки.
Также он может быть представлен в виде ссылки на ячейку таблицы, в которой будет указано требуемое ограничение. Безусловно, второй способ является более рациональным, поскольку позволяет гибко менять расчеты, не редактируя выражение.
Итак, если он указан, то расчет идет именно по его данным. Если отсутствует, то складываются значения из той же области, где производился поиск.
Чтобы лучше понять это описание, рассмотрим несколько простых задач. Надеюсь, что они будут понятны не только «продвинутым» пользователям, но и подойдут для «чайников».
Примеры использования функции СУММЕСЛИ в Excel
Сумма если больше чем, меньше, или равно
Начнем с самого простого. Предположим, у нас есть данные о продажах шоколада. Рассчитаем различные варианты продаж.
D2:D21 – это координаты, в которых мы ищем значение.
I2 – ссылка на критерий отбора. Иначе говоря, мы ищем ячейки со значением 144 и складываем их.
Поскольку третий параметр функции не указан, то мы сразу складываем отобранные числа. Область поиска будет одновременно являться и диапазоном суммирования.
Можно указать его прямо в формуле, как это сделано в I13
=СУММЕСЛИ(D2:D21;» 
В I3 запишем выражение:
F2:F21 – это область, в которой мы отбираем подходящие значения.
I2 – здесь записано, что именно отбираем.
E2:E21 – складываем числа, соответствующие найденным совпадениям.
Конечно, можно указать параметр отбора прямо в выражении:
Но мы уже договорились, что так делать не совсем рационально.
Важное замечание. Не забываем, что все текстовые значения необходимо заключать в кавычки.
Подстановочные знаки для частичного совпадения.
При работе с текстовыми данными часто приходится производить поиск по какой-то части слова или фразы.
Вернемся к нашему случаю. Определим, сколько всего было заказов на черный шоколад. В результате, у нас есть 2 подходящих наименования товара. Как учесть их оба? Для этого есть понятие неточного соответствия.
Мы можем производить поиск и подсчет значений, указывая не всё содержимое ячейки, а только её часть. Таким образом мы можем расширить границы поиска, применив знаки подстановки “?”, “*”.
Символ “?” позволяет заменить собой один любой символ.
Символ ”*” позволяет заменить собой не один, а любое количество символов (в том числе ноль).
Эти знаки можно применить в нашем случае двумя способами. Либо прямо вписать их в таблицу –
=СУММЕСЛИ(C2:C21;»*»&I2&»*»;E2:E21)
где * вставлены прямо в выражение и «склеены» с нужным текстом.
Этому требованию соответствует “Нет”.
Подойдет “Зеленый”, “Оранжевый”, “Серебряный”, “Голубой”, “Коричневый”, “Золотой”, “Розовый”.
) нужно поставить перед каждым из них. К примеру, если мы будем искать текст, состоящий из трех звездочек, то можено записать так:
А если текст просто содержит в себе 3 звездочки, то можно наше выражение переписать так:
Точная дата либо диапазон дат.
Если нам нужно найти сумму чисел, соответствующих определённой дате, то проще всего в качестве критерия указать саму эту дату.
Примечание. При этом не забывайте, что формат указанной вами даты должен соответствовать региональным настройкам вашей таблицы!
Обратите внимание, что мы также можем здесь вписать ее прямо в формулу, а можем использовать ссылку.
Рассчитываем итог продаж за сегодняшний день – 04.02.2020г.
Рассчитаем за вчерашний день.
=СУММЕСЛИ(A2:A21;СЕГОДНЯ()-1;E2:E21)
СЕГОДНЯ()-1 как раз и будет «вчера».
Складываем за даты, которые предшествовали 1 февраля.
А если нас интересует временной интервал «от-до»?
Мы можем рассчитать итоги за определённый период времени. Для этого применим маленькую хитрость: разность функций СУММЕСЛИ. Предположим, нам нужна выручка с 1 по 4 февраля включительно. Из продаж после 1 февраля вычитаем все, что реализовано до 4 февраля.
Если критерий указать просто “*”, то мы учитываем для подсчета непустые ячейки, в которых имеется хотя бы одна буква или символ (кроме пустых).
Теперь рассмотрим, как можно находить сумму, соответствующую пустым ячейкам.
Для того, чтобы найти пустые, в которых нет ни букв, ни цифр, в качестве критерия поставьте парные одинарные кавычки ‘’, если значение критерия указано в ячейке, а формула ссылается на неё.
Если же указать на отбор только пустых ячеек в самой формуле, то впишите двойные кавычки.
=СУММЕСЛИ(F2:F21;«»;E2:E21)
Сумма по нескольким условиям.
Функция СУММЕСЛИ может работать только с одним условием, как мы это делали ранее. Но очень часто случается, что нужно найти совокупность данных, удовлетворяющих сразу нескольким требованиям. Сделать это можно как при помощи некоторых хитростей, так и с использованием других функций. Рассмотрим все по порядку.
Вновь вернемся к нашему случаю с заказами. Рассмотрим два условия и найдем, сколько всего сделано заказов черного и молочного шоколада.
1. СУММЕСЛИ + СУММЕСЛИ
Находим сумму заказов по каждому виду товара, а затем просто их складываем. Думаю, с этим вы уже научились работать :).
Это самое простое решение, но не самое универсальное и далеко не единственное.
2. СУММ и СУММЕСЛИ с аргументами массива.
Вышеупомянутое решение очень простое и может выполнить работу быстро, когда критериев немного. Но если вы захотите работать с несколькими, то она станет просто огромной. В этом случае лучшим подходом является использование в качестве аргумента массива критериев. Давайте рассмотрим этот подход.
Вы можете начать с перечисления всех ваших условий, разделенных запятыми, а затем заключить итоговый список, разделенный точкой с запятой, в <фигурные скобки>, который технически называется массивом.
Если вы хотите найти покупки этих двух товаров, то ваши критерии в виде массива будут выглядеть так:
Поскольку здесь использован массив критериев, то результатом вычислений также будет массив, состоящий из двух значений.
А теперь воспользуемся функцией СУММ, которая умеет работать с массивами данных, складывая их содержимое.
=СУММ(СУММЕСЛИ($C$2:$C$21; ;$E$2:$E$21))
Важно, что результаты вычислений в первом и втором случае совпадают.
3. СУММПРОИЗВ и СУММЕСЛИ.
А если вы предпочитаете перечислять критерии в какой-то специально отведенной для этого части таблицы? Можете использовать СУММЕСЛИ в сочетании с функцией СУММПРОИЗВ, которая умножает компоненты в заданных массивах и возвращает сумму этих произведений.
Вот как это будет выглядеть:
в H3 и H4 мы запишем критерии отбора.
Но, конечно, ничто не мешает вам перечислить значения в виде массива критериев:
=СУММПРОИЗВ(СУММЕСЛИ(C2:C21; ;E2:E21))
Результат, возвращаемый в обоих случаях, будет идентичен тому, что вы наблюдаете на скриншоте.
Важное замечание! Обратите внимание, что все перечисленные выше три способа производят расчет по логическому ИЛИ. То есть, нам нужны продажи шоколада, который будет или черным, или молочным.
Почему СУММЕСЛИ у меня не работает?
Этому может быть несколько причин. Иногда ваше выражение не возвращает того, что вы ожидаете, только потому, что тип данных в ячейке или в каком-либо аргументе не подходит для нее. Итак, вот что нужно проверить.
1. «Диапазон данных» и «диапазон суммирования» должны быть указаны ссылками, а не в виде массива.
Первый и третий атрибуты функции всегда должны быть ссылкой на область таблицы, например A1: A10. Если вы попытаетесь передать что-нибудь еще, например, массив <1,2,3>, Excel выдаст сообщение об ошибке.
Правильно: =СУММЕСЛИ(A1:A3, «цвет», C1:C3)
Неверно : =СУММЕСЛИ(<1,2,3>, «цвет», C1:C3)
2. Ошибка при суммировании значений из других листов или рабочих книг
Как и любая другая функция Excel, СУММЕСЛИ может ссылаться на другие листы и рабочие книги, если они в данный момент открыты.
Найдем сумму значений в F2: F9 на листе 1 книги 1, если соответствующие данные записаны в столбце A, и если среди них содержатся «яблоки»:
Однако это перестанет работать, как только Книга1 будет закрыта. Это происходит потому, что области, на которые ссылаются формулы в закрытых книгах, преобразуются в массивы и хранятся в таком виде в текущей книге. А поскольку в аргументах 1 и 3 массивы не допускаются, то формула выдает ошибку #ЗНАЧ!.
3. Чтобы избежать проблем, убедитесь, что диапазоны данных и поиска имеют одинаковый размер.
Как отмечалось в начале этого руководства, в современных версиях Microsoft Excel они не обязательно должны иметь одинаковый размер. Но вот в Excel 2000 и более ранних версиях это может вызвать проблемы. Однако, даже в самых последних версиях Excel 2010 и Excel 2016 сложные выражения, в которых диапазон сложения имеет меньше строк и/или столбцов, чем диапазон поиска, являются капризными. Вот почему рекомендуется всегда иметь их одинакового размера и формы.