Посчитать сумму чисел в Excel

Мой коллега однажды спросил меня, как использовать формулы Excel, чтобы вычислить сумму абсолютных значений в указанном диапазоне. Этот вопрос довольно регулярно всплывает на форумах, и многие пользователи часто испытывают большие трудности при работе с этим, казалось бы, простым действием.

К сожалению, в Microsoft Excel не существует встроенной функции способной выполнить суммирование по модулю, поэтому Вы должны немного поработать, чтобы получить правильный ответ.

Вот наши данные:

Мы видим, что сумма чисел в диапазоне A2:A8 дает результат −60:

= -10 + 10 + 20 + -20 + 30 + -40 + -50 = -60

= 10 + 10 + 20 + 20 + 30 + 40 + 50 = 180

Вариант 1 — Использование вспомогательного столбца

На мой взгляд, лучший способ подсчета суммы абсолютных значений в Excel — использовать вспомогательный столбец. В ячейку B2 вводим формулу:

=ABS(A2)

=СУММ(B2:B8) =SUM(B2:B8)

Для подсчета суммы в строке итогов используется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL). Это универсальная функция, которая может выполнять суммирование, так же как и функция СУММ (SUM). Но есть и существенные отличия, например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) полностью игнорирует числа, которые были скрыты вручную или с помощью фильтрации. Есть еще несколько отличий, но это мало относится к теме данной статьи.

Это без сомнения очень хороший способ, но что же делать, когда необходимо все уместить в одну формулу без каких-либо вспомогательных столбцов?

Вариант 2 — Использование функции СУММ в формуле массива или СУММПРОИЗВ

Использовать формулу массива или СУММПРОИЗВ (SUMPRODUCT) для решения такой задачи — очень грубый подход!

Функция СУММ в формуле массива:

=СУММ(ABS(A2:A8)) =SUM(ABS(A2:A8))

Формула с СУММПРОЗВ:

=СУММПРОИЗВ(ABS(A2:A8)) =SUMPRODUCT(ABS(A2:A8))

Вариант 3 — Использование СУММЕСЛИ

Думаю, что данный подход самый удобный из всех ранее перечисленных. С помощью функции СУММЕСЛИ (SUMIF) значения делятся на 2 массива: с положительными и отрицательными числами и суммируются. Ноль по понятным причинам игнорируется. Затем мы просто вычитаем из положительной суммы отрицательную (т.е. суммируем их). Формула выглядит вот так:

=СУММЕСЛИ(A2:A8,">0")-СУММЕСЛИ(A2:A8,"<0") =SUMIF(A2:A8,">0")-SUMIF(A2:A8,"<0")

=СУММ(СУММЕСЛИ(A2:A8,{">0","<0"})*{1,-1}) =SUM(SUMIF(A2:A8,{">0","<0"})*{1,-1})

Если мы возьмем первый вариант формулы, получим следующее:

= 60-(-120) = 180

= СУММ({60,-120}*{1,-1}) = СУММ({60,120}) = 180