Суміф із кількома критеріями на основі логіки АБО та І

Під час роботи з аркушами Excel вам може знадобитися підсумовувати значення на основі кількох критеріїв. Іноді кілька критеріїв походять з одного стовпця (логіка АБО), але іноді з різних стовпців (логіка І). Як у цьому випадку впоратися з цим завданням у Excel?

• Суміф із кількома критеріями на основі логіки АБО
  • За допомогою формули SUMIF + SUMIF +…
  • За допомогою функцій SUM та SUMIF
• Sumif з кількома критеріями на основі логіки І за допомогою функції SUMIFS

Суміф із кількома критеріями на основі логіки АБО

Припустимо, що у вас є список продуктів, який містить постачальника та кількість, як показано на скріншоті нижче, тепер ви хочете отримати всі загальні кількості, які постачають постачальник Джон і Девід. Тут я наведу для вас дві прості формули.

За допомогою формули SUMIF + SUMIF +…

Якщо ви хочете підсумувати числа, які відповідають одному з критеріїв (логіка АБО) з кількох критеріїв, ви можете додати кілька функцій SUMIF в одну формулу, загальний синтаксис:

• criteria_range: діапазон комірок, яким потрібно відповідати критеріям;
• criteria1: Перший критерій, який використовується для визначення клітинок, які потрібно підсумувати;
• criteria2: Другий критерій, який використовується для визначення клітинок, які потрібно підсумувати;
• sum_range: діапазон комірок, з яких потрібно підсумувати.

Тепер скопіюйте або введіть будь-яку з наведених нижче формул у порожню клітинку та натисніть Що натомість? Створіть віртуальну версію себе у ключ для отримання результату:

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

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

• Перший SUMIF(B4:B14,"Іван",C4:C14) знаходить рядки Івана і підсумовує загальні кількості;
• Друга SUMIF(B4:B14,"Давид",C4:C14) знаходить рядки Давида і підсумовує загальні величини;
• Потім складіть ці дві формули SUMIF, щоб отримати всі загальні кількості, надані Джоном і Девідом.

За допомогою функцій SUM та SUMIF

Наведена вище формула дуже проста у використанні, якщо існує лише пара критеріїв, але якщо ви хочете підсумувати значення з кількома умовами АБО, наведена вище формула може бути зайвою. У цьому випадку краща формула, створена на основі функцій SUM і SUMIF, може зробити вам послугу. Загальні синтаксиси:

Загальна формула з жорстко закодованим текстом:

• criteria_range: діапазон комірок, яким потрібно відповідати критеріям;
• criteria1: Перший критерій, який використовується для визначення клітинок, які потрібно підсумувати;
• criteria2: Другий критерій, який використовується для визначення клітинок, які потрібно підсумувати;
• sum_range: діапазон комірок, з яких потрібно підсумувати.

Загальна формула з посиланнями на клітинки:

• criteria_range: діапазон комірок, яким потрібно відповідати критеріям;
• criteria_cells: клітинки, які містять критерії, які потрібно використовувати;
• sum_range: діапазон комірок, з яких потрібно підсумувати.

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

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

=SUM(SUMIF(B4:B14, {"John","David"}, C4:C14))

> SUMIF(B4:B14, {"John","David"}, C4:C14):

• {"Джон","Девід"}: константа масиву, яка являє собою набір кількох критеріїв, наданих у фігурних дужках.
• SUMIF(B4:B14,"Давид",C4:C14) Константа масиву, що використовує логіку АБО, змушує функцію SUMIF сумувати числа в C4:C14 на основі будь-якого з множинних критеріїв («Джон» і «Девід»), і вона поверне два окремі результати: {410,1520}.

> SUM(SUMIF(B4:B14, {"John","David"}, C4:C14))=SUM({410,1520}): Нарешті, ця функція SUM підсумовує ці результати масиву, щоб повернути результат: 1930.

Sumif з кількома критеріями на основі логіки І за допомогою функції SUMIFS

Якщо ви хочете підсумувати значення з кількома критеріями в різних стовпцях, ви можете використовувати функцію SUMIF для швидкого вирішення цього завдання. Загальний синтаксис:

• sum_range: діапазон комірок, з яких потрібно підсумувати;
• criteria_range1: Діапазон, де застосовується criteira1;
• criteria1: Перший критерій, який перевіряється щодо діапазону_критеріїв1 і визначає, які клітинки додати; (Типом критеріїв може бути: число, логічний вираз, посилання на клітинку, текст, дата або інша функція Excel.)
• criteria_range2, criteria2…: Інші додаткові діапазони та пов'язані з ними критерії. (Ви можете встановити 127 пар критеріїв діапазону та критеріїв у формулі SUMIFS.)

Скажімо, у мене є таблиця зі стовпцями Продукт, Постачальник, Кількість і Ціна, як показано нижче. Тепер я хочу дізнатися суму загальної ціни продукту Apple і кількості, що більше або дорівнює 200.

Будь ласка, застосуйте будь-яку з наведених нижче формул у порожню клітинку та натисніть Що натомість? Створіть віртуальну версію себе у ключ для повернення результату:

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

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

• Діапазон A4:A14 перевіряє продукт «Apple», а діапазон C4:C14 витягує всі кількості, що перевищують або дорівнюють 200, тоді функція SUMIFS підсумовує лише відповідні клітинки з діапазону D4:D14.

Використана відносна функція:

Більше статей: