Функция SUMIF в Excel

Функция SUMIF (СУММЕСЛИ) в Excel возвращает сумму ячеек, удовлетворяющих одному условию. Критерии могут применяться к датам, числам и тексту. Функция СУММЕСЛИ поддерживает логические операторы (>,<,<>,=) и подстановочные знаки (*,?) для частичного совпадения.

Что делает функция SUMIF в Excel?

Функция SUMIF в Excel суммирует ячейки в диапазоне, соответствующем критериям.

Возвращаемое значение

Сумма совпадающих ячеек.

Аргументы

• range — диапазон, к которому применяются критерии.
• criteria — Критерии для применения.
• sum_range — [необязательный] диапазон для суммирования. Если этот параметр опущен, ячейки в диапазоне суммируются.

Синтаксис

=SUMIF(range, criteria, [sum_range])

Примечания по использованию

Функция SUMIF суммирует ячейки в диапазоне, которые удовлетворяют одному условию, называемому критерием. Функция SUMIF — это распространенная, широко используемая функция в Excel, которую можно использовать для суммирования ячеек на основе дат, текстовых значений и чисел. Обратите внимание, что SUMIF может применять только одно условие.

Синтаксис

Общий синтаксис для SUMIF выглядит следующим образом:

=SUMIF(range,criteria,[sum_range])

Функция SUMIF принимает три аргумента. Первый аргумент, range , представляет собой диапазон ячеек, к которым применяются критерии. Второй аргумент, критерии, — это применяемые критерии вместе с любыми логическими операторами. Последний аргумент, sum_range, представляет собой диапазон, который необходимо суммировать.

Обратите внимание, что sum_range является необязательным. Если диапазон_суммы не указан, функция СУММЕСЛИ будет суммировать ячейки в первом аргументе, диапазоне.

Применение критериев

Функция СУММЕСЛИ поддерживает логические операторы (>,<,<>,=) и подстановочные знаки (*,?) для частичного совпадения. Сложная часть использования функции — это синтаксис, необходимый для применения критериев.

Это связано с тем, что SUMIF находится в группе из восьми функций, которые разбивают логические критерии на две части: диапазон и критерии. Из-за такой конструкции операторы необходимо заключать в двойные кавычки («»). В таблице ниже показаны примеры синтаксиса, необходимого для общих критериев:

Цель	Критерии
Ячейки больше 75	«>75»
Ячейки равные 100	100 или «100»
Ячейки меньше или равные 100	«<=100»
Ячейки, равные «красному»	«красный»
Ячейки не равны «красному»	«<>красный»
Пустые ячейки «»	«»
Непустые ячейки _	«<>»
Ячейки, начинающиеся с «X»	«Икс*»
Ячейки меньше A1	«<«&A1
Ячейки меньше, чем сегодня	«<«&СЕГОДНЯ()

Обратите внимание, что последние два примера включают конкатенацию с помощью символа амперсанда (&). Каждый раз, когда вы используете значение из другой ячейки или используете результат формулы в критериях с логическим оператором, таким как «<», вам нужно будет выполнить конкатенацию. Это связано с тем, что Excel необходимо оценить ссылки на ячейки и формулы, чтобы получить значение, прежде чем это значение можно будет соединить с оператором.

Ограничения

У SUMIF есть несколько ограничений, о которых вам следует знать. Во-первых, SUMIF поддерживает только одно условие. Если вам нужно суммировать ячейки по нескольким критериям, используйте функцию SUMIFS.

Во-вторых, функция СУММЕСЛИ требует фактического диапазона для аргумента диапазона; вы не можете заменить массив. Это означает, что вы не можете делать такие вещи, как извлечение года из диапазона, содержащего даты внутри функции SUMIF. Если вам нужно манипулировать значениями, которые появляются в аргументе, прежде чем применять критерии, функция  SUMPRODUCT является гибким решением.

Основное использование

С числами в диапазоне A1:A10 вы можете использовать SUMIF для суммирования ячеек больше 5 следующим образом:

=SUMIF(A1:A10,">5")

Если диапазон B1:B10 содержит названия цветов, такие как «красный», «синий» и «зеленый», вы можете использовать SUMIF для суммирования чисел в A1:A10, когда цвет в B1:B10 «красный», например:

=SUMIF(B1:B10,"red",A1:A10)

Обратите внимание, что A1:A10 теперь вводится как диапазон_суммы, поскольку он отличается от диапазона , который содержит только названия цветов. Напомним: критерии применяются к ячейкам в диапазоне . Когда ячейки в диапазоне соответствуют критериям, суммируются соответствующие ячейки в диапазоне sum_range. Аргумент диапазон_суммы является необязательным. Если sum_range опущен, вместо этого суммируются ячейки в диапазоне .

Пример рабочего листа

На показанном листе есть три формулы SUMIF. В первой формуле (G5) функция возвращает общий объем продаж, где Имя = «Джим». Во второй формуле (G6) СУММЕСЛИ возвращает общий объем продаж, где штат = «ca» (Калифорния). В третьей формуле (G7) СУММЕСЛИ возвращает сумму продаж > 100:

=SUMIF(B5:B15,"jim",D5:D15) // name = "jim"
=SUMIF(C5:C15,"ca",D5:D15) // state = "ca"
=SUMIF(D5:D15,">100") // sales > 100

Обратите внимание, что знак равенства (=) не требуется при построении критерия «равно». Также обратите внимание, что SUMIF не чувствителен к регистру; вы можете использовать «джим» или «джим». Наконец, обратите внимание, что последняя формула не включает sum_range , поэтому вместо этого суммируется диапазон.

Критерии в другой ячейке

Значение из другой ячейки можно включить в критерий с помощью конкатенации. В приведенном ниже примере функция СУММЕСЛИ возвращает сумму всех продаж, превышающую значение в G4. Обратите внимание, что оператор «больше» (>), который является текстом, должен быть заключен в кавычки. Формула в G5:

=SUMIF(D5:D9,">"&G4) // sum if greater than G4

Не равно

Чтобы выразить критерий «не равно», используйте оператор «<>»  , заключенный в двойные кавычки («»):

=SUMIF(B5:B9,"<>red",C5:C9) // not equal to "red"
=SUMIF(B5:B9,"<>blue",C5:C9) // not equal to "blue"
=SUMIF(B5:B9,"<>"&E7,C5:C9) // not equal to E7

Обратите внимание, что SUMIF не чувствителен к регистру.

Пустые ячейки

СУММЕСЛИ может вычислять суммы на основе ячеек, которые пусты или не пусты. В приведенном ниже примере СУММЕСЛИ используется для суммирования сумм в столбце C в зависимости от того, содержит ли столбец D «x» или он пуст:

=SUMIF(D5:D9,"",C5:C9) // blank
=SUMIF(D5:D9,"<>",C5:C9) // not blank

Даты

Лучший способ использовать СУММЕСЛИ с датами — обратиться к допустимой дате в другой ячейке или использовать функцию ДАТА. В приведенном ниже примере показаны оба метода:

=SUMIF(B5:B9,"<"&DATE(2019,3,1),C5:C9)
=SUMIF(B5:B9,">="&DATE(2019,4,1),C5:C9)
=SUMIF(B5:B9,">"&E9,C5:C9)

Обратите внимание, что мы должны объединить оператор с датой в E9. Чтобы использовать более сложные критерии даты (т. е. все даты в данном месяце или все даты между двумя датами), вам нужно переключиться на функцию СУММЕСЛИМН, которая может обрабатывать несколько критериев.

Подстановочные знаки

Функция СУММЕСЛИ поддерживает подстановочные знаки, как показано в примере ниже:

=SUMIF(B5:B9,"mi*",C5:C9) // begins with "mi"
=SUMIF(B5:B9,"*ota",C5:C9) // ends with "ota"
=SUMIF(B5:B9,"????",C5:C9) // contains 4 characters

Тильда (~) — это escape-символ, позволяющий находить буквальные подстановочные знаки. Например, чтобы соответствовать буквальному вопросительному знаку (?), звездочке (*) или тильде (~), добавьте тильду перед подстановочным знаком (например, ~?, ~*, ~~).

Предупреждение о средней дальности

СУММЕСЛИ делает определенные предположения о размере  sum_range , по существу изменяя его размер, когда это необходимо, чтобы соответствовать аргументу диапазона , используя верхнюю левую ячейку в диапазоне в качестве источника. В некоторых случаях такое поведение может привести к результату, который кажется разумным , но на самом деле неверным.

Примечания

• SUMIF поддерживает только одно условие. Используйте функцию СУММЕСЛИМН для нескольких критериев.
• Когда sum_range опущен, ячейки в диапазоне будут суммироваться.
• Нечисловые критерии должны быть заключены в двойные кавычки (например, «<100», «>32», «TX»).
• Ссылки на ячейки в критериях не заключаются в кавычки, т.е. «<«&A1.
• Подстановочные знаки? и * можно использовать в критериях. Знак вопроса соответствует любому одному символу, а звездочка соответствует любой последовательности символов (ноль или более).
• Чтобы соответствовать буквальному вопросительному знаку (?) или звездочке (*), используйте тильду (~), например (~?, ~*).
• Функция требует диапазон, вы не можете заменить массив.

Многие организации работают сегодня с отсрочкой платежа. Почти каждая из них рано или поздно сталкивается с задержками оплаты. Зачастую дело доходит до суда.

При этом задолженность по оплате может составлять как существенную сумму, так и мизерную. Естественно, нет смысла подавать в суд на неплательщика, сумма задолженности которого меньше чем судебные издержки.

Сегодня мы составим список должников, выявим тех, на кого есть смысл подавать в суд и посчитаем сумму, на которую мы можем рассчитывать в случае положительного исхода, с учетом пени за просроченный период.

Для этого мы будем использовать

логический оператор «СУММЕСЛИ» в Excel

Начнем с того, что составим либо сформируем в бухгалтерской программе список должников следующего вида:

Используя данные о дате отгрузки, определим крайний срок оплаты исходя из предоставленной отсрочки. Прибавим количество дней отсрочки к дате отгрузки.

Для того, чтобы прибавить число к дате в Excel

необходимо произвести следующие действия:

Активизируем соответствующую ячейку и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем соответствующие ячейки в следующем порядке:

= ДАТА + ЧИСЛО ENTER

Если дата не получилась, проверим формат ячейки и удостоверимся в том, что формат установлен в режиме «дата».

Получаем:

Теперь рассчитаем количество просроченных дней. Для этого вычтем из текущей даты дату крайнего срока оплаты.

Для того, чтобы в Excel вычесть из одной даты другую

необходимо произвести следующие действия:

Активизируем соответствующую ячейку и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем соответствующие ячейки в следующем порядке:

= ДАТА(1) – ДАТА(2) ENTER

Если число не получилось, опять же проверим формат ячейки и удостоверимся в том, что формат установлен в режиме «числовой».

В нашем случае мы будем ссылаться на одну и ту же ячейку текущей даты, поэтому воспользуемся абсолютной ссылкой

Протянем формулу до конца листа и получим:

Ну, а теперь рассчитаем пеню за период

Расчет пени за период просрочки

Рассмотрим два случая:

• исходя из 0,1% за день просрочки
• исходя из ставки рефинансирования на день расчета (возьмем 10%)

Рассчитаем пеню в Excel:

1. 0,1% от суммы задолженности за 1 день просрочки

Активизируем соответствующую ячейку и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем соответствующие ячейки в следующем порядке:

= 0,1% * Сумма задолженности * Количество просроченных дней ENTER

2. Ставка рефинансирования на день расчета (в примере — 10%)

Активизируем соответствующую ячейку и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем соответствующие ячейки в следующем порядке:

= 10% * Сумма задолженности / 365 * Количество просроченных дней ENTER

Сложим полученную пеню и сумму задолженности

и приступим к вычленению тех контрагентов, на которых есть смысл подавать в суд.

Логический оператор «СУММЕСЛИ»

Предположим, что судебные издержки составляют 950 рублей на одного клиента. Для того, чтобы узнать общую сумму, на которую мы можем рассчитывать после положительного решения суда без учета задолженности контрагентов, имеющих задолженность меньше 950 рублей с учетом пени, используем логический оператор «суммесли».

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

Посмотрим, что мы имеем:

Давайте разбираться.

Окошко «Диапазон» нужно для того, чтобы выбрать область значений, среди которых будет производиться выборка. В нашем случае это будут все значения последнего столбика кроме итоговой суммы.

Окошко «Критерий» служит для того, чтобы задать условие выборки. В нашем случае условие будет: >950

И, наконец, окошко «Диапазон_суммирования» нужен для задания значений, сумму которых мы хотим получить с учетом условия. В нашем случае «Диапазон» и «Диапазон_суммирования» будут совпадать, т.к. мы задаем числовое условие, имеющее отношение только к последнему столбику.

После заполнения полей получаем:

В результате наших расчетов получаем общую сумму, планируемую к получению, за исключением сумм задолженностей, которые не превышают 950 рублей.

Для демонстрации работы логического оператора «суммесли» проведем еще одну выборку.

В первом случае «Диапазон» и «Диапазон_суммирования» совпали. Рассмотрим случай, когда в данных окошках указываются разные диапазоны значения.

Например, мы хотим выяснить сумму задолженности только по «Клиенту 1»

В этом случае в графе «Диапазон» мы проставляем значения первого столбика, а в графе «Критерий» вручную пишем «Клиент 1», либо выбираем любую ячейку, содержащую это значение. В графе «Диапазон_суммирования» выбираем последний столбик, т.к. хотим выбрать задолженность по Клиенту 1 из общей суммы.

Получаем:

Как видите, логический оператор «суммесли» многофункционален и при правильном подходе может пригодиться при решении различных задач.

Удачи вам в расчетах!