Як додати кілька критеріїв у Сумміслі

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

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

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

Використовуючи формулу СУМІСЛІ + СУМІСЛІ +…

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

• 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) знаходить рядки Давида та підсумовує загальні кількості;
• Потім складіть ці дві формули СУМІСЛІ, щоб отримати всі загальні кількості, надані Джоном та Девідом.

Використовуючи функції СУМ та СУМІСЛІ

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

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

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

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

• 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 на основі будь-якого з кількох критеріїв («Джон» та «Девід»), і вона повертає два окремі результати: .

> СУМ (СУМІСЛІ (B4: B14, , C4: C14)) = СУМ () : Нарешті, ця функція SUM складає ці результати масиву, щоб відновити результат: 1930.

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

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

• 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.

Як додати кілька критеріїв у Сумміслі

В Excel функція СУМІСЛИ - корисна функція для підсумовування осередків з кількома критеріями в різних стовпцях, але за допомогою цієї функції ми також можемо підсумовувати комірки на основі кількох критеріїв в одному стовпчику. У цій статті. Я розповім про те, як підсумовувати значення більш ніж одного критерію в одному стовпці.

Підсумовуйте осередки з кількома критеріями АБО в одному стовпці з формулами

Наприклад, я маю наступний діапазон даних, тепер я хотів би отримати загальні замовлення продукту KTE і KTO в січні.

Для кількох критеріїв АБО в одному полі використовуйте кілька функцій СУМІСЛИ:

Формула 1: СУМІСЛИ + СУМІСЛІ

Будь ласка, введіть цю формулу: =SUMIF(A2:A10,"KTE",B2:B10) + SUMIF(A2:A10,"KTO",B2:B10) , а потім натисніть Enter key, ви отримаєте загальну вартість продукту KTE та KTO, див. скріншоти:

1. У наведеній вище формулі A2: A10 це діапазон осередків, до яких ви хочете застосувати критерії, B2: B10 - це комірки, які ви хочете підсумовувати, а KTE, KTO - це критерії, за якими ви підсумовуєте комірки.

2. У цьому прикладі є тільки два критерії, ви можете застосувати більше критеріїв, просто продовжуйте додавати СУМІСЛИ () після формули, наприклад = sumif (діапазон, критерії, діапазон_сум) + sumif (діапазон, критерії, діапазон_сум) + sumif (діапазон, критерії, діапазон_сум) +…

Формула 2: СУМ та СУМІСЛІ

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

Введіть цю формулу в порожню комірку: = СУМ (СУМІСЛІ (A2: A10; ; B2: B10)) , а потім натисніть Enter ключ, щоб отримати потрібний результат, див. знімок екрана:

1.У наведеній вище формулі A2: A10 це діапазон осередків, до яких ви хочете застосувати критерії, B2: B10 це комірки, які ви хочете підсумовувати, та КТЕ, KTO є критеріями, за якими ви підсумовуєте осередки.

2. Щоб підсумувати більше критеріїв, вам просто потрібно додати критерії до фігурних дужок, наприклад = СУМ (СУМІСЛІ (A2: A10, , B2: B10)) .

3. Ця формула може використовуватися тільки в тому випадку, якщо осередки діапазону, до яких ви хочете застосувати критерії, знаходяться в одному стовпці.

Advanced Combine Rows: (Об'єднайте рядки, що повторюються, і підсумовуйте / усереднюйте відповідні значення):

• 1. Вкажіть ключовий стовпець, на основі якого ви хочете поєднати інший стовпець;
• 2. Виберіть один облік для об'єднаних даних.

Kutools for Excel: з більш ніж 300 зручними надбудовами Excel, які можна скуштувати безкоштовно без обмежень протягом 30 днів. Завантажте безкоштовну пробну версію прямо зараз!

Статті на тему:

Функція СУМІСЛІ

Функція СУМІСЛІ використовується, якщо необхідно підсумувати значення діапазон, що відповідають зазначеному критерію. Припустимо, наприклад, що у стовпці з числами необхідно підсумувати лише значення, що перевищують 5. Для цього можна використовувати таку формулу: =СУМІСЛІ(B2:B25;"> 5")

Це відео - частина навчального курсу Додавання чисел в Excel.

• При необхідності умови можна застосувати до одного діапазону, а підсумувати відповідні значення іншого діапазону. Наприклад, формула =СУМІСЛІ(B2:B5; "Іван"; C2:C5) підсумовує ті значення з діапазону C2:C5, для яких відповідні значення з діапазону B2:B5 рівні "Іван".
• Якщо необхідно виконати підсумовування осередків відповідно до кількох умов, використовуйте функцію СУМЕСЛІМН.

Важливо: Функція СУМІСЛИ повертає невірні результати при використанні для зіставлення рядків довжиною понад 255 символів або рядкового #VALUE!.

Синтаксис

СУМІСЛИ(діапазон; умова; [діапазон_підсумовування])

Аргументи функції СУМІСЛІ описані нижче.

• Діапазон - Обов'язковий аргумент. Діапазон осередків, що оцінюються на відповідність умовам. Осередки у кожному діапазоні повинні містити числа, імена, масиви чи посилання числа. Порожні та текстові значення ігноруються. Вибраний діапазон може містити дати стандартного формату Excel (див. приклади нижче).
• Умова . Обов'язковий аргумент. Умова у формі числа, виразу, посилання на комірку, тексту або функції, яка визначає, які комірки необхідно підсумовувати. Можна ввімкнути знаки підстановки : знак запитання (?) для порівняння з будь-яким одним символом, зірочка (*) для відповідності будь-якій послідовності символів. Якщо потрібно знайти безпосередньо знак питання (або зірочку), необхідно поставити перед ним знак "тильда" (~). Наприклад, критерії можна виразити як 32, ">32", B5, "3?", "apple*", "*~?" або TODAY().

Важливо: Усі текстові умови та умови з логічними та математичними знаками необхідно укладати у подвійні лапки ("). Якщо умовою є число, використовувати лапки не потрібно.

Приклади

Приклад 1

Скопіюйте зразок даних з наступної таблиці та вставте їх у комірку A1 нового аркуша Excel. Щоб відобразити результати формул, виділіть їх та натисніть клавішу F2, а потім – клавішу Enter. За потреби змініть ширину стовпців, щоб побачити всі дані.

Вартість майна