Функція «sumifs» в Excel: приклади використання

При складанні звітної документації іноді необхідно скористатися підсумовуванням осередків, які відповідають певним умовам. Так, функція «sumifs» Excel (приклади будуть приведені) відмінно справляється з поставленим завданням. Якщо в операторі «sumif» можливий відбір по одному критерію, то вищезгаданий варіант дозволяє задіяти кілька діапазонів.

Математичні функції в Excel

В даній категорії міститься близько 80 функцій. Тут можна знайти оператори, здатні прорахувати значення аркуша будь-якого формату. В Excel «sumif» часто зустрічається, а набір тригонометричних функцій підходить для певного кола користувачів. У чому ж виражається суть арифметичного оператора, у якого є аналог в останніх версіях програмного продукту «sumifs»? Його завдання підсумувати значення, які потрапляють під певні критерії.


Більшою ступеня математичні функції покликані автоматизувати роботу користувача. Якщо виникають питання щодо застосування оператора, викликають довідку. Зробити це можна у вікні «Аргументи функції» натисканням на посилання «довідка» або через F1. Для введення формули необхідно натиснути знак «=». В іншому випадку програмний продукт розпізнає інформацію як текст або видає помилку. Формули користувач прописує вручну, спираючись на свої знання та навички, через панель інструментів або через «Майстер підстановок».

Опис функції

Сума чисел, що задовольняють визначеним критеріям, досягається за рахунок оператора «sumifs» в Excel. Приклади, якщо розглянути, наочно покажуть, як правильно користуватися цією функцією і не допускати помилок.


В мові програмування VBA і англійської версії редактора електронних таблиць прийнятий синтаксис, написаний латинськими літерами. У вітчизняному аналогу – росіянами. Синтаксис функції виглядає наступним чином: =sumifs(диапазон_суммирования;[диапазон_условия1; условие1];[диапазон_условия2; условие2]; ) Під діапазоном підсумовування розуміється масив, осередки якого будуть складатися, якщо вони задовольняють наступним умовам. Інший блок аргументів у синтаксисі – диапазон_условия1; условие1. Вони дозволяють вибрати в конкретному масиві по першому фактору потрібні комірки, які сумуються в межах початкового інтервалу. Додатковими критеріями виступають наступні диапазон_условия10; условие10.

Особливості функції

Якщо порівнювати «sumif» і «sumifs», то розташування аргументів у синтаксисі зазначених операторів розрізняється. У першій функції діапазон підсумовування знаходиться на третій позиції, у другий – на першій. Якщо неправильно ввести дані для «sumifs» в Excel, приклади виявляють помилку. Справа в тому, що розмірність всіх рядків і стовпців діапазонів однакова для даного оператора. «Sumif» допускає різну кількість клітинок в інтервалі підсумовування та умови. У разі перебору першого аргументу виявляються порожні комірки або текстові, вони ігноруються. При прописуванні критерію використовують знак «*». Він означає, що шукати потрібно міститься фрагмент і не стовідсотковий збіг з умовою. Функція «sumifs» в Excel (приклади це підтверджують) допускають довжину рядка до 255 символів. Клітинки, зазначені в діапазоні підсумовування, лише тоді складаються, коли задовольняють всім поставленим умовам. Інакше кажучи, виконується ще логічна функція «І».

Приклад 1

Для закріплення матеріалу користувачеві потрібно самому вирішувати подібні завдання по «sumifs» в Excel. Приклади використання представлені нижче.
Дано 5 стовпців, де вказані: дата; колір; штат; кількість; вартість. Виконується пошук за наступними критеріями: діапазон підсумовування: F5:F11; інтервал першого умови: пошук за кольором; початковий критерій відбору: міститься слово red; діапазон другої умови: пошук за штатом; інший критерій: міститься абревіатура TX.

Приклад 2

Дані 4 стовпця, в яких вказані: А – категорія товару; В – конкретні продукти; З – російський місто; D – обсяг продажу. Перша умова в прикладі 1 - відібрати всі клітинки, що містять слово «овочі». Другий критерій - знайти клітини, що відповідають за місто «Москва». У другому прикладі перша умова містить пошук по фруктам. Другий критерій відноситься до Казані. Щоб правильно була написана формула «sumifs» в Excel, приклади рекомендують винести умови в окремі комірки. Завдяки цьому користувач посилається на конкретну адресу, що виключає помилку у функції.
У другому операторі цифрами позначені аргументи «sumifs»: 1 – діапазон підсумовування (обсяг продажів); 2 – інтервал першої умови (пошук по категорії); 3 – критерій 1 (пошук слова «фрукти»); 4 – діапазон другої умови (знаходження по місту); 5 – критерій 2 (пошук слова «Казань»).

Приклад 3

Дано 2 стовпця. У першому вказані імена співробітників, у другому – обсяг продажів для кожного з них. Необхідно створити 3 функції «sumifs» в Excel. Приклади, як це зробити, показані на зображенні. Для першого оператора вказується діапазон підсумовування (для всіх трьох функцій він однаковий): В2:В5; інтервал першого і другого умов збігається з попереднім, перший критерій відображає рядки, де обсяг продажів не менше 100 другий же – де не більше 500. Для другого оператора вказується попередній діапазон підсумовування. За першим стовпцем йде відбір умов: перше говорить про пошук імен, де міститься літера «а», друге – про перебування продавця, ім'я якого починається на «І». Знайдені значення сумуються. Для третього оператора також вказується попередній діапазон підсумовування. Для обох умов пошук йде в першому стовпці. Початковий критерій відбору: ім'я продавця « <> Іван». Друга умова: обсяг продажів понад 200.

Приклад 4

Надано 3 стовпця, в яких вказана дата, тип витрат, сума на конкретний вид витрати. Необхідно зробити вибірку по двом умовам: Критерій не включає слово «Інші». Умова містить слово «Витрати*». Знак «*» говорить про те, що після введеної інформації йде продовження у клітинці стовпця Ст. В якості самостійного завдання користувач може ввести умову щодо дати за конкретний період: за місяць або тиждень. Щоб це зробити, необхідно в лапках вказати період, наприклад, «>01012017». Другою умовою виступає: « <31.01.2017». Функції Excel завжди докладно описані в довідці Microsoft. Якщо виникають питання, навіть досвідчений користувач не гребує застосувати вбудовану допомогу програми.