Note: The other languages of the website are Google-translated. Back to English
English English

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 - поможет вам выделиться из толпы

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

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

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

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (0)
Оценок пока нет. Оцените первым!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места