Все формулы РФ

Excel и Google Workspace / Поиск и подстановка

SUMIFS для суммы по нескольким условиям

SUMIFS суммирует значения из D2:D100 только по тем строкам, где город в B2:B100 равен Москве, а дата в C2:C100 не раньше 1 января 2026 года. Функция подходит для отчетов продаж, расходов и оплат с несколькими фильтрами.

Опубликовано: Обновлено:

Поиск и подстановкаОфисные расчетыExcelЕсть историческая справкаСтраницы с задачами и решениями

Формула

$$=SUMIFS(D2:D100,B2:B100,"Москва",C2:C100,">="&DATE(2026,1,1))$$

Обозначения

$D2:D100$
диапазон чисел, которые нужно суммировать
$B2:B100$
диапазон городов для первого условия
$"Москва"$
значение города, по которому отбираются строки
$C2:C100$
диапазон дат для второго условия
$">="&DATE(2026,1,1)$
условие даты: больше или равно 1 января 2026 года

Условия применения

  • Диапазон суммы и все диапазоны условий должны иметь одинаковый размер.
  • Текстовые условия берутся в кавычки, а условия сравнения с датой или числом часто собираются через оператор &.
  • Даты должны быть настоящими датами Excel, а не текстом, который только выглядит как дата.

Ограничения

  • SUMIFS объединяет условия логикой И: строка должна выполнить все условия сразу.
  • Для логики ИЛИ обычно используют несколько SUMIFS и складывают результаты.
  • Если в суммируемом диапазоне встречается текст вместо числа, он не попадет в сумму как числовое значение.

Подробное объяснение

SUMIFS сначала проверяет каждую строку по всем заданным условиям. В этой формуле строка должна одновременно относиться к Москве и иметь дату не раньше 1 января 2026 года.

Если строка прошла проверку, Excel берет число из соответствующей позиции диапазона D2:D100 и добавляет его к сумме. Если хотя бы одно условие не выполнено, строка пропускается.

Порядок аргументов у SUMIFS отличается от старой SUMIF: сначала идет диапазон суммы, затем пары диапазон условия и условие. Это удобно, когда условий много, но требует внимательности при наборе формулы.

Условия сравнения записываются как текстовые выражения. Когда критерий зависит от функции или ячейки, оператор сравнения соединяют с этим значением через &, например ">="&DATE(2026,1,1) или ">"&F2.

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

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

Как пользоваться формулой

  1. Выберите числовой диапазон, который нужно суммировать.
  2. Добавьте первый диапазон условия и критерий для него.
  3. Добавьте второй диапазон условия и критерий даты.
  4. Убедитесь, что все диапазоны начинаются и заканчиваются на одинаковых строках.
  5. Проверьте итог на небольшом наборе строк вручную.

Историческая справка

Функции условного суммирования появились как ответ на повседневную потребность строить отчеты без сводных таблиц и макросов.

SUMIFS расширила идею SUMIF, добавив несколько условий в одной формуле. Это сделало ее особенно полезной для управленческих отчетов и бухгалтерских сверок.

В офисной практике функция стала стандартным инструментом для быстрых итогов по датам, подразделениям, менеджерам, статусам и категориям.

Даже при наличии Power Query и сводных таблиц SUMIFS остается удобной для прозрачных расчетов прямо на листе.

С развитием выгрузок из CRM, банковских систем и складских программ такие формулы стали еще полезнее: они быстро дают итог по нужному срезу без перестройки модели данных.

Пример

Дано: B2:B5 содержит города Москва, Москва, Казань, Москва. C2:C5 содержит даты 2026-01-10, 2025-12-30, 2026-01-12, 2026-02-02. D2:D5 содержит суммы 15000, 9000, 8000, 6000. Формула =SUMIFS(D2:D5,B2:B5,"Москва",C2:C5,">="&DATE(2026,1,1)) учитывает строки 2 и 5. Результат: 21000. Строка 3 не попадает в сумму, потому что дата относится к 2025 году, хотя город Москва. Строка 4 не попадает, потому что город Казань, хотя дата подходит. Если добавить условие по менеджеру, итог сузится до строк, где выполнены уже три критерия одновременно.

Частая ошибка

Частая ошибка: написать условие даты как обычный текст в неподходящем региональном формате. Надежнее использовать DATE и соединять его с оператором сравнения через &. Еще одна ошибка: задать D2:D100, B2:B90 и C2:C100 разного размера. Excel вернет ошибку или не даст корректный расчет. Также важно помнить, что "Москва" и "Москва " с пробелом в конце считаются разными значениями.

Практика

Задачи с решением

Посчитать продажи Москвы

Условие. B2:B4 содержит Москва, Казань, Москва. C2:C4 содержит 2026-01-05, 2026-01-06, 2026-02-01. D2:D4 содержит 4000, 7000, 9000.

Решение. Формула суммирует только строки с городом Москва и датой не раньше 2026-01-01. Подходят первая и третья строки: 4000 + 9000.

Ответ. 13000

Изменить условие на сумму после февраля

Условие. Нужно учитывать только московские продажи с датой не раньше 1 февраля 2026 года.

Решение. Меняем критерий даты на ">="&DATE(2026,2,1). В примере подойдет только строка с датой 2026-02-01 и суммой 9000.

Ответ. 9000

Дополнительные источники

  • Microsoft Support: SUMIFS function - https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b
  • Microsoft Support: Excel functions by category - https://support.microsoft.com/en-us/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb
  • Dan Bricklin: VisiCalc and the origins of spreadsheets - https://www.bricklin.com/visicalc.htm

Связанные формулы

Excel и Google Workspace

VLOOKUP для точного поиска в Excel

$=VLOOKUP(E2,A2:C20,3,FALSE)$

Функция VLOOKUP ищет значение из E2 в первом столбце диапазона A2:C20 и возвращает значение из третьего столбца найденной строки. Последний аргумент FALSE включает точное совпадение, поэтому формула подходит для артикулов, кодов клиентов и других ключей, где приблизительный поиск недопустим.

Excel и Google Workspace

XLOOKUP для точного поиска в Excel

$=XLOOKUP(E2,A2:A20,C2:C20,"Не найдено",0)$

XLOOKUP ищет значение из E2 в диапазоне A2:A20 и возвращает соответствующее значение из C2:C20. В отличие от VLOOKUP, диапазон поиска и диапазон результата задаются отдельно, поэтому функция спокойно ищет как вправо, так и влево.

Excel и Google Workspace

INDEX и MATCH для поиска значения слева

$=INDEX(A2:A20,MATCH(E2,C2:C20,0))$

Связка INDEX и MATCH ищет значение E2 в C2:C20, определяет номер найденной позиции и возвращает значение с той же позиции из A2:A20. Такой прием особенно полезен, когда результат расположен левее столбца поиска.

Excel и Google Workspace

COUNTIFS для подсчета строк по нескольким условиям

$=COUNTIFS(B2:B100,"Москва",D2:D100,">5000")$

COUNTIFS считает строки, которые одновременно удовлетворяют нескольким условиям. В этой формуле учитываются только строки, где в столбце B указана Москва, а значение в столбце D больше 5000.