СУММИФ и СЧЁТЕСЛИ: сложите только те строки, которые совпадают.

1. Фильтрация и суммирование в одной формуле

Перед объяснением: как бы вы получили общее количество Востока за февраль из этой таблицы вообще без формулы, только с помощью меню, и сколько шагов для этого потребуется?

Вы можете отфильтровать регион по востоку, отфильтровать месяц по февралю, прочитать сумму в строке состояния и отменить оба фильтра. Четыре шага, число, которое нигде не живет, и лист, который вы временно сломали, чтобы кто-то еще на него посмотрел. Условное агрегирование — это та же работа, что и одна формула: фильтрация строк и суммирование их за один ход, при этом ответ сохраняется в ячейке.

=SUMIFS(D2:D17, A2:A17, "East", B2:B17, "Feb")   → 2,200

Прочитайте слева направо. Д2:Д17 — это столбец, который нужно добавить, и в SUMIFS он всегда идет первым. Тогда приходи пары критериев. Пара критериев — это два аргумента, которые передаются вместе: диапазон для поиска и строки условий, которые должны там совпадать. A2:A17, "East" это одна пара, сохраняйте строки, регион которых — Восток. B2:B17, "Feb" это другое, и строки должны проходить оба. Два ряда: 1480 Аны и 720 Бена, и 1480 + 720 = 2200.

Пары складываются столько, сколько вам нужно, до 127 штук. Добавлять C2:C17, "Ana" и ответ сужается до 1480, это всего лишь ее февральская распродажа на Востоке. Каждая пара — это еще один вентиль с картинки выше, и вентили всегда объединяются по принципу «И»: все условия, а не любые.

2. СУММЕСЛИ против СУМИФС и ловушка порядка аргументов

Перед пояснением: SUMIF — это более старая версия с одним условием. Если вы сначала разработали его, а позже понадобилась версия с несколькими условиями, почему столбец суммы может оказаться в другом положении?

Вот ловушка в одной паре строк. Оба из них возвращают общее количество Востока - 5480.

=SUMIF(A2:A17, "East", D2:D17)          → 5,480  (sum range LAST)
=SUMIFS(D2:D17, A2:A17, "East")         → 5,480  (sum range FIRST)

СУММЕСЛИ помещает диапазон сумм в конец в качестве третьего аргумента. SUMIFS ставит его в начало. Причина в истории проектирования, о которой вы только что догадались: форма СУММЕСЛИ представляла собой диапазон, условие, а затем необязательное «фактически суммировать этот другой столбец». Когда Майкрософт создала SUMIFS, диапазон сумм больше не мог быть необязательным или последним, поскольку парам критериев требуется свободное пространство для размещения за ним. Таким образом, две функции читают друг друга в обратном направлении, навсегда.

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

Мой совет — положить конец проблеме, а не управлять ею. Всегда пишите SUMIFS, даже для одного условия. Это стоит одну дополнительную букву, обрабатывает каждый случай, обрабатываемый SUMIF, и в конечном итоге в ваших книгах будет ровно один порядок аргументов. То же самое для COUNTIFS вместо COUNTIF и AVERAGEIFS вместо AVERAGEIF.

3. Критерии написания: текст, числа, сравнения и ссылки на ячейки.

Перед пояснением: «Восток» нуждается в кавычках, и сравнение с 1000 тоже предполагает кавычки, но в другом месте. Как вы думаете, куда идут кавычки «сумма не менее 1000»?

Критерии бывают трех видов, и вся сложность заключается в правилах цитирования.

Текстовые критерии в кавычках: "East", "Feb", "Ana". При сопоставлении игнорируются верхний и нижний регистр, поэтому «восток» тоже подойдет.

Простые числовые критерии не надо кавычек. =SUMIFS(D2:D17, D2:D17, 500) будет суммировать только строки, сумма которых равна ровно 500, а здесь это только строка Гила: 500.

Критерии сравнения поместите оператор и число в кавычки, как один фрагмент текста: ">=1000". В первый раз это звучит странно, но все сравнение передается как небольшая строка инструкций.

=SUMIFS(D2:D17, D2:D17, ">=1000")   → 10,610

Восемь из шестнадцати строк содержат 1000 или более, а 1200 + 1480 + 1130 + 1340 + 1610 + 1275 + 1520 + 1055 = 10610. Пары критериев также свободно смешиваются, поэтому восточные строки, содержащие не менее 1000, являются =SUMIFS(D2:D17, A2:A17, "East", D2:D17, ">=1000"), что составляет 1200 + 1480 + 1130 = 3810.

Ссылка на ячейку внутри сравнения стоит запомнить, потому что именно он делает отчет настраиваемым. Если порог 1000 находится в ячейке F1, вы не можете написать «>=F1», потому что это будет сравниваться с буквальным текстом F1. Вы приклеиваете текст оператора к значению ячейки с помощью &, символ соединения:

=SUMIFS(D2:D17, D2:D17, ">="&F1)   → 10,610 when F1 holds 1000

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

4. СЧЁТЕСЛИ и СРЗНИЕ, одна и та же грамматика.

Прежде чем объяснить: зная SUMIFS, что вы ожидаете от COUNTIFS в качестве своего первого аргумента, учитывая, что у счетчика нет столбца для добавления?

Ничего, вот ответ. Для подсчета не требуется столбец суммы, поэтому COUNTIFS — это только пары критериев:

=COUNTIFS(A2:A17, "East", B2:B17, "Feb")   → 2
=COUNTIFS(B2:B17, "Feb")                   → 6

Два восточных ряда в феврале, всего шесть февральских рядов, и оба вы можете проверить на глаз в таблице выше. COUNTIFS также является инструментом аудита для остальных членов семейства: прежде чем доверять какой-либо SUMIFS, спросите COUNTIFS, на скольких строках она находится. Общее число 2200 означает, что в двух строках находится нечто иное, чем в 200, что является той же честностью малой выборки, что и GROUP BY и HAVING преподает на стороне SQL.

AVERAGEIFS представляет собой один абзац, потому что это в точности грамматика SUMIFS: сначала столбец среднего значения, затем пары критериев. =AVERAGEIFS(D2:D17, A2:A17, "West") возвращает 1215, что соответствует пяти продажам Уэста: 860 + 1340 + 990 + 1610 + 1275 = 6075, разделенным на 5. Единственное, что нужно знать: если ни одна строка не совпадает, СРЗНАЧЕСЛИ показывает ошибку деления на ноль, а не ноль, и эта ошибка говорит вам правду.

5. Полное до и после

Та же таблица, тот же вопрос: что продавали в феврале в каждом регионе?

До

Ручной цикл. Отфильтруйте регион по востоку, отфильтруйте месяц по февралю, прочитайте 2200 в строке состояния, запишите его на другом листе вручную, отмените, повторите для Запада, Севера, Юга. Четыре итога, шестнадцать действий меню и вставленные числа устаревают в момент добавления строки. Хуже всего не усилия. Дело в том, что ничто не связывает набранные 2200 с данными, поэтому никто не сможет их проверить позже, включая вас.

после

Небольшой сводный блок. Поместите названия регионов в клавиши F2–F5, затем одну формулу в G2, записанную один раз со знаками доллара, чтобы она заполнялась:

=SUMIFS($D$2:$D$17, $A$2:$A$17, F2, $B$2:$B$17, "Feb")

Каждый номер активен, каждый номер можно проверить, и блок обновляется, когда наступает март. Две из этих строк также являются предупреждениями, и это следующий раздел, потому что 0 Юга и недостающие 500 из строки пустой области Гила пытаются вам что-то сказать. Представьте себе, что вы создаете этот точный блок на основе своих собственных данных о продажах, прежде чем читать дальше: в каком столбце указан ваш регион, какова ваша сумма и что будет находиться в F2?

6. Пограничные случаи: пробелы, даты и несовпадающие диапазоны.

Перед объяснением: добавьте приведенные выше четыре итоговых показателя за февраль. Вы получаете 5680. Но суммирование каждой февральской строки таблицы дает 6180. Куда пропали 500?

Из каждой названной группы выпадают пробелы. Недостающие 500 — это строка Гила, ячейка региона которой пуста. Он соответствует «Восток» нет, «Запад» нет и т. д., поэтому он не отображается ни в одном регионе, и ни одна ошибка не указывает на это. Сводка по региону, в которой молча пропускаются строки, является причиной того, что отчет расходится с бухгалтерской книгой. Проверка представляет собой одну формулу: =SUMIFS(D2:D17, A2:A17, "") возвращает 500, общее количество находится в пустых регионах. Если это значение не равно нулю, у вас есть неназначенные строки, которые нужно исправить или сообщить как отдельную строку.

Нулевой результат имеет два значения. Общее количество данных Юг за февраль равно 0, поскольку у Юг вообще нет строк за февраль. Ноль может означать «совпадающие строки добавляются к нулю» или «ничего не соответствует». =COUNTIFS(A2:A17, "South", B2:B17, "Feb") возврат 0 определяет, какой из них у вас есть.

Даты в критериях лучше всего работают в виде скобок. Наш столбец «Месяц» содержит текст, поэтому «Февраль» напрямую соответствует ему. Реальные таблицы обычно содержат фактические даты, например 14 февраля 2026 г., и ни один критерий не соответствует «весь месяц». Узор – это два критерия на одной колонне, пол и потолок:

=SUMIFS(D2:D17, B2:B17, ">="&DATE(2026,2,1), B2:B17, "<"&DATE(2026,3,1))

Там написано: 1 февраля или после этой даты, но до 1 марта. С использованием DATE(2026,2,1) вместо того, чтобы вводить «01.02.2026», можно обойти любую неоднозначность регионального формата даты. В нашей таблице, где столбец «Месяц» является датой, эта скобка вернет полные 6180 за февраль.

Все диапазоны должны быть одинакового размера. Если диапазон сумм — D2:D17, а диапазон критериев — A2:A16, Excel отображает #ЗНАЧ!. Раздражающе, но честно: исправление заключается в том, что каждый диапазон охватывает одни и те же строки. Скрытая версия этой ошибки — диапазон критериев, сдвинутый на одну строку, D2:D17 против A3:A18, который имеет ту же высоту и нигде не содержит ошибок. Ссылки на весь столбец, D:D против A:A, устраняют оба риска на простом листе.