СЧЁТЕСЛИ и СУММЕСЛИ

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

СЧЁТЕСЛИ считает, сколько ячеек в диапазоне удовлетворяют условию; СУММЕСЛИ складывает только те значения, для которых условие выполнено.

Зачем это нужно

Представьте журнал из 200 строк: даты продаж, города, суммы. Вопрос учителя на ЕГЭ или начальника на работе звучит одинаково: «Сколько заказов из Москвы?» и «На какую сумму продали в Москве?». Перебирать руками 200 строк — это ошибка и потерянное время. Две функции, СЧЁТЕСЛИ и СУММЕСЛИ, отвечают на оба вопроса мгновенно и пересчитываются сами, когда данные меняются.

Английские имена этих функций — COUNTIF и SUMIF. В Google Таблицах и англоязычном Excel пишут именно их; смысл и порядок аргументов те же.

Синтаксис: диапазон, критерий, диапазон_суммирования

У СЧЁТЕСЛИ всего два аргумента:

=СЧЁТЕСЛИ(диапазон; критерий)

Диапазон — где искать, критерий — что искать. У СУММЕСЛИ аргументов три, и порядок коварный: сначала диапазон, по которому проверяем условие, потом сам критерий, и только третьим — диапазон, числа из которого надо сложить.

=СУММЕСЛИ(диапазон_условия; критерий; диапазон_суммирования)

Возьмём таблицу заказов. Город — в столбце B (строки 2–9), сумма — в столбце C.

Формулы и их результат:

Проверим суммирование: московские строки 2, 4, 6, 9 дают $1200 + 1500 + 900 + 2000 = 5600$ — сходится.

Критерии со сравнением

Критерий — это не только точное слово. Можно сравнивать числа, упаковав знак сравнения в кавычки вместе со значением:

Например, =СЧЁТЕСЛИ(C2:C9; ">=1000") вернёт 4 (строки 2, 4, 7, 9). А если число лежит в ячейке E1, его подставляют конкатенацией: =СЧЁТЕСЛИ(C2:C9; ">="&E1) — знак сравнения в кавычках, ссылка приклеена амперсандом.

Шаблоны: звёздочка и знак вопроса

В текстовых критериях работают подстановочные знаки. Звёздочка * заменяет любое число любых символов, вопрос ? — ровно один символ.

Несколько условий: СЧЁТЕСЛИМН и СУММЕСЛИМН

Когда условие одно — берём СЧЁТЕСЛИ/СУММЕСЛИ. Когда условий несколько (например, «Москва И сумма больше 1000»), нужны их «множественные» версии: СЧЁТЕСЛИМН (COUNTIFS) и СУММЕСЛИМН (SUMIFS). Внимание: у СУММЕСЛИМН диапазон суммирования идёт ПЕРВЫМ, а пары «диапазон; критерий» — после него.

=СЧЁТЕСЛИМН(диап1; крит1; диап2; крит2; ...)
=СУММЕСЛИМН(диапазон_суммы; диап1; крит1; диап2; крит2; ...)

Все условия соединяются логическим И — строка попадёт в подсчёт, только если выполнены ВСЕ критерии сразу.

Москва с суммой не меньше 1000 — это строки 2, 4, 9 (значения 1200, 1500, 2000), их три, в сумме 4700. Строка 6 (Москва, 900) не прошла по второму условию.

Как это работает

Под капотом обе функции делают один проход по диапазону: для каждой ячейки проверяют, истинно ли условие, и либо прибавляют единицу (СЧЁТЕСЛИ), либо прибавляют соответствующее число (СУММЕСЛИ). Это легко повторить на Python, чтобы убедиться в результате:

goroda = ["Москва", "Казань", "Москва", "Пермь",
          "Москва", "Казань", "Пермь", "Москва"]
summy  = [1200, 800, 1500, 600, 900, 1100, 400, 2000]

# СЧЁТЕСЛИ(B; "Москва")
schet = sum(1 for g in goroda if g == "Москва")
# СУММЕСЛИ(B; "Москва"; C)
sumif = sum(s for g, s in zip(goroda, summy) if g == "Москва")
# СУММЕСЛИМН: Москва И сумма >= 1000
sumifs = sum(s for g, s in zip(goroda, summy)
             if g == "Москва" and s >= 1000)

print("СЧЁТЕСЛИ:", schet)
print("СУММЕСЛИ:", sumif)
print("СУММЕСЛИМН:", sumifs)

Вывод:

СЧЁТЕСЛИ: 4
СУММЕСЛИ: 5600
СУММЕСЛИМН: 4700

Видно, что формула таблицы — это просто условная сумма по списку. Если обозначить число подходящих строк как $n$, а их значения как $x_i$, то СУММЕСЛИ возвращает

$$ \sum_{i:\,\text{условие}} x_i $$

то есть сумму только по тем индексам, где условие истинно.

Частые ошибки

• Перепутан порядок аргументов СУММЕСЛИ. В СУММЕСЛИ диапазон суммирования — третий, а в СУММЕСЛИМН — первый. Это самая частая путаница; держите шпаргалку рядом.
• Знак сравнения без кавычек. Писать надо ">=1000" целиком в кавычках. Если ссылаетесь на ячейку — ">="&E1, а не ">=E1" (иначе будете искать текст «>=E1»).
• Разная длина диапазонов. В СУММЕСЛИМН все диапазоны должны быть одинакового размера (например, все B2:B9 и C2:C9), иначе функция выдаст ошибку.
• Лишние пробелы в данных. «Москва » с пробелом на конце не совпадёт с критерием «Москва» — частая причина, когда подсчёт «теряет» строки.

Итоги

• СЧЁТЕСЛИ(диапазон; критерий) считает строки по одному условию, СУММЕСЛИ(диапазон; критерий; диапазон_суммы) складывает их значения.
• Критерий бывает точным словом, сравнением в кавычках (">=1000") или шаблоном со звёздочкой/вопросом.
• Для нескольких условий берут СЧЁТЕСЛИМН и СУММЕСЛИМН — все критерии соединяются логическим И.
• У СУММЕСЛИМН диапазон суммирования стоит первым — не путайте с СУММЕСЛИ.