Суммирование ячеек в excel по условию: Суммирование значений с учетом нескольких условий

Пример функции СУММЕСЛИ для суммирования в Excel по условию

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

Примеры использования функции СУММЕСЛИ в Excel

Пример 1. В таблице Excel записаны члены геометрической прогрессии. Какую часть (в процентах) составляет сумма первых 15 членов прогрессии от общей суммы ее членов.

Вид исходной таблицы данных:

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

Описание аргументов:

• A3:A22 – диапазон ячеек, содержащих порядковые номера членов прогрессии, относительно которых задается критерий суммирования;
• «<=15» – логическое выражение, являющееся критерием суммирования;
• B3:B22 – диапазон ячеек, содержащих значения членов прогрессии.

Полученный результат:

Доля в процентах первых 15-ти значений (75% — от количества всех 20-ти значений) данной геометрической прогрессии составляет всего лишь 0,41%.



Сумма ячеек с определенным значением в Excel

Пример 2. В таблице Excel указаны данные о работе двух продавцов небольшого магазина. Определить, какой из сотрудника принес больший доход за 19 рабочих дней).

Исходная таблица имеет следующий вид:

Для расчета используем функцию в формуле:

Функция ЕСЛИ выполняет проверку возвращаемых значений функциями СУММЕСЛИ с условиями проверки «Иванов» и «Петров» соответственно и возвращает текстовую строку с фамилией продавца, суммарная прибыль которого оказалась больше.

В итоге получим следующее значение:

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

Пример 3. В таблице указаны данные о зарплате сотрудника на протяжении 12 месяцев прошлого года. Рассчитать доходы работника за весенние месяцы.

Вид таблицы данных:

Весенними месяцами являются месяца с номерами 3, 4 и 5. Для расчета используем формулу:

Сумма зарплат с 6-го по 12-й месяц является подмножеством множества суммы зарплат с 3-го по 12-й месяц. Разница этих сумм является искомой величиной – суммой зарплат за весенние месяцы:

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

Для расчетов используем следующую формулу:

В результате получим:

Особенности использования функции СУММЕСЛИ в Excel

Функция СУММЕСЛИ имеет следующий синтаксис:

=СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])

Описание аргументов:

• диапазон – обязательный для заполнения аргумент, принимающий ссылку на диапазон ячеек с данными, в отношении которых применяется определенный критерий. В ячейках данного диапазона могут содержаться имена, текстовые строки, данные ссылочного типа, числовые значения, логические ИСТИНА или ЛОЖЬ, даты в формате Excel. Если данный диапазон также является диапазоном суммирования (третий аргумент опущен), на итоговый результат не влияют пустые ячейки и ячейки, содержащие текстовые данные.
• условие – обязательный для заполнения аргумент, который может быть указан в виде числа, текстовой строки, логического выражения, результата выполнения какой-либо функции. Переданное в качестве данного аргумента значение или выражение является критерием суммирования рассматриваемой функции.
• [диапазон_суммирования] – необязательный для заполнения аргумент, принимающий ссылку на диапазон ячеек, содержащих числовые значения, для которых будет рассчитана сумма с учетом критерия суммирования (условие).

Примечания:

1. Если третий необязательный аргумент явно не указан, диапазон ячеек, указанных в качестве первого аргумента, также является диапазоном суммирования.
2. Условия, представленные в виде текстовой строки или выражения, содержащего символы «>», «<», «=», должны быть указаны в кавычках. Если аргумент условие представлен в виде числа, кавычки не требуются.
3. Если аргумент условие указан в виде текстовой строки, можно использовать жесткий критерий (точное совпадение с указанной подстрокой) или выполнить поиск значений с неточным совпадением, заменив недостающие символы звездочкой «*» (любое количество символов) или вопросительным знаком «?» (один любой символ). В качестве примеров могут быть критерии «ст?л» (стол либо стул при поиске наименований мебели), «Ива*» (фамилии Иванов, Иваненко, Иванищев и другие, которые начинаются на «Ива»).
4. Если функции ссылаются на ячейки, содержащие коды ошибок #ЗНАЧ! или текстовые строки длиной свыше 255 символов, функция СУММЕСЛИ может возвращать некорректный результат.
5. Аргументы могут ссылаться на диапазоны с разным количеством ячеек. Функция СУММЕСЛИ рассчитывает сумму значений для такого количества ячеек из диапазона суммирования, которое соответствует количеству ячеек, содержащихся в диапазоне. Расчет выполняется с левой верхней ячейки диапазона суммирования.
6. Функция СУММЕСЛИ позволяет использовать только один критерий суммирования. Если необходимо указать сразу несколько критериев, следует использовать функцию СУММЕСЛИМН.
7. Критерий суммирования не обязательно должен относиться к диапазону суммирования. Например, для расчета общей зарплаты сотрудника за год в таблице, в которой содержатся данные о зарплате всех сотрудников, можно ввести формулу =СУММЕСЛИ(A1:A100;”Петренко”;B1:B100), где:

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

• a. A1:A100 – диапазон ячеек, в которых хранятся фамилии сотрудников;
• b. «Петренко» – критерий поиска (фамилия работника) для диапазона A1:A100;
• c. B1:B100 – диапазон ячеек, в которых хранятся данные о зарплатах работников (диапазон суммирования).

Выборочные вычисления по одному или нескольким критериям

11068 20.02.2013 Скачать пример

Постановка задачи

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

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

Способ 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), которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев — ячейки, содержащие условия отбора — и указать затем этот диапазон функции как аргумент:

=БДСУММ(A1:D26;D1;F1:G2)

Суммирование только положительных значений в Excel

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

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

 =СУММЕСЛИ(A1:A10;">0")
 

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

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

Использование функции СУММЕСЛИ

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

Шаг 1: Выберите диапазон

Первым шагом является выбор диапазона ячеек, которые вы хотите суммировать. Например, если вы хотите суммировать значения в ячейках от A1 до A10, выберите эти ячейки.

Шаг 2. Введите функцию СУММЕСЛИ

Затем введите функцию СУММЕСЛИ в ячейку, в которой должна отображаться сумма. Синтаксис функции СУММЕСЛИ:

=СУММЕСЛИ(диапазон, критерии, [диапазон_суммы])

«Диапазон» — это диапазон ячеек, которые вы хотите оценить. «Критерии» — это условие, которое вы хотите применить. В этом случае мы хотим суммировать только положительные значения, поэтому критерий будет «>0». «sum_range» — это диапазон ячеек, которые вы хотите суммировать. Если вы не укажете диапазон_сумм, Excel будет использовать диапазон, указанный вами в первом аргументе.

Шаг 3: Введите критерии

Введите критерии во второй аргумент функции СУММЕСЛИ. В этом случае критерии будут «>0». Это говорит Excel суммировать только те значения, которые больше нуля.

Шаг 4: Нажмите Enter

Нажмите Enter, чтобы завершить формулу. Excel будет суммировать только положительные значения в указанном диапазоне.

Использование функции СУММ с формулой массива

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

Шаг 1: Выберите диапазон

Выберите диапазон ячеек, которые вы хотите просуммировать. Например, если вы хотите суммировать значения в ячейках от A1 до A10, выберите эти ячейки.

Шаг 2. Введите формулу массива

Введите следующую формулу в качестве формулы массива:

=СУММ(ЕСЛИ(A1:A10>0,A1:A10))

Нажмите Ctrl+Shift+Enter, чтобы ввести формулу как формула массива. Excel будет суммировать только положительные значения в указанном диапазоне.

Вывод

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

Суммарные значения, удовлетворяющие одному из нескольких условий в Excel

Советы и учебные пособия по Excel

Редактировать

Добавить в избранное Избранное

Автор: дон

Курс макросов Excel и VBA (скидка 80 %)

Как суммировать значения, которые соответствуют одному из многих потенциальных критериев; это в основном суммирование с условием ИЛИ.

Это не то же самое, что использовать функцию СУММЕСЛИМН(), потому что эта функция суммирует только те значения, которые удовлетворяют всем условиям внутри нее.

Формула

 =СУММЕСЛИ(A1:A5,"красный",B1:B5)+СУММЕСЛИ(A1:A5,"черный",B1:B5)
 

Эта формула суммирует числа для цветов красный и черный .

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

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

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

Результат:

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

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

Примечания

Если вы хотите суммировать значения на основе нескольких критериев в нескольких столбцах с использованием этого типа условия ИЛИ, ознакомьтесь с нашим учебным пособием о том, как суммировать значения, которые равны 1 из многих условий в нескольких столбцах.

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

Функция Excel: СУММЕСЛИ()

Версия Excel: Эксель 2003, Эксель 2007, Эксель 2010, Эксель 2013, Эксель 2016

---

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения Более 200 руководств Excel

Станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel с помощью этого онлайн-курса. (Опыт работы с VBA не требуется.)

Посмотреть курс

Подпишитесь на еженедельные уроки

БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков
50+ часов видео
Более 200 справочников Excel

Станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel с помощью этого онлайн-курса.