Sumif с несколькими критериями на основе логики ИЛИ и И
При работе с листами Excel вам может потребоваться суммировать значения на основе нескольких критериев. Иногда несколько критериев взяты из одного столбца (логика ИЛИ), но иногда из разных столбцов (логика И). В таком случае, как бы вы могли справиться с этой задачей в Excel?
- Sumif с несколькими критериями на основе логики ИЛИ
- Sumif с несколькими критериями на основе логики И с использованием функции СУММЕСЛИМН
Sumif с несколькими критериями на основе логики ИЛИ
Предположим, у вас есть список продуктов, который содержит поставщика и количество, как показано на скриншоте ниже, теперь вы хотите получить все общие количества, которые поставляются поставщиком Джоном и Дэвидом. Здесь я представлю вам две простые формулы.
Используя формулу СУММЕСЛИ + СУММЕСЛИ +…
Если вы хотите суммировать числа, которые соответствуют любому из критериев (логика ИЛИ) из нескольких критериев, вы можете сложить несколько функций СУММЕСЛИ в одной формуле, общий синтаксис следующий:
- criteria_range: Диапазон ячеек, который должен соответствовать критериям;
- criteria1: Первый критерий, используемый для определения суммирования ячеек;
- criteria2: Второй критерий, используемый для определения суммирования ячеек;
- sum_range: Диапазон ячеек, по которым вы хотите произвести суммирование.
Теперь скопируйте или введите любую из приведенных ниже формул в пустую ячейку и нажмите Enter ключ для получения результата:
=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) находит строки Давида и суммирует общие количества;
- Затем сложите эти две формулы СУММЕСЛИ, чтобы получить все общие количества, предоставленные Джоном и Дэвидом.
Используя функции СУММ и СУММЕСЛИ
Приведенная выше формула очень проста в использовании, если есть только пара критериев, но если вы хотите суммировать значения с несколькими условиями ИЛИ, приведенная выше формула может быть избыточной. В этом случае лучшая формула, созданная на основе функций СУММ и СУММЕСЛИ, может оказать вам услугу. Общие синтаксисы:
Общая формула с жестко закодированным текстом:
- criteria_range: Диапазон ячеек, который должен соответствовать критериям;
- criteria1: Первый критерий, используемый для определения суммирования ячеек;
- criteria2: Второй критерий, используемый для определения суммирования ячеек;
- sum_range: Диапазон ячеек, по которым вы хотите произвести суммирование.
Общая формула со ссылками на ячейки:
Array formula, should press Ctrl + Shift + Enter keys together.
- criteria_range: Диапазон ячеек, который должен соответствовать критериям;
- criteria_cells: Ячейки, содержащие критерии, которые вы хотите использовать;
- sum_range: Диапазон ячеек, по которым вы хотите произвести суммирование.
Пожалуйста, введите или скопируйте любую из приведенных ниже формул в пустую ячейку и получите результат:
=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 с несколькими критериями на основе логики И с использованием функции СУММЕСЛИМН
Если вы хотите суммировать значения с несколькими критериями в разных столбцах, вы можете использовать функцию СУММЕСЛИ, чтобы быстро решить эту задачу. Общий синтаксис:
- sum_range: Диапазон ячеек, по которым вы хотите произвести суммирование;
- criteria_range1: Диапазон, в котором применяется критерий1;
- criteria1: Первый критерий, который проверяется по диапазону критериев1 и определяет, какие ячейки нужно добавить; (типом критерия может быть: число, логическое выражение, ссылка на ячейку, текст, дата или другая функция Excel.)
- criteria_range2, criteria2…: Другие дополнительные диапазоны и связанные с ними критерии. (вы можете установить 127 пар критериев_диапазона и критериев в формуле СУММЕСЛИМН.)
Скажем, у меня есть таблица со столбцами Product, Supplier, Qty и Price, как показано на скриншоте ниже. Теперь я хочу узнать сумму общей цены продукта Apple и количества, которое больше или равно 200.
Примените любую из приведенных ниже формул в пустую ячейку и нажмите Enter ключ для возврата результата:
=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 для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
