Excel и Google Workspace / Поиск и подстановка
COUNTIFS для подсчета строк по нескольким условиям
COUNTIFS считает строки, которые одновременно удовлетворяют нескольким условиям. В этой формуле учитываются только строки, где в столбце B указана Москва, а значение в столбце D больше 5000.
Формула
Обозначения
- $B2:B100$
- диапазон городов для первого условия
- $"Москва"$
- город, который должен быть указан в строке
- $D2:D100$
- диапазон числовых значений для второго условия
- $">5000"$
- условие: значение должно быть больше 5000
Условия применения
- Все диапазоны условий должны иметь одинаковую высоту и соответствовать одним и тем же строкам.
- Числовое сравнение записывается в кавычках как текстовый критерий, например ">5000".
- COUNTIFS считает строки, а не сумму значений; для суммы по тем же условиям нужна SUMIFS.
Ограничения
- Все условия соединяются логикой И, поэтому строка должна пройти каждый критерий.
- Пустые ячейки и текстовые числа могут исказить результат, если ожидается обычное числовое сравнение.
- Для сложных условий с ИЛИ формулу приходится комбинировать из нескольких COUNTIFS.
Подробное объяснение
COUNTIFS просматривает диапазоны построчно. Для каждой позиции она проверяет, равен ли город Москве и больше ли соответствующее значение в D заданного порога.
Если оба условия истинны, счетчик увеличивается на единицу. Если хотя бы одно условие не выполнено, строка не попадает в результат.
Функция возвращает количество строк, поэтому она хорошо подходит для KPI вида "сколько заявок", "сколько клиентов", "сколько просрочек". Денежный итог по тем же фильтрам нужно считать через SUMIFS.
Условие сравнения хранится в кавычках, потому что Excel воспринимает его как критерий. Если порог находится в ячейке F2, критерий записывают как ">"&F2.
В Google Sheets функция COUNTIFS работает схожим образом. Различие чаще всего проявляется в локальных разделителях аргументов и в том, как таблица распознает числа после импорта.
COUNTIFS
Если критерий записан текстом, знак сравнения помещают в кавычки: ">5000" или ">="&F2. Все диапазоны условий должны иметь одинаковую высоту и начинаться с соответствующих строк, иначе подсчет будет казаться случайным. Для проверки полезно временно включить автофильтр по тем же двум условиям и сравнить число видимых строк с результатом COUNTIFS.
Как пользоваться формулой
- Определите, какие строки нужно считать.
- Добавьте диапазон первой категории и текстовый критерий.
- Добавьте числовой диапазон и условие сравнения.
- Проверьте, что диапазоны имеют одинаковую длину.
- Сравните результат с ручной фильтрацией на нескольких строках.
Историческая справка
COUNTIFS появилась как многокритериальное развитие функции COUNTIF. Она упростила подсчет строк в таблицах, где одного фильтра уже недостаточно.
В офисных отчетах функция стала привычной для контроля статусов, сроков, сумм и категорий без построения сводной таблицы.
Ее часто используют вместе с SUMIFS: одна формула показывает количество операций, другая сумму по тем же условиям.
Такие функции сделали электронные таблицы ближе к небольшим аналитическим системам, где условия можно быстро менять прямо на листе.
С ростом количества операционных метрик COUNTIFS стала основой простых дашбордов: она считает заявки, просрочки, сделки, позиции и другие строки учета.
Историческая линия формулы
COUNTIFS является встроенной функцией Microsoft Excel. Она развивает семейство условных функций, созданных для практического анализа табличных данных. Ее значение не в новой математике, а в удобной форме записи нескольких критериев для офисных отчетов, контроля KPI и сверок.
Пример
Дано: B2:B6 содержит Москва, Москва, Казань, Москва, Москва. D2:D6 содержит 4000, 6500, 8000, 7200, 5000. Формула =COUNTIFS(B2:B6,"Москва",D2:D6,">5000") учитывает строки со значениями 6500 и 7200. Значение 5000 не подходит, потому что условие строго больше 5000. Результат: 2. Строка с Казанью и суммой 8000 тоже не учитывается, потому что не проходит первое условие. Если заменить критерий на ">=5000", последняя московская строка добавится к подсчету и результат станет 3. Так удобно проверять пороги в отчетах.
Частая ошибка
Нередко авторы отчетов ждут, что условие ">5000" включит ровно 5000, но для этого нужно писать ">=5000". Еще одна ошибка: считать, что COUNTIFS суммирует значения, хотя она возвращает только количество подходящих строк. В импортированных отчетах числа могут быть сохранены как текст, и тогда сравнение работает не так, как ожидается. Также стоит проверять лишние пробелы в текстовых категориях.
Практика
Задачи с решением
Посчитать крупные заказы
Условие. B2:B5 содержит Москва, Москва, Тула, Москва. D2:D5 содержит 5100, 4900, 9000, 12000.
Решение. COUNTIFS учитывает только московские строки со значением больше 5000. Подходят 5100 и 12000.
Ответ. 2
Включить значение ровно 5000
Условие. Нужно считать московские строки, где сумма не меньше 5000.
Решение. Заменяем критерий ">5000" на ">=5000". Тогда строка со значением 5000 тоже будет учтена.
Ответ. Используйте критерий ">=5000"
Дополнительные источники
- Microsoft Support: COUNTIFS function - https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842
- 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 и возвращает значение из третьего столбца найденной строки. Последний аргумент FALSE включает точное совпадение, поэтому формула подходит для артикулов, кодов клиентов и других ключей, где приблизительный поиск недопустим.
Excel и Google Workspace
XLOOKUP для точного поиска в Excel
XLOOKUP ищет значение из E2 в диапазоне A2:A20 и возвращает соответствующее значение из C2:C20. В отличие от VLOOKUP, диапазон поиска и диапазон результата задаются отдельно, поэтому функция спокойно ищет как вправо, так и влево.
Excel и Google Workspace
INDEX и MATCH для поиска значения слева
Связка INDEX и MATCH ищет значение E2 в C2:C20, определяет номер найденной позиции и возвращает значение с той же позиции из A2:A20. Такой прием особенно полезен, когда результат расположен левее столбца поиска.
Excel и Google Workspace
SUMIFS для суммы по нескольким условиям
SUMIFS суммирует значения из D2:D100 только по тем строкам, где город в B2:B100 равен Москве, а дата в C2:C100 не раньше 1 января 2026 года. Функция подходит для отчетов продаж, расходов и оплат с несколькими фильтрами.