СЧЁТЕСЛИ и СУММЕСЛИ: подсчёт и сумма по условию
СЧЁТЕСЛИ считает ячейки, подходящие под условие, а СУММЕСЛИ складывает числа в строках, подходящих под условие:
=СЧЁТЕСЛИ(диапазон;критерий)
=СУММЕСЛИ(диапазон;условие;диапазон_суммирования)Пример — продажи менеджеров:
| A | B | C | |
|---|---|---|---|
| 1 | Менеджер | Город | Сумма |
| 2 | Анна | Минск | 1200 |
| 3 | Борис | Гомель | 800 |
| 4 | Вера | Минск | 1500 |
| 5 | Глеб | Брест | 650 |
| 6 | Дарья | Гомель | 950 |
| 7 | Егор | Минск | 400 |
| Формула | Результат | Что считает |
|---|---|---|
=СЧЁТЕСЛИ(B2:B7;"Минск") | 3 | сколько продаж в Минске |
=СУММЕСЛИ(B2:B7;"Минск";C2:C7) | 3100 | сумма продаж в Минске |
=СЧЁТЕСЛИ(C2:C7;">1000") | 2 | сколько продаж больше 1000 |
=СУММЕСЛИ(C2:C7;">=800") | 4450 | сумма продаж от 800 |
=СЧЁТЕСЛИ(A2:A7;"*а") | 2 | имена на «а»: Анна, Вера |
В СУММЕСЛИ первый диапазон — где проверять условие, третий — что складывать. Если условие проверяется по самим числам, третий аргумент не нужен: =СУММЕСЛИ(C2:C7;">=800").
Как записывать условия
| Условие | Запись |
|---|---|
| равно тексту | "Минск" (регистр не важен) |
| равно числу | 1200 |
| больше, меньше | ">1000", "<500", ">=800" |
| не равно | "<>Минск" |
| непустые ячейки | "<>" |
| пустые ячейки | "" |
| начинается с «Го» | "Го*" |
| содержит «ск» | "*ск*" |
| ровно 4 буквы | "????" |
Знаки сравнения пишутся в кавычках вместе с числом. * — любое количество любых символов, ? — один символ.
Условие из ячейки. Порог лежит в E1 — его приклеивают к знаку через &:
=СЧЁТЕСЛИ(C2:C7;">"&E1)Запись ">E1" не сработает: Excel будет сравнивать с текстом «E1». Город из ячейки — просто ссылка: =СУММЕСЛИ(B2:B7;E2;C2:C7).
Несколько условий
СЧЁТЕСЛИМН и СУММЕСЛИМН принимают пары «диапазон; условие». Все условия должны выполняться одновременно:
=СЧЁТЕСЛИМН(B2:B7;"Минск";C2:C7;">1000")
=СУММЕСЛИМН(C2:C7;B2:B7;"Гомель";C2:C7;">900")Первая вернёт 2 (Анна и Вера), вторая — 950 (только Дарья: продажа Бориса в Гомеле меньше 900).
У СУММЕСЛИМН порядок аргументов другой, чем у СУММЕСЛИ: диапазон суммирования идёт первым. Если переделываете формулу из одной в другую, переставьте аргументы — иначе результат будет неверным.
Условие «Минск или Гомель» одной функцией не задаётся — сложите две: =СЧЁТЕСЛИ(B2:B7;"Минск")+СЧЁТЕСЛИ(B2:B7;"Гомель") вернёт 5.
Среднее по условию считает СРЗНАЧЕСЛИ с теми же аргументами, что у СУММЕСЛИ: =СРЗНАЧЕСЛИ(B2:B7;"Минск";C2:C7) вернёт 1033,33… (3100 / 3).
Подводные камни
- Диапазоны разного размера.
B2:B7иC2:C10в одной формуле — неверный итог или ошибка#ЗНАЧ!у функций с «МН». Держите диапазоны одинаковыми. - Копирование вниз. Закрепляйте диапазоны
$:=СЧЁТЕСЛИ($B$2:$B$7;E2). - Лишние пробелы и числа-текст не совпадут с условием — как и в функциях поиска.
- Длинные строки. Условие длиннее 255 символов функции сравнивают неверно.