SUMIFS (функція SUMIFS)

Функція SUMIFS, яка належить до математичних і тригонометричних функцій, додає всі аргументи, які відповідають кільком критеріям. Наприклад, функцію SUMIFS можна використовувати, щоб обчислювати кількість продавців у країні, які (1) живуть за адресами з однаковим поштовим індексом і (2) чиї прибутки перевищують певну суму.

Створення завдання

Це відео входить до навчального курсу Розширені функції IF.

Синтаксис

SUMIFS(діапазон_суми;діапазон_критерію1;критерій1;[діапазон_критерію2;критерій2];...)

  • =SUMIFS(A2:A9;B2:B9;"=A*";C2:C9;"Тарас")

  • =SUMIFS(A2:A9;B2:B9;"<>Банани";C2:C9;"Тарас")

Ім’я аргументу

Опис

Діапазон_суми    (обов’язковий аргумент)

Діапазон клітинок, які потрібно додати.

Діапазон_критерію1    (обов’язковий аргумент)

Діапазон, який перевіряється за допомогою аргументу критерій1.

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

Критерій1    (обов’язковий аргумент)

Критерій, що визначає, які клітинки додавати в аргументі діапазон_критерію1. Наприклад, можна ввести такі критерії: 32, ">32", B4, "яблука" або "32".

Діапазон_критерію2;критерій2;...    (необов’язковий аргумент)

Додаткові діапазони та пов’язані з ними критерії. Можна ввести до 127 пар діапазонів і критеріїв.

Приклади

Щоб використовувати ці приклади в програмі Excel, виділіть дані в таблиці, перетягнувши вказівник, клацніть виділений фрагмент правою кнопкою миші та виберіть команду Копіювати. На новому аркуші клацніть правою кнопкою миші клітинку A1 і виберіть пункт Використовувати форматування кінцевого фрагмента в розділі Параметри вставлення.

Обсяг продажу

Продукт

Продавець

5

Яблука

Тарас

4

Яблука

Юлія

15

Артишоки

Тарас

3

Артишоки

Юлія

22

Банани

Тарас

12

Банани

Юлія

10

Морква

Тарас

33

Морква

Юлія

Формула

Опис

=SUMIFS(A2:A9;B2:B9;"=A*";C2:C9;"Тарас")

Додає кількість продуктів, які починаються з букви А та які продав Тарас. Ця формула містить символ узагальнення * в аргументі критерій1, "=A*" для пошуку відповідних назв продуктів у діапазоні_критерію1 B2:B9 та шукає ім’я продавця "Тарас" у діапазоні_критерію2 C2:C9. Потім числа в діапазоні_суми A2:A9, які відповідають обом критеріям, додаються. У результаті отримаємо 20.

=SUMIFS(A2:A9;B2:B9;"<>Банани";C2:C9;"Тарас")

Додає кількість продуктів, відмінних від бананів, які продав Тарас. Ця формула виключає банани за допомогою символів <> в аргументі критерій1 ("<>Банани") і шукає ім’я продавця "Тарас" у діапазоні_критерію2 C2:C9. Потім числа в діапазоні_суми A2:A9, які відповідають обом умовам, додаються. У результаті отримаємо 30.

Поширені проблеми

Проблема

Опис

Замість очікуваного результату відображається 0 (нуль).

Переконайтеся, що аргументи критерій1,2 взято в лапки, якщо шукаються текстові значення, наприклад ім’я користувача.

Результат хибний, коли аргумент діапазон_суми має значення TRUE або FALSE.

Значення TRUE та FALSE для аргументу діапазон_суми обчислюються по-різному, а це може призвести до неочікуваних результатів, якщо їх додати.

Клітинки в діапазоні_суми, які містять значення TRUE, інтерпретуються як 1. Клітинки, які містять значення FALSE, інтерпретуються як 0 (нуль).

Практичні поради

Дія

Опис

Використовуйте символи узагальнення.

Використання символів узагальнення, як-от знака питання (?) і зірочки (*) в аргументах критерій1,2 допоможе знайти схожі, але не точні збіги.

Знак питання відповідає будь-якому одному символу. Зірочка відповідає будь-якій послідовності символів. Якщо потрібно знайти власне знак питання або зірочку, введіть тильду (~) перед знаком питання.

Наприклад, =SUMIFS(A2:A9;B2:B9;"=A*";C2:C9;"Тара?") додасть усі екземпляри з ім’ям, що починаються з букв "Тара" та закінчуються на ще одну будь-яку букву.

Запам’ятайте відмінності між функціями SUMIF і SUMIFS.

Порядок аргументів у функціях SUMIF і SUMIFS відрізняється. Зокрема, аргумент діапазон_суми є першим у функції SUMIFS і третім у SUMIF. Це часто стає причиною проблем, коли використовуються ці функції.

Коли ви копіюєте та редагуєте ці подібні функції, переконайтеся, що аргументи розташовано в правильному порядку.

Використовуйте однакову кількість рядків і стовпців для аргументів діапазону.

Аргумент діапазон_критерію має містити таку саму кількість рядків і стовпців, що й аргумент діапазон_суми.

На початок сторінки

Маєте запитання щодо конкретних функцій?

Опублікуйте запитання на форумі спільноти Microsoft Excel

Допомога в удосконаленні програми Excel

Маєте пропозиції щодо того, як покращити наступну версію програми Excel? Тоді перегляньте розділи на веб-сайті Excel User Voice.

Див. також

Див. відео про те, як використовувати розширені функції IF, зокрема SUMIFS

Функція SUMIF додає лише ті значення, які відповідають одній умові

Функція SUMSQ підсумовує значення після піднесення до квадрата кожного з них

Функція COUNTIF підраховує лише ті значення, які відповідають одній умові

Функція COUNTIFS підраховує лише ті значення, які відповідають кільком умовам

Функція IFS (Office 365, Excel 2016 або пізнішої версії)

Огляд формул у програмі Excel

Способи уникнення недійсних формул

Виявлення помилок у формулах

Математичні та тригонометричні функції

Функції Excel (за алфавітом)

Функції Excel (за категоріями)

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×