Базовая формула
= СЧЁТЕСЛИ (диапазон; значение)
Для того, чтобы подсчитать количество ячеек равных определенному значению, вы можете использовать функцию СЧЁТЕСЛИ.
В показанном примере, G4 содержит следующую формулу:
= СЧЁТЕСЛИ (D5: D11; «красный»)
Функция СЧЁТЕСЛИ является полностью автоматической — она подсчитывает количество ячеек в диапазоне, которые соответствуют поставленным критериям.
Для диапазона, мы используем D5: D11, который содержит цвета. Для критериев, мы просто используем «красный».
СЧЁТЕСЛИ возвращает количество значений в D5: D11, которые равны «красный».
Примечание: когда текстовые значения подставляются непосредственно в качестве критериев, они должны быть заключены в двойные кавычки (» «). Если у вас есть критерии в другой ячейке, вы можете указать адрес ячейки в качестве критерия без кавычек.
Количество ячеек, равных с учетом регистра
= СУММПРОИЗВ (— СОВПАД (значение; диапазон))
Для подсчета ячеек, которые содержат определенный текст с учетом регистра, вы можете применить формулу, которая использует функцию СОВПАД наряду с СУММПРОИЗВ.
В примере, есть список имен в B5:B11 . В диапазоне E5:E8, существует еще один список имен, где дубликаты удалены. Для того, чтобы получить подсчет «алена», вы можете использовать следующую формулу:
= СУММПРОИЗВ ((—СОВПАД(D5;B5:B11)))
Сочетание этих формул даст ответ 3, так как есть 3 записи «алена» (в нижнем регистре) в диапазоне B5:B11.
СОВПАД функция сравнивает аргументы, текст1 и текст2. Когда эти значения точно соответствуют (соблюдая регистр), СОВПАД возвращает ИСТИНА. В этом случае мы даем СОВПАД диапазон для текст2, поэтому она становится формулой массива и возвращает массив истинных и ложных значений:
{ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА}
Каждая ИСТИНА представляет собой точное совпадение «алена» в B5:B11
Двойной дефис (технически: двойной одинарный) преобразует истинные и ложные значения в 1 и 0, которые выглядят следующим образом:
{0;0;0;0;1;1;1}
Наконец, СУММПРОИЗВ просто суммирует значения в массиве и возвращает 3. Поскольку СУММПРОИЗВ может обрабатывать массивы изначально, для этого не обязательно использовать Ctrl + Shift + Enter, чтобы ввести эту формулу.
Количество ячеек, равных x или y
=СЧЁТЕСЛИ (диапазон;значение1) + СЧЁТЕСЛИ (диапазон;значение2)
Для того, чтобы подсчитать количество ячеек, равное либо одному значению, либо другому, можно использовать формулу, которая содержит функцию СЧЁТЕСЛИ дважды.
В примере, активная ячейка содержит эту формулу:
=СЧЁТЕСЛИ(B5:B10; «яблоки»)+СЧЁТЕСЛИ(B5:B10; «груши»)
СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые соответствуют критериям, входящие в комплект поставки. В этом случае критерием первого СЧЁТЕСЛИ является «яблоки» и критерием второго СЧЁТЕСЛИ является «груши». Первый СЧЁТЕСЛИ считает количество ячеек в B5: B10 равным «яблоки». Второй СЧЁТЕСЛИ считает количество ячеек в B5: B10 равным «груши». Эти два результата суммируются и сумма возвращается как результат формулы.
Обратите внимание, что текстовые значения в критериях СЧЁТЕСЛИ должны быть заключены в кавычки (» «). Также отметим, что СЧЁТЕСЛИ не чувствительна к регистру. В примере, будут учитываться слова «яблоки» и «груши» в любой комбинации букв верхнего и нижнего регистра.
Если вы подсчитываете ячейки, которые содержат числовое значение, нет необходимости добавлять кавычки. Например, если в приведенном выше примере вы хотите посчитать ячейки, которые содержат ноль или 1, то подойдет формула:
= СЧЁТЕСЛИ (rng; 0) + СЧЁТЕСЛИ (rng; 1)
Количество ячеек, равных одному из многих значений
= СУММПРОИЗВ ( СЧЁТЕСЛИ (диапазон; значение))
Для того, чтобы подсчитать количество ячеек, равное одному из многих значений, вы можете использовать функцию СЧЁТЕСЛИ внутри СУММПРОИЗВ. В общей форме формулы (выше) rng представляет собой диапазон ячеек, и значение представляют значениями для подсчета.
В показанном примере, ячейка G4 содержит следующую формулу:
=СУММПРОИЗВ(СЧЁТЕСЛИ( B5:B10; D5:D7))
Примечание СЧЁТЕСЛИ не чувствительна к регистру.
СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые отвечают критериям. Когда вы даете СЧЁТЕСЛИ диапазон ячеек в качестве критериев, он возвращает массив чисел как результат, где каждое число представляет собой количество одной вещи в диапазоне. В этом случае диапазон ( D5:D7) содержит 3 значения, поэтому СЧЁТЕСЛИ возвращает 3 результаты в массиве как:
= СУММПРОИЗВ ({1;1;1})
так как значения «ручка», «пульт» и «собака» все появляются один раз в диапазоне B5:B10. Для обработки этого массива, мы используем функцию СУММПРОИЗВ, которая предназначена для работы с массивами. СУММПРОИЗВ просто суммирует элементы в массиве и возвращает результат, 3.
С массивом констант
С ограниченным числом значений, вы можете использовать константу массива в формуле с СУММ, как:
= СУММ (СЧЁТЕСЛИ (B5: B10, { «красный», «зеленый», «синий»}))
Но если вы используете ссылки на ячейки в критериях, вам необходимо ввести как формулу массива, либо перейти к СУММПРОИЗВ.