Как в эксель суммировать только положительные значения

Обновлено: 07.07.2024

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

Считайте только положительные или отрицательные числа в диапазоне с формулами

Здесь функция СЧЁТЕСЛИ может помочь вам быстро подсчитать, сколько положительных или отрицательных чисел в диапазоне.

1. Чтобы подсчитать только положительные значения, введите эту формулу в пустой ячейке. = СЧЁТЕСЛИ ($ A $ 1: $ D $ 7, "> 0") в него, см. снимок экрана:

2, Затем нажмите Enter key, и все положительные числа были подсчитаны, и результат отображается в ячейке. Смотрите скриншот:

Ноты:

1. Если вы хотите подсчитать количество только отрицательных значений, примените эту формулу = СЧЁТЕСЛИ ($ A $ 1: $ D $ 7, "<0") .

2. В приведенной выше формуле A1: D7 диапазон данных, который вы хотите использовать.

Суммируйте только положительные или отрицательные числа в диапазоне с формулами

В Excel функция СУММЕСЛИ может помочь вам суммировать только положительные или отрицательные значения. Вы можете применить эту формулу так:

1. Введите эту формулу = СУММЕСЛИ ($ A $ 1: $ D $ 7, "> 0") в пустую ячейку, см. снимок экрана:

2, Затем нажмите Enter key, и суммированы только все положительные числа.

Ноты:

1. Чтобы суммировать все отрицательные числа только в диапазоне, используйте эту формулу = СУММЕСЛИ ($ A $ 1: $ D $ 7; "<0") .

2. В приведенной выше формуле A1: D7 диапазон данных, который вы хотите использовать.

В этом посте представлены две формулы на основе функции СУММПРОИЗВ и ИЗМЕНИТЬ, которые помогут вам подсчитывать и суммировать только целые числа в диапазоне ячеек в Excel.

Как считать в Excel только целые числа

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

Общая формула

=SUMPRODUCT(--(MOD(range,1)=0))

аргументы

Диапазон: Диапазон ячеек, в которых вы хотите посчитать целые числа.

Как пользоваться этой формулой?

1. Выберите пустую ячейку, чтобы разместить результат.

2. Введите в нее приведенную ниже формулу и нажмите Enter ключ для получения результата.

=SUMPRODUCT(--(MOD(E3:E17,1)=0))

Как работает эта формула?

=SUMPRODUCT(--(MOD(E3:E17,1)=0))

1) МОД (E3: E17,1): Здесь используйте функцию MOD для проверки целых чисел в E3: E17. Он возвращает остатки после каждого числа в диапазоне E3: E17 делится на 1: .
2) =0: Сравните каждое число в массиве с 0 и вернет массив ИСТИНА ЛОЖЬ, например: . В этом массиве значения ИСТИНА представляют собой целое число, а значения ЛОЖЬ представляют собой десятичное число.
3) - : Эти два знака минус преобразуют «ИСТИНА» в 1 и «ЛОЖЬ» в 0. Здесь вы получите новый массив как .
4) SUMPRODUCT : Функция СУММПРОИЗВ суммирует все числа в массиве и возвращает окончательный результат как 10.

Как суммировать только целые числа в Excel

Чтобы суммировать только целые числа в указанном диапазоне ячеек, вы можете сделать следующее.

В качестве примера возьмем приведенную выше таблицу продаж продуктов.

Общая формула

=SUMPRODUCT(--(MOD(range,1)=0)*range)

аргументы

Диапазон: Диапазон ячеек, по которым вы хотите суммировать целые числа.

Как пользоваться этой формулой?

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

=SUMPRODUCT(--(MOD(E3:E17,1)=0)*E3:E17)

Как работает эта формула?

=SUMPRODUCT(--(MOD(E3:E17,1)=0)*E3:E17)

1) МОД (E3: E17,1): Здесь используйте функцию MOD для проверки целых чисел в E3: E17. Он возвращает остатки после каждого числа в диапазоне E3: E17 делится на 1: .
2) =0: Сравните каждое число в массиве с 0 и вернет массив ИСТИНА ЛОЖЬ, например: . В этом массиве значения ИСТИНА представляют собой целое число, а значения ЛОЖЬ представляют собой десятичное число.
3) - : Эти два знака минус преобразуют «ИСТИНА» в 1 и «ЛОЖЬ» в 0. Здесь вы получите новый массив как .
4) SUMPRODUCT(*E3:E17): Здесь каждое число в массиве умножает числа в диапазоне E3: E17, чтобы получить следующий результат: СУММПРОИЗВ (0; 2028; 900; 3944; 2757; 1231; 0; 2313; 0; 3152; 1361; 0; 1980; 2579 ; 0), а затем функция СУММПРОИЗВ суммирует все числа в массиве и возвращает окончательный результат как 22245.

Связанные функции

Функция СУММПРОИЗВ в Excel
Функцию СУММПРОИЗВ в Excel можно использовать для умножения двух или более столбцов или массивов, а затем получения суммы произведений.

Функция Excel MOD
Функция Excel MOD возвращает остаток от деления числа на делитель. Результат имеет тот же знак, что и делитель.

Родственные формулы

Подсчитайте числа, которые начинаются с определенного числа
В этом руководстве представлена формула на основе функций СУММПРОИЗВ и ВЛЕВО для подсчета количества ячеек, содержащих числа, начинающиеся с определенного числа в Excel.

Подсчитайте несколько критериев с логикой НЕ в Excel
Эта статья покажет вам, как подсчитать количество ячеек с несколькими критериями с логикой НЕ в Excel.

Подсчет вхождений определенного текста во всей книге Excel
В этой статье будет продемонстрирована формула, основанная на функциях СУММПРОИЗВ, СЧЁТЕСЛИ и КОСВЕННО, для подсчета вхождений определенного текста во всей книге.

Подсчитайте числа, где n-я цифра равна заданному числу
В этом руководстве представлена формула, основанная на функциях СУММПРОИЗВ и СРЕДНЕЕ, для подсчета чисел, где n-я цифра равна заданному числу в Excel.

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.

Имеем таблицу по продажам, например, следующего вида:

Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".

Способ 1. Функция СУММЕСЛИ, когда одно условие

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:

Жмем ОК и вводим ее аргументы:

Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.
Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.
Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. - при необходимости.

Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться - см. следующие способы.

Способ 3. Столбец-индикатор

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1, иначе - 0. Формула, которую надо ввести в этот столбец очень простая:

=(A2="Копейка")*(B2="Григорьев")

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:

Способ 4. Волшебная формула массива

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

=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)

После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

Способ 4. Функция баз данных БДСУММ

В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:

В Excel функция СУММЕСЛИ может помочь суммировать ячейки на основе одного критерия, в этой статье будет объяснен синтаксис этой функции и представлены некоторые примеры использования функции СУММЕСЛИ.

Синтаксис:

Синтаксис функции СУММЕСЛИ в Excel:

Аргументы:

range : Необходимые. Диапазон ячеек, к которым вы хотите применить критерии.
criteria : Необходимые. Критерии, используемые для определения, какие ячейки необходимо добавить. Критерий может быть текстом, числом, датой, логическим выражением, ссылкой на ячейку или другой функцией.
Внимание: Любые текстовые критерии или критерии, содержащие математические символы, должны быть заключены в двойные кавычки. Если критерий числовой, двойные кавычки не требуются.
sum_range :Необязательный. Это диапазон ячеек для суммирования. Если этот параметр опущен, в качестве диапазон_суммы.

Вернуть:

Возврат суммирования для списка чисел на основе определенных критериев.

Примеры:

Пример 1. Суммирование, если значения больше, меньше, равны или других операторов сравнения

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

Предположим, у вас есть таблица, которая содержит столбцы Name, Qty и Price, как показано на скриншоте ниже, как вы можете получить общую цену, если Qty больше 250?

Пожалуйста, примените приведенную ниже формулу в пустую ячейку:

Советы: В приведенной выше формуле:
B2: B8: диапазон ячеек, в которых будет выполняться поиск критериев.
"> 250": это условие, по которому вы хотите суммировать значения ячеек.
C2: C8: относится к суммируемому диапазону.

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

Внимание: Вы можете использовать другие операторы сравнения, например, больше или равно (> =), меньше (<), меньше или равно (<=), равно (=), не равно (<>), чтобы ваша потребность.

Пример 2: формулы СУММЕСЛИ с текстовыми критериями

Если вы хотите суммировать значения ячеек в зависимости от того, существует ли конкретное текстовое значение в другом столбце, функция СУММЕСЛИМН также может оказать вам услугу.

Случай 1. Суммируйте значения ячеек, если они содержат определенный текст в другом столбце.

В качестве примера возьмите данные ниже, чтобы получить все общее количество, если имя Дэвид в столбце A.

Введите или скопируйте приведенную ниже формулу в пустую ячейку:

Советы: В приведенной выше формуле:
A2: A8: диапазон ячеек, в которых будет выполняться поиск критериев.
«Давид»: это условие, по которому вы хотите суммировать значения ячеек.
B2: B8: диапазон суммируемых ячеек.

Внимание: Вы также можете использовать ссылку на ячейку, чтобы заменить условие в приведенной выше формуле следующим образом: =SUMIF(A2:A8, D2, B2:B8) .

Случай 2: значения ячеек суммы не равны определенному значению

Если вы хотите суммировать ячейки, когда ячейки не равны определенному значению, например, чтобы просуммировать все количество людей, кроме Дэвида, как вы могли бы это сделать?

Пожалуйста, примените приведенную ниже формулу в пустую ячейку:

Советы: В приведенной выше формуле:
A2: A8: диапазон ячеек, в которых будет выполняться поиск критериев.
«<> Дэвид»: это условие, по которому вы хотите суммировать значения ячеек.
B2: B8: диапазон суммируемых ячеек.

Внимание: Вы также можете использовать ссылку на ячейку, чтобы заменить условие в приведенной выше формуле следующим образом: =SUMIF(A2:A8,"<>"&D2, B2:B8) , вы должны использовать двойные кавычки (""), чтобы заключить операторы сравнения, и использовать амперсанд (&), чтобы объединить ссылку на ячейку. Смотрите скриншот:

Пример 3: формулы СУММЕСЛИ с подстановочными знаками

Иногда вам может потребоваться суммировать значения ячеек на основе частичного совпадения текста, а не точного совпадения. В этом случае вы можете комбинировать подстановочные знаки звездочку (*) или вопросительный знак (?) С функцией СУММЕСЛИ.

Звездочка (*): Представляет любое количество символов

Вопросительный знак (?): Представляет любой отдельный символ в определенной позиции

Случай 1: Сумма значений на основе частичного совпадения с символом звездочки (*)

Например, у меня есть таблица, показанная на следующем скриншоте, теперь я хочу суммировать общие заказы продукта, текст которого содержит Kutools.

Здесь введите следующую формулу в пустую ячейку:

Затем нажмите Enter ключ, чтобы получить результат ниже:

Заметки:

1. Если вы хотите суммировать значения ячеек, которые начинаются с Kutools, используйте эту формулу: = СУММЕСЛИ (A2: A8; «Kutools *»; B2: B8) .

2. Чтобы суммировать значения ячеек, если соответствующая ячейка в столбце A заканчивается текстом Kutools, используйте эту формулу: = СУММЕСЛИ (A2: A8; «* Kutools»; B2: B8) .

3. Вы также можете использовать ссылку на ячейку, чтобы заменить условие в приведенной выше формуле следующим образом: =SUMIF(A2:A8, "*"&D2&"*", B2:B8) . Вы должны использовать двойные кавычки (""), чтобы заключить звездочку, и использовать амперсанд (&), чтобы объединить ссылку на ячейку. Смотрите скриншот:

Случай 2. Суммируйте значения с определенным количеством символов с вопросительным знаком (?)

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

Чаевые: В этой формуле: один вопросительный знак обозначает один единственный символ, пять вопросительных знаков обозначают 5 символов.

А затем нажмите Enter key, вы получите нужный результат:

Пример 4: Суммирование значений ячеек, соответствующих пустым или непустым ячейкам

В Excel функция СУММЕСЛИ может также помочь суммировать значения в списке, если соседняя ячейка является пустой или непустой ячейкой.

Случай 1. Суммирование значений ячеек, если соседняя ячейка пуста

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

Затем нажмите Enter ключ, чтобы получить результат общей цены, если в столбце A есть пустые ячейки, см. снимок экрана:

Случай 2: Суммирование значений ячеек, если соседняя ячейка не пустая.

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

А затем нажмите Enter key, все значения, в которых соседние ячейки содержат значения, были суммированы, см. снимок экрана:

Пример 5: СУММЕСЛИ с датами в Excel

Обычно вы также можете использовать функцию СУММЕСЛИ для суммирования значений на основе определенной даты в Excel.

Случай 1. Сумма значений, если дата является текущей датой или меньше, больше сегодняшней.

Чтобы суммировать значения ячеек, дата которых является текущей датой, вы можете использовать следующую формулу:

Затем нажмите Enter key, суммируются все ячейки, соответствующие сегодняшней дате. Смотрите скриншот:

Заметки:

1. Чтобы суммировать значения, если соответствующая дата меньше или раньше сегодняшней, используйте эту формулу: = СУММЕСЛИ (B2: B8; "<" & СЕГОДНЯ (); C2: C8) .

2. Чтобы суммировать значения, если соответствующая дата больше или позже сегодняшней, используйте эту формулу: = СУММЕСЛИ (B2: B8; ">" & СЕГОДНЯ (); C2: C8) .

Случай 2: сумма значений между двумя заданными датами

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

Пожалуйста, примените следующую формулу:

=SUMIF(B2:B8, ">=6/15/2019", C2:C8) - SUMIF(B2:B8, ">=7/31/2019", C2:C8)

А затем нажмите клавишу Enter, было вычислено общее значение между двумя заданными датами, см. Снимок экрана:

Внимание: Вы также можете использовать ссылку на ячейку, чтобы заменить условие в приведенной выше формуле следующим образом: = СУММЕСЛИ (B2: B8, "> =" & E2, C2: C8) - СУММЕСЛИ (B2: B8, "> margin-left: 13px;">

Пример 6: Сумма значений в нескольких столбцах в Excel

Эта функция СУММЕСЛИ может также помочь вам суммировать значения в нескольких столбцах на основе одного условия. Например, если у вас есть диапазон данных, как показано на скриншоте ниже, то теперь, чтобы получить общие значения продукта KTE за четыре месяца - январь, февраль, март и апрель. Как вы могли это сделать?

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

Пожалуйста, примените приведенную ниже формулу в пустую ячейку:

=SUM(SUMIF(A2:A8,G2,B2:B8), SUMIF(A2:A8,G2,C2:C8), SUMIF(A2:A8,G2,D2:D8),SUMIF(A2:A8,G2,E2:E8))

Затем нажмите клавишу Enter, чтобы получить правильный результат, который вам нужен, см. Снимок экрана:

Советы: В приведенной выше формуле:
A2: A8: диапазон ячеек, в которых будет выполняться поиск критериев.
G2: это условие, по которому вы хотите суммировать значения ячеек.
B2: E8: диапазон суммируемых ячеек.

Другие статьи о функции СУММЕСЛИ:

Сумма соответствующих значений с той же датой в Excel
Вы когда-нибудь пытались суммировать соответствующие значения на основе той же даты из другого столбца в Excel? Предположим, у меня есть следующий диапазон данных (A1: B13), столбец A - это диапазон дат, содержащий несколько повторяющихся дат, а столбец B имеет номер заказа, теперь мне нужно сложить числа на основе той же даты в столбце A. Как быстро и легко закончить в Excel?

Ячейки Sumif не равны определенному значению в Excel
В этой статье я расскажу о том, как суммировать ячейки, если ячейки в другом столбце не равны определенному значению на листе Excel.

Дата Sumif меньше / больше сегодняшней в Excel
Предположим, у нас есть таблица объемов продаж с плановыми и фактическими объемами на каждый день. Теперь нам необходимо подсчитать даты, произошедшие до сегодняшнего дня, и вычислить общие объемы планирования до сегодняшнего дня, чтобы динамически сравнивать объемы планирования и фактические объемы. Здесь я применю функцию СУММЕСЛИ, чтобы быстро сделать это в Excel.

Сумиф с одним или несколькими критериями в Excel
В Excel суммирование значений на основе одного или нескольких критериев - обычная задача для большинства из нас, функция СУММЕСЛИ может помочь нам быстро суммировать значения на основе одного условия, а функция СУММЕСЛИМН помогает нам суммировать значения с несколькими критериями. В этой статье я опишу, как суммировать по одному или нескольким критериям в Excel?

Sumif на основе частичного совпадения в Excel
В качестве примера возьмем данные ниже. Я хочу просуммировать значения ячеек в столбце B, если в столбце A содержится частичный текст «KTE». Как этого добиться? В этой статье я расскажу о том, как суммировать значения ячеек на основе частичного совпадения в листе Excel.

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Читайте также: