Суммирование всех числовых ячеек с игнорированием ошибок в Excel

При суммировании диапазона чисел, содержащего ошибки, стандартная функция СУММ работает некорректно. Чтобы складывать только числовые значения, игнорируя ошибки, используйте функцию АГРЕГАТ или комбинацию СУММ с ЕСЛИОШИБКА.

• Суммирование чисел с игнорированием различных ошибок с помощью функции АГРЕГАТ
• Суммирование чисел с игнорированием различных ошибок с помощью функций СУММ и ЕСЛИОШИБКА
• Суммирование чисел с игнорированием конкретной ошибки с помощью функции СУММЕСЛИ

Суммирование чисел с игнорированием различных ошибок с помощью функции АГРЕГАТ

В Excel функция АГРЕГАТ позволяет суммировать диапазон ячеек, игнорируя любые ошибки. Общий синтаксис:

• 9: это аргумент «номер_функции» в функции АГРЕГАТ, указывающий на функцию суммирования;
• 6: это другой аргумент — «параметры» в функции АГРЕГАТ, используемый для игнорирования значений ошибок;
• range: диапазон ячеек, которые нужно просуммировать.

Введите приведённую ниже формулу в пустую ячейку и нажмите клавишу Enter, чтобы получить сумму:

Суммирование чисел с игнорированием различных ошибок с помощью функций СУММ и ЕСЛИОШИБКА

Вы также можете объединить функции СУММ и ЕСЛИОШИБКА, чтобы создать формульный массив, который решает эту задачу. Общий синтаксис:

• rangeДиапазон ячеек, сумму которых вы хотите получить, игнорируя ошибки.

Введите следующую формулу в пустую ячейку и нажмите клавиши Ctrl + Shift + Enter, чтобы получить правильный результат (см. скриншот):

Пояснение к формуле:

=SUM(IFERROR(C2:C10,0))

• IFERROR(C2:C10,0): функция ЕСЛИОШИБКА заменит все ошибки на нули, в результате чего вы получите массив вида: {1200;0;1500;0;1005;900;0;2005;1690}.
• SUM(IFERROR(C2:C10,0))=SUM({1200,0,1500,0,1005,900,0,2005,1690}): функция СУММ складывает все числа в массиве и возвращает итоговый результат — 8300.

Суммирование чисел с игнорированием конкретной ошибки с помощью функции СУММЕСЛИ

Оба описанных выше метода позволяют суммировать диапазон ячеек, игнорируя любые ошибки. Если в ваших данных встречается только один тип ошибки — например, #Н/Д — и вы хотите исключить именно её при суммировании, используйте функцию СУММЕСЛИ. Общий синтаксис:

• range: диапазон ячеек, которые вы хотите просуммировать;
• error_type: тип ошибки, которую вы хотите игнорировать при суммировании, например #Н/Д, #ССЫЛ! и другие.

Введите приведённую ниже формулу в пустую ячейку и нажмите клавишу Enter, чтобы получить требуемый результат:

: если при использовании этой формулы в списке данных появляются другие типы ошибок, функция СУММЕСЛИ вернёт ошибку.

Используемые связанные функции:

Другие статьи: