Перейти к основному содержанию

Sumif с несколькими критериями на основе логики ИЛИ и И

При работе с листами Excel вам может потребоваться суммировать значения на основе нескольких критериев. Иногда несколько критериев взяты из одного столбца (логика ИЛИ), но иногда из разных столбцов (логика И). В таком случае, как бы вы могли справиться с этой задачей в Excel?


Sumif с несколькими критериями на основе логики ИЛИ

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

Используя формулу СУММЕСЛИ + СУММЕСЛИ +…

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

=SUMIF(criteria_range, criteria1, sum_range)+SUMIF(criteria_range, criteria2, sum_range)+…
  • criteria_range: Диапазон ячеек, который должен соответствовать критериям;
  • criteria1: Первый критерий, используемый для определения суммирования ячеек;
  • criteria2: Второй критерий, используемый для определения суммирования ячеек;
  • sum_range: Диапазон ячеек, по которым вы хотите произвести суммирование.

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

=SUMIF(B4:B14,"John",C4:C14) + SUMIF(B4:B14,"David",C4:C14)           (Type the criteria manually)
=SUMIF(B4:B14,E4,C4:C14) + SUMIF(B4:B14,E5,C4:C14)                           
(Use a cell reference)


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

=SUMIF(B4:B14,"John",C4:C14) + SUMIF(B4:B14,"David",C4:C14)

  • Первый СУММЕСЛИ (B4: B14; «Джон»; C4: C14) находит строки Джона и суммирует общие количества;
  • Второй СУММЕСЛИ (B4: B14, «Давид», C4: C14) находит строки Давида и суммирует общие количества;
  • Затем сложите эти две формулы СУММЕСЛИ, чтобы получить все общие количества, предоставленные Джоном и Дэвидом.

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

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

Общая формула с жестко закодированным текстом:

=SUM(SUMIF(criteria_range, {criteria1,criteria2,...}, sum_range))
  • criteria_range: Диапазон ячеек, который должен соответствовать критериям;
  • criteria1: Первый критерий, используемый для определения суммирования ячеек;
  • criteria2: Второй критерий, используемый для определения суммирования ячеек;
  • sum_range: Диапазон ячеек, по которым вы хотите произвести суммирование.

Общая формула со ссылками на ячейки:

{=SUM(SUMIF(criteria_range, criteria_cells, sum_range))}
Array formula, should press Ctrl + Shift + Enter keys together.
  • criteria_range: Диапазон ячеек, который должен соответствовать критериям;
  • criteria_cells: Ячейки, содержащие критерии, которые вы хотите использовать;
  • sum_range: Диапазон ячеек, по которым вы хотите произвести суммирование.

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

=SUM(SUMIF(B4:B14, {"John","David"}, C4:C14))           (Type the criteria manually)
=SUM(SUMIF(B4:B14, E4:E5, C4:C14))                             
(Use cell references, array formula, should press Ctrl + Shift + Enter keys)


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

= СУММ (СУММЕСЛИ (B4: B14; {"Джон"; "Давид"}; C4: C14))

> СУММЕСЛИ (B4: B14, {"Джон", "Давид"}, C4: C14):

  • {"Джон", "Дэвид"}: Константа массива, которая представляет собой набор нескольких критериев, заключенных в фигурные скобки.
  • СУММЕСЛИ (B4: B14, «Давид», C4: C14) Константа массива, использующая логику ИЛИ, заставляет функцию СУММЕСЛИ суммировать числа в C4: C14 на основе любого из нескольких критериев («Джон» и «Дэвид»), и она возвращает два отдельных результата: {410,1520}.

> СУММ (СУММЕСЛИ (B4: B14, {"Джон", "Давид"}, C4: C14)) = СУММ ({410,1520}): Наконец, эта функция SUM складывает эти результаты массива, чтобы вернуть результат: 1930.


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

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

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • sum_range: Диапазон ячеек, по которым вы хотите произвести суммирование;
  • criteria_range1: Диапазон, в котором применяется критерий1;
  • criteria1: Первый критерий, который проверяется по диапазону критериев1 и определяет, какие ячейки нужно добавить; (типом критерия может быть: число, логическое выражение, ссылка на ячейку, текст, дата или другая функция Excel.)
  • criteria_range2, criteria2…: Другие дополнительные диапазоны и связанные с ними критерии. (вы можете установить 127 пар критериев_диапазона и критериев в формуле СУММЕСЛИМН.)

Скажем, у меня есть таблица со столбцами Product, Supplier, Qty и Price, как показано на скриншоте ниже. Теперь я хочу узнать сумму общей цены продукта Apple и количества, которое больше или равно 200.

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

=SUMIFS(D4:D14,A4:A14,"Apple",C4:C14,">=200")           (Type the criteria manually)
=SUMIFS(C4:C14,A4:A14,G3,C4:C14,G4)                           
(Use cell references)


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

=SUMIFS(D4:D14,A4:A14,"Apple",C4:C14,">=200")

  • Диапазон A4: A14 проверяет наличие продукта «Apple», а диапазон C4: C14 извлекает все количества, превышающие или равные 200, затем функция СУММЕСЛИМН суммирует только совпавшие ячейки из диапазона D4: D14.

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

  • SUMIF:
  • Функция СУММЕСЛИ может помочь суммировать ячейки на основе одного критерия.
  • SUMIFS:
  • Функция СУММЕСЛИМН в Excel может помочь суммировать значения ячеек на основе нескольких критериев.

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

  • Сумма, если ячейки содержат определенный текст в другом столбце
  • Из этого туториала Вы узнаете, как суммировать значения, если ячейки содержат определенный или частичный текст в другом столбце. В качестве примера возьмем диапазон данных ниже, чтобы получить общее количество продуктов, содержащих текст «Футболка», с этой задачей в Excel могут справиться как функция СУММЕСЛИ, так и функция СУММПРОИЗВ.
  • Сумма наименьших или нижних значений N в Excel
  • В Excel легко суммировать диапазон ячеек с помощью функции СУММ. Иногда вам может потребоваться суммировать наименьшие или нижние 3, 5 или n чисел в диапазоне данных, как показано ниже. В этом случае СУММПРОИЗВ вместе с функцией МАЛЕНЬКИЙ могут помочь вам решить эту проблему в Excel.

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

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

Популярные опции: Найдите, выделите или определите дубликаты  |  Удалить пустые строки  |  Объедините столбцы или ячейки без потери данных  |  Раунд без формулы ...
Супер ВПросмотр: Несколько критериев  |  Множественное значение  |  На нескольких листах  |  Нечеткий поиск...
Адв. Выпадающий список: Простой раскрывающийся список  |  Зависимый раскрывающийся список  |  Выпадающий список с множественным выбором...
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  Сравнить столбцы с Выберите одинаковые и разные ячейки ...
Рекомендуемые функции: Сетка Фокус  |  Просмотр дизайна  |  Большой Формулный Бар  |  Менеджер книг и листов | Библиотека ресурсов (Авто текст)  |  Выбор даты  |  Комбинировать листы  |  Шифровать/дешифровать ячейки  |  Отправлять электронные письма по списку  |  Суперфильтр  |  Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы ...)  |  50+ График Тип (Диаграмма Ганта ...)  |  40+ Практических Формулы (Рассчитать возраст по дню рождения ...)  |  19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути ...)  |  12 Конверсия Инструменты (Числа в слова, Конверсия валюты ...)  |  7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить ячейки Excel ...)  |  ... и более

Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...


Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
do you guys know if this logic works on google sheets?
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations