Количество ячеек не соответствующих значениям X и Y

Что делать если в некотором диапазоне, необходимо посчитать все значения не равные значениям X или Y?  Как посчитать количество ячеек не соответствующих значениям X и Y? Ответ прост, на помощь придет хорошо знакомая функция «СЧЁТЕСЛИМН».

Исходные данные

В диапазоне перечислены названия продуктов, пусть это будут фрукты. Значения могут, как повторяться, так и быть уникальными. Диапазон значений будет располагаться, начиная с ячейки B2 и до ячейки В12. В диапазоне будут следующие значения – яблоки, груши, слива, персики, яблоки, вишня, лимоны, мандарины, ананасы, груши.

Формула

Так как же посчитать, сколько из наших десяти ячеек не соответствуют значениям «Яблоки» и «Груши». Чем отличается функция «СЧЁТЕСЛИ» от «СЧЁТЕСЛИМН»? Ответ простой, количеством поддерживаемых условий, если «СЧЁТЕСЛИ» использует только одно условие то «СЧЁТЕСЛИМН» поддерживает несколько условий. В целях нашей задачи не стоит подсчет количества значений, которые не содержатся в другом диапазоне. У нас простое условие, проверяем только два значения. По этому не будем изобретать велосипед, и обойдемся единственной функцией «СЧЁТЕСЛИМН». Таким образом, формула будет выглядеть как:

=СЧЁТЕСЛИМН(В2:В12; «<>яблоки»; В2:В12; «<>груши»)

Всё просто проверяем диапазон и подсчитываем только те значения, которые не равны «яблоки» и «груши». Не стоит забывать, что в функции «СЧЁТЕСЛИМН» между условиями выполняется логическая операция «И». То есть подсчитываться будут только те значения для которых выполняются все условия. В нашем примере не равно (знак <>) «яблоки» и не равно «груши».

Условие из ячейки

Можно немного изменить условие в формуле, так что бы проверяемые значения брались из другой/других ячеек. Запишем значение «яблоки» в ячейку А2, а значение «груши» в ячейку А3. Теперь изменим формулу так что бы использовались значения из ячеек.

=СЧЁТЕСЛИМН(В2:В12; «<>»&A2; В2:В12; «<>»&A3)

Обратите внимание, логическое условие всё равно заключено в кавычки. А что бы в условие подставлялось значение из ячейки, перед ее именем ставится знак «амперсанд» (&).