Получение условного количества ячеек, содержащих значения (Microsoft Excel)

Формула = СУММЕСЛИ (B1: B100, «Текущий», D1: D100) предоставляет сумму значений в столбце D при условии, что соответствующая ячейка в столбце B содержит текст «Текущий». Однако на самом деле Кеннету нужно подсчитать значения в столбце D, когда столбец B содержит «Current». (Количество значений в D вполне может отличаться от количества экземпляров «Current» в B.) Ему хотелось бы, чтобы это было так же просто, как изменение SUMIF на COUNTIF, но это приводит к ошибке.

Причина возникновения ошибки заключается в том, что СУММЕСЛИ требует трех параметров, а СЧЁТЕСЛИ — только двух. Таким образом, если вы просто замените СУММЕСЛИ на СЧЁТЕСЛИ, вы получите ошибку. Вместо этого вы можете использовать СЧЁТЕСЛИ следующим образом:

Это не приведет к ошибке, но и не даст правильного ответа. Кеннет хочет подсчитать количество ячеек, содержащих значения в диапазоне D1: D100, но только если соответствующая ячейка в столбце B содержит текст «Текущая». Формула СЧЁТЕСЛИ даже не учитывает столбец D; он считает только количество ячеек в диапазоне B1: B100, которые содержат слово «Текущий».

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

Эта формула подсчитывает количество значений в D1: D100, которые больше нуля. (Ну, он также считает их, только если B1: B100 содержит «текущий».) Это работает, потому что пустые ячейки или ячейки, содержащие текст, считаются СЧЁТЕСЛИМН как эквивалентные 0. Также обратите внимание, что функция нечувствительна к регистру когда дело доходит до сопоставления: «текущий»

также будет соответствовать «Текущему» или любой комбинации прописных и строчных букв в слове.

Если ваши ячейки могут содержать отрицательные значения, вам следует попробовать вместо этого следующий вариант:

Недостатком является то, что этот метод также включает в счет любые ячейки, содержащие текст.

Вы также можете использовать следующую формулу:

Опять же, это включает текстовые значения в счетчике. Если вы хотите игнорировать текстовые значения, тогда отлично подойдет этот вариант:

Также можно использовать следующую формулу, если вы вводите ее как формулу массива (нажмите Ctrl + Shift + Enter):

Есть и другие способы получить необходимую сумму. Один из методов — использовать функцию DCOUNT (которая подсчитывает на основе нескольких критериев), но этот подход требует дополнительных настроек, чем уже описанные формулы.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13433) применим к Microsoft Excel 2007, 2010, 2013 и 2016.