Функцію SUMIF використовують, щоб числові дані діапазону підсумувати за заданим критерієм. За назвою функції (підсумовувати якщо) можна зрозуміти, що вона не просто підраховує суму, але ще й підпорядковується будь-яким логічним умовам. Її можна успішно пристосувати для вирішення різних завдань. Тому ми підготували вам приклади, як використовувати SUMIF.
Функція SUMIF та її синтаксис
Функція SUMIF в Excel (СУММЕСЛИ в російській версії Excel) має такі аргументи:
- Діапазон – обов’язковий аргумент, вказуються клітинки, які потрібно обчислити відповідно до заданої умови.
- Критерій – обов’язковий параметр, що визначає, які клітинки з діапазону будуть обрані. В якості критерію може бути адреса клітинки, число, текст або інша функція
- Діапазон для суми – не обов’язковий параметр, фактичні клітинки, які необхідно підсумувати, якщо вони відповідають критерію. Якщо цей аргумент не вказати, то підсумовуватимуться клітинки, до яких застосовується умови, тобто будуть взяті аргументи з Діапазон.
Якщо в якості умови використовуватиметься текст або математичні символи, то їх обов’язково потрібно брати в лапки.
Приклад, як працює функція SUMIF В Excel
Розглянемо класичний приклад (example) SUMIF Excel. Нехай є таблиця з даними продажу деяких товарів.
У таблиці вказано позиції, їх кількості, а також приналежність до тієї чи іншої групи товарів (другий стовпець). Розглянемо поки що спрощене використання SUMIF (сума якщо), коли нам потрібно порахувати суму лише за тими позиціями, значення за якими відповідають певній умові.
Наприклад, хочемо дізнатися, скільки було продано топових позицій, тобто тих, значення яких перевищує 70 одиниць. Шукати такі товари очима, а потім підсумовувати вручну не дуже зручно, тому функція SUMIF тут дуже доречна.
Насамперед виділяємо клітинку, де буде підрахована сума, наприклад, F2. Далі викликаємо «Майстер функцій». Це значок fx у рядку формул та шукаємо у списку функцію SUMIF, натискаємо на неї.
Відкривається діалогове вікно, де для вирішення цього завдання потрібно заповнити всього два (перші) поля із трьох запропонованих. Саме 2, а не 3, оскільки наша умова знаходиться в діапазоні для суми:
- У полі «Діапазон» вказуються клітинки таблиці Excel, де є всі вихідні значення для пошуку, з яких потрібно щось відібрати і потім скласти. У нашому випадку D2:D16, зазвичай задається за допомогою мишки.
- У полі «Критерій» вказується та умова, за якою формула проводитиме відбір, тобто вказуємо «>70». Якщо не поставити лапки, вони потім самі з’являться.
Таким чином, функція SUMIF Excel бере критерій і починає відбирати всі значення із зазначеного діапазону, що задовольняють заданому критерію. Після цього усі відібрані значення складаються. Так працює алгоритм функції.
Заповнивши всі поля, натиснути на клавіатурі клавішу «Enter», або «ОК» у вікні майстра. На місці функції, що вводиться (комірка F2), має з’явитися розраховане значення. У розглянутому прикладі вийшло 252 шт. Тобто сумарне значення проданих товарів у кількості понад 70 штук становило 252 шт. До речі, це число можна побачити у нижньому лівому кутку вікна Майстра ще до натискання «ОК».
Приклад SUMIF з використанням всіх параметрів
Розглянемо, наприклад, коли критерій (умова) не збігається з діапазоном для суми. Така ситуація зустрічається набагато частіше. Розглянемо ті самі умовні дані. Допустимо потрібно дізнатися суму конкретної групи товарів, припустимо, Крупи.
Для цього виконуємо такі дії:
- Знову виділяємо клітинку з майбутнім підрахунком суми, наприклад, F4 і викликаємо Майстер функцій. У вікні “Виберіть функцію” виділяємо нашу функцію та натискаємо ОК.
- У рядку «Діапазон» вказуємо діапазон, де міститься критерій, у нашому випадку це стовпець із назвами груп товарів (B2:B16). У рядку «Критерій» у лапках пишемо «Крупи». У полі «Діапазон для суми» – вказуємо діапазон, де знаходяться дані для суми, тобто D2:D16.
- Натискаємо кнопку ОК. Результатом буде сума проданих товарів із групи «Крупи» – 185 шт.
Розрахунок кількох сум для кількох критеріїв
Розглянемо приклад, коли потрібно дізнатися про суми проданих товарів по кожній групі. Тобто цікавить 3 (три) значення по 3-м (трьом) групам (фрукти, овочі та крупи). Для цього зазвичай робиться список груп у вигляді окремої таблиці.
Назви груп окремої таблиці повинні точно збігатися з назвами груп у вихідній таблиці, інакше будуть помилкові розрахунки.
Після цього прописується формула для першої групи (у нашому випадку клітинка G7) і простягається на всі інші. Тут потрібно звернути увагу на відносність посилань.
Діапазон із критеріями та діапазон для суми мають бути абсолютними посиланнями, щоб при копіюванні формули вони не «поїхали вниз», їх адреси не повинні змінюватися. А ось сам критерій має бути відносним посиланням (змінюваним), тому що для кожної суми існує свій критерій підсумовування. У нашому випаду критеріями є назви груп зі створеної окремої таблиці.
Щоб діапазон із комірками не змінювався, після його виділення на клавіатурі потрібно натиснути функціональну клавішу F4.
Коли всі поля заповнені, натискаємо ОК. Далі, знаходячись на клітинці G7, беремо за її нижній правий куточок, затискаємо ЛКМ і простягаємо формулу вниз. Заповнені поля Майстра функцій при подібному розрахунку та готовий результат будуть виглядати як показано на скріншоті нижче.
Функція SUMIFS
Трапляються випадки, коли потрібно підсумувати дані таблиці по діапазону за кількома критеріями. Відмінність SUMIFS від формули SUMIF у тому, що для неї можна вказувати декілька діапазонів критеріїв, і до кожного з них свою умову. Діапазон для суми указується лише 1 (один) раз. Ця функція з’явилася вперше у MS Excel 2007.
Розглянемо приклад із нашої таблиці. Припустимо, нам потрібно знайти суму з групи «Овочі», але тільки з міста Херсон. Для цього викликаємо Майстер функцій (значок fx) знаходимо потрібну нам функцію та заповнюємо поля.
Після заповнення натискаємо ОК. В результаті Excel порахує дані та виведе число 80 шт. Заповнені дані будуть виглядати як на знімку з екрана нижче.
Використання SUMIF в Google Sheets
Сьогодні все більше користувачів використовують SUMIF Excel в Гугл таблицях. Вона має ті ж самі аргументи і працює по тому ж самому принципу. Тобто спочатку вказується діапазон для умови, потім сам критерій і останнім прописують діапазон для суми.
Знову розглянемо приклад пошуку суми конкретної групи товарів, наприклад «Крупи», але вже в гугл таблицях. SUMIF в Google Sheets працює таким чином:
- Виділяємо клітинку, де буде підрахована сума, наприклад F4;
- Натискаємо знак «=» (дорівнює), повністю пишемо назву функції і відкриваємо дужки;
- Мишкою вказуємо діапазон де знаходяться початкові дані для пошуку (B4:B18);
- Ставимо крапку з комою, вказуємо нашу умову у лапках – «Крупи»;
- Знову ставимо «;» та мишкою вказуємо діапазон для суми – D4:D18;
- Закриваємо дужку і натискаємо клавішу Enter на клавіатурі.
В результаті отримаємо число 185 шт. Принцип роботи з SUMIF in Google Sheets з формулами такий самий, як і у звичайному Excel, тільки більше доводить працювати вручну.
Ми розглянули різні варіанти використання функції Excel SUMIF (сума-якщо). Якщо у вас залишилися питання або є досвід її використання, пропонуємо поділитися цим в коментарях.
