Суммирование только тех ячеек, которые содержат формулы, в Excel

Допустим, у вас в Excel есть список значений, часть из которых рассчитана с помощью формул. Как просуммировать только те ячейки, которые содержат формулы, как показано на скриншоте ниже?

Суммирование только тех ячеек, которые содержат формулы, в Excel

В Excel можно создать простую формулу с использованием функций СУММПРОИЗВ (SUMPRODUCT) и ЕСЛИФОРМУЛА (ISFORMULA), чтобы просуммировать только те ячейки в заданном диапазоне, которые содержат формулы. Общий синтаксис формулы выглядит следующим образом:

• range: диапазон — диапазон данных, из которого нужно просуммировать ячейки, содержащие формулы.

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

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

=SUMPRODUCT(B2:B12*ISFORMULA(B2:B12)):

• ЕСЛИФОРМУЛА(B2:B12): эта функция проверяет, содержит ли ячейка формулу. Если да — возвращается ИСТИНА (TRUE), если нет — ЛОЖЬ (FALSE). В результате вы получите массив следующего вида: {ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА}.
• B2:B12*ЕСЛИФОРМУЛА(B2:B12)= {50;150;35;45;200;80;95;45;50;30;108}*{ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА}: при математической операции значения ИСТИНА и ЛОЖЬ автоматически преобразуются в 1 и 0 соответственно. В результате умножения двух массивов получаем: {50;150;35;45;200;80;95;45;50;30;108}*{0;1;0;0;1;0;0;1;0;0;1} = {0;150;0;0;200;0;0;45;0;0;108}.
• SUMPRODUCT(B2:B12*ISFORMULA(B2:B12))= SUMPRODUCT({0;150;0;0;200;0;0;45;0;0;108}): наконец, функция СУММПРОИЗВ (SUMPRODUCT) суммирует все значения в массиве и возвращает результат — 503.

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

Если вам нужно просуммировать значения, пропуская ячейки с формулами, добавьте функцию НЕ (NOT) в приведённую выше формулу следующим образом:

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

Дополнительные статьи: