Формулы количества и суммы в Excel — это самые популярные функции, которые чаще всего нужны для составления отчётов, ведомостей и аналитических таблиц. Базовые формулы количества и суммы в Excel — СЧЁТ и СУММ — знают почти все. Но что делать, когда нужно посчитать не все значения подряд, а только те, которые соответствуют определённым условиям? Для этого в Excel есть расширенные формулы количества и суммы в Excel с условиями: СЧЁТЕСЛИ, СУММЕСЛИ, а также их версии для нескольких условий — СЧЁТЕСЛИМН и СУММЕСЛИМН. Разберём каждую с примерами.
Таблица 1. Формулы количества в Excel (COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS)
| Функция | Описание и пример |
|---|---|
| СЧЁТ | СЧЁТ (COUNT) — подсчитывает количество ячеек, содержащих числа. Учитывает числа, даты и текстовое представление чисел (например, «123»). Синтаксис: =СЧЁТ(значение1;[значение2];...)Пример: =СЧЁТ(A1:A20) — посчитает, сколько ячеек в диапазоне A1:A20 содержат числа. |
| СЧЁТЗ | СЧЁТЗ (COUNTA) — подсчитывает количество заполненных ячеек в диапазоне (не пустых). Учитывает данные любого типа: текст, числа, ошибки, логические значения. Пустые ячейки игнорирует. Синтаксис: =СЧЁТЗ(значение1;[значение2];...)Пример: =СЧЁТЗ(A2:A7) — посчитает все непустые ячейки в диапазоне A2:A7. |
| СЧИТАТЬПУСТОТЫ | СЧИТАТЬПУСТОТЫ (COUNTBLANK) — подсчитывает количество пустых ячеек в указанном диапазоне. Синтаксис: =СЧИТАТЬПУСТОТЫ(диапазон)Пример: =СЧИТАТЬПУСТОТЫ(A2:A7) — покажет, сколько пустых ячеек в диапазоне A2:A7. |
| СЧЁТЕСЛИ | СЧЁТЕСЛИ (COUNTIF) — подсчитывает количество ячеек, отвечающих одному условию. Функция не учитывает регистр. Синтаксис: =СЧЁТЕСЛИ(диапазон; условие)Примеры: =СЧЁТЕСЛИ(A2:A5;"Лондон") — сколько ячеек в диапазоне A2:A5 содержат текст «Лондон».=СЧЁТЕСЛИ(A2:A5;A4) — сколько ячеек равны значению в ячейке A4. |
| СЧЁТЕСЛИМН | СЧЁТЕСЛИМН (COUNTIFS) — подсчитывает количество ячеек, удовлетворяющих нескольким условиям одновременно. Важно: все диапазоны должны быть одинакового размера. Синтаксис: =СЧЁТЕСЛИМН(диапазон_условия1; условие1; [диапазон_условия2; условие2];…)Пример: =СЧЁТЕСЛИМН(B2:B5;"=Да"; F2:F5;">1") — сколько строк, где в столбце B стоит «Да», а в столбце F значение больше 1. |
Таблица 2. Формулы суммы в Excel (SUM, SUMIF, SUMIFS)
| Функция | Описание и пример |
|---|---|
| СУММ | СУММ (SUM) — суммирует все числовые значения в указанном диапазоне или перечисленных ячейках. Синтаксис: =СУММ(число1; [число2];...)Примеры: =СУММ(A2:A10) — сложит числа из диапазона A2:A10.=СУММ(A2:A10; C2:C10) — сложит числа из двух несмежных диапазонов. |
| СУММЕСЛИ | СУММЕСЛИ (SUMIF) — суммирует значения, которые соответствуют одному условию. Можно задать условие для одного диапазона, а суммировать значения из другого. Синтаксис: =СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])Пример 1: =СУММЕСЛИ(B2:B25;">5") — суммирует все числа из диапазона B2:B25, которые больше 5.Пример 2: =СУММЕСЛИ(A2:A7;"Фрукты";C2:C7) — сложит значения из C2:C7 только для тех строк, где в A2:A7 написано «Фрукты». |
| СУММЕСЛИМН | СУММЕСЛИМН (SUMIFS) — суммирует значения, которые удовлетворяют нескольким условиям одновременно. Важно: сначала указывается диапазон суммирования, затем пары «диапазон_условия — условие». Синтаксис: =СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)Пример: =СУММЕСЛИМН(B2:B9; C2:C9; "Рязань"; E2:E9; "Бананы") — суммирует значения из B2:B9 для тех строк, где в столбце C написано «Рязань», а в столбце E — «Бананы». |
Какую формулу когда выбирать: краткая шпаргалка
- Просто посчитать количество чисел →
=СЧЁТ(диапазон) - Посчитать все заполненные ячейки (и текст, и числа) →
=СЧЁТЗ(диапазон) - Посчитать пустые ячейки →
=СЧИТАТЬПУСТОТЫ(диапазон) - Посчитать количество по одному условию →
=СЧЁТЕСЛИ(диапазон; условие) - Посчитать количество по нескольким условиям →
=СЧЁТЕСЛИМН(диапазон1; условие1; диапазон2; условие2) - Просто сложить числа →
=СУММ(диапазон) - Сложить числа по одному условию →
=СУММЕСЛИ(диапазон_условия; условие; диапазон_суммирования) - Сложить числа по нескольким условиям →
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; диапазон_условия2; условие2)
Пример из реальной работы: как применять формулы количества и суммы в Excel
Исходные данные: у вас есть таблица продаж за месяц: столбец A — город, столбец B — товар, столбец C — количество, столбец D — выручка.
Задачи и решения:
- Сколько всего продаж (строк) было? →
=СЧЁТЗ(A2:A1000) - Сколько продаж было в Москве? →
=СЧЁТЕСЛИ(A2:A1000; "Москва") - Сколько продаж бананов было в Рязани? →
=СЧЁТЕСЛИМН(A2:A1000; "Рязань"; B2:B1000; "Бананы") - Общая выручка за месяц →
=СУММ(D2:D1000) - Выручка только по Москве →
=СУММЕСЛИ(A2:A1000; "Москва"; D2:D1000) - Выручка по бананам в Рязани →
=СУММЕСЛИМН(D2:D1000; A2:A1000; "Рязань"; B2:B1000; "Бананы")
💡 Важно: в функциях с несколькими условиями (СЧЁТЕСЛИМН и СУММЕСЛИМН) все диапазоны условий должны быть одинаковой длины и одного размера. Если размеры различаются, Excel выдаст ошибку #ЗНАЧ!.
Частые ошибки и как их избежать
- Ошибка: путаю СЧЁТЗ и СЧЁТ. Запомните: СЧЁТ считает только числа, СЧЁТЗ — всё подряд, кроме пустых ячеек.
- Ошибка: в условии не использую кавычки для текста. Любой текст, дата или логическое выражение в условии должно быть в кавычках:
"Москва",">5","<>". - Ошибка: для ссылки на ячейку кавычки не нужны. Если условие берётся из другой ячейки, пишите просто
=СЧЁТЕСЛИ(A2:A100; B1), без кавычек. - Ошибка: в СУММЕСЛИМН перепутан порядок аргументов. У СУММЕСЛИМН первым идёт диапазон суммирования, у СЧЁТЕСЛИМН — условия. Будьте внимательны.
В Excel также есть полезные функции, о которых мало кто знает. Они достаточно простые, но при этом могут существенно упростить работу и сэкономить время в десятки и сотни раз. Полезные лайфхаки можно прочитать в статье «Полезные функции Excel: мелочи, которые помогут упростить работу».
📌 О самых используемых формулах, лайфхаках и секретах Excel можно прочитать, перейдя по ссылке.
Самоучитель по Excel: справочник формул с примерами для пользователей (в т.ч. начинающих), умные и сводные таблицы, работа с большими массивами данных, защита данных эксель и т.д.