Подсчет количества равных ячеек

Для того, чтобы подсчитать количество ячеек равных определенному значению, вы можете использовать функцию СЧЁТЕСЛИ.

В показанном примере, G4 содержит следующую формулу:

Функция СЧЁТЕСЛИ является полностью автоматической - она подсчитывает количество ячеек в диапазоне, которые соответствуют поставленным критериям.

Для диапазона, мы используем D5: D11, который содержит цвета. Для критериев, мы просто используем "красный".

СЧЁТЕСЛИ возвращает количество значений в D5: D11, которые равны "красный".

Примечание: когда текстовые значения подставляются непосредственно в качестве критериев, они должны быть заключены в двойные кавычки (" "). Если у вас есть критерии в другой ячейке, вы можете указать адрес ячейки в качестве критерия без кавычек.

Для подсчета ячеек, которые содержат определенный текст с учетом регистра, вы можете применить формулу, которая использует функцию СОВПАД наряду с СУММПРОИЗВ. 

В примере, есть список имен в B5:B11 . В диапазоне E5:E8, существует еще один список имен, где дубликаты удалены. Для того, чтобы получить подсчет "алена", вы можете использовать следующую формулу:

Сочетание этих формул даст ответ 3, так как есть 3 записи "алена" (в нижнем регистре) в диапазоне B5:B11.

СОВПАД функция сравнивает аргументы, текст1 и текст2. Когда эти значения точно соответствуют (соблюдая регистр), СОВПАД возвращает ИСТИНА. В этом случае мы даем СОВПАД диапазон для текст2, поэтому она становится формулой массива и возвращает массив истинных и ложных значений:

Каждая ИСТИНА представляет собой точное совпадение "алена" в B5:B11

Двойной дефис (технически: двойной одинарный) преобразует истинные и ложные значения в 1 и 0, которые выглядят следующим образом:

Наконец, СУММПРОИЗВ просто суммирует значения в массиве и возвращает 3. Поскольку СУММПРОИЗВ может обрабатывать массивы изначально, для этого не обязательно использовать Ctrl + Shift + Enter, чтобы ввести эту формулу.

Для того, чтобы подсчитать количество ячеек, равное либо одному значению, либо другому, можно использовать формулу, которая содержит функцию СЧЁТЕСЛИ дважды. 

В примере, активная ячейка содержит эту формулу:

СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые соответствуют критериям, входящие в комплект поставки. В этом случае критерием первого СЧЁТЕСЛИ является "яблоки" и критерием второго СЧЁТЕСЛИ является "груши". Первый СЧЁТЕСЛИ считает количество ячеек в B5: B10 равным "яблоки". Второй СЧЁТЕСЛИ считает количество ячеек в B5: B10 равным "груши". Эти два результата суммируются и сумма возвращается как результат формулы.

Обратите внимание, что текстовые значения в критериях СЧЁТЕСЛИ должны быть заключены в кавычки (" "). Также отметим, что СЧЁТЕСЛИ не чувствительна к регистру. В примере, будут учитываться слова "яблоки" и "груши" в любой комбинации букв верхнего и нижнего регистра.

Если вы подсчитываете ячейки, которые содержат числовое значение, нет необходимости добавлять кавычки. Например, если в приведенном выше примере вы хотите посчитать ячейки, которые содержат ноль или 1, то подойдет формула:

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

В показанном примере, ячейка G4 содержит следующую формулу:

Примечание СЧЁТЕСЛИ не чувствительна к регистру.

СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые отвечают критериям. Когда вы даете СЧЁТЕСЛИ диапазон ячеек в качестве критериев, он возвращает массив чисел как результат, где каждое число представляет собой количество одной вещи в диапазоне. В этом случае диапазон ( D5:D7) содержит 3 значения, поэтому СЧЁТЕСЛИ возвращает 3 результаты в массиве как:

так как значения "ручка", "пульт" и "собака" все появляются один раз в диапазоне B5:B10. Для обработки этого массива, мы используем функцию СУММПРОИЗВ, которая предназначена для работы с массивами. СУММПРОИЗВ просто суммирует элементы в массиве и возвращает результат, 3.

С ограниченным числом значений, вы можете использовать константу массива в формуле с СУММ, как:

Но если вы используете ссылки на ячейки в критериях, вам необходимо ввести как формулу массива, либо перейти к СУММПРОИЗВ.