Функція SUMIF в Excel: вивчайте на прикладі
Шарлотта Міллер
Що таке функція SUMIF?
SUMIF – це функція, яка використовується для підсумовування значень за єдиним критерієм. Використовуючи цю функцію, ви можете знайти суму чисел, застосовуючи умову в діапазоні. Ця функція належить до функцій математики та тригонометрії. Подібно до назви, це буде підсумовувати, якщо задані критерії задоволено. Ця функція використовується для знаходження суми певних чисел у великому наборі даних.
Як використовувати функцію SUMIF?
SUMIF підсумовує діапазон клітинок відповідно до вказаних критеріїв. Діапазон комірок, до яких має бути застосована умова, обмеження, відповідно до якого буде обчислюватися сума, діапазон комірок, для яких потрібно вказати суму для обчислення відповідно до умови формула.
Формула MS SUMIF
Як правило, дотримуйтеся формату, у якому необхідно надати критерії та діапазон критеріїв тощо.
SUMIF(range,criteria,sum range)
- Діапазон: діапазон комірок, який містить критерії
- Критерії: Умова, яка повинна бути виконана
- Сумовий діапазон: Діапазон клітинок, які потрібно додати, якщо умова виконана.
Приклад 1: Використання SUMIF
Наведені дані таблиці з номерами замовлень і сумою рахунку. Необхідно знайти суму рахунку для окремих замовлень.
Крок 1) Ви хочете знайти суму рахунку для замовлення певного бренду, де номер замовлення починається з «Amzcd».
| S# | Номер замовлення | Bill сума |
|---|---|---|
| 1 | Amzcd 008 | $450 |
| 2 | Amzcd 009 | $500 |
| 3 | AFJK 010 | $1,102 |
| 4 | Amzcd 011 | $850 |
| 5 | Amzcd 012 | $564 |
| 6 | АБВФ 013 | $345 |
| 7 | Amzcd 014 | $10.50 |
| 8 | Amzcd 015 | $835.50 |
| 9 | АБВФ 016 | $1,236 |
| 10 | AFJK 017 | $478.20 |
| 11 | Amzcd 018 | $100.25 |
Крок 2) Виберіть клітинку нижче для суми рахунку та застосуйте формулу, щоб знайти суму рахунку лише для замовлень, яка починається з "Amzcd".
Крок 3) Застосовується формула
'=SUMIF (B2: B12, "Amzcd*," C2: C12)'
- B2: B12 — це діапазон клітинок, де перевірятимуться критерії.
- «Amzcd*» — це критерій, який застосовується на початку номера замовлення.
- C2: C12 — це діапазон комірок, які потрібно додати відповідно до критеріїв
Крок 4) Якщо ви перевірите таблицю, виділені суми рахунків, підсумовані відповідно до наведених критеріїв. Сума рахунку для кожного замовлення, що починається з «Amzcd», вибирається, і сума знайдеться.
Крок 5) Перевірте кінець стовпця Сума рахунку, щоб побачити суму суми рахунку, яка задовольняє вказану умову.
Приклад 2: Як використовувати SUMIF з операторами порівняння
Набір операторів порівняння існує в перевершувати. Функцію SUMIF можна використовувати разом із операторами порівняння. Оператори порівняння можуть використовуватися для визначення умов у функції SUMIF. Отже, якщо операція порівняння повертає справжнє значення, вона підсумовує значення.
Крок 1) У вас є таблиця даних із назвами продуктів, кількістю та ціною. Як можна знайти загальну ціну продукту, для якого Qty більше і дорівнює 100?
| S# | Продукт | Кількість | ціна |
|---|---|---|---|
| 1 | Безалкогольні напої | 100 | $1,789 |
| 2 | Матеріали одягу | 80 | $752 |
| 3 | Харчова олія | 150 | $489 |
| 4 | Горішки | 500 | $2,589 |
| 5 | Файли cookie | 205 | $1,500 |
| 6 | Взуття | 130 | $300 |
| 7 | Електронні предмети | 100 | $4,500 |
| 8 | овочі | 410 | $3,752 |
| 9 | іграшки | 50 | $200 |
| 10 | меблі | 210 | $500 |
Крок 2) Якщо застосувати критерії в логічному форматі «Кількість>=100», формула SUMIF може сформуватися, як показано нижче.
Крок 3) Застосовується формула
=SUMIF (C2: C11,">=100", D2: D11)
- C2: C11 – це діапазон клітинок, де шукатимуть критерії
- '>=100' – це умова, яка виражається за допомогою оператора порівняння більше та дорівнює
- D2: D11 – це діапазон, який потрібно підсумувати відповідно до критеріїв
Крок 4) Виділені комірки – ціни, які задовольняють критеріям, де Qty>=100
Крок 5) Сума значень знаходиться в комірці D13; це сума цін на продукти, кількість яких більше або дорівнює 100.
Приклад 3: Як використовувати SUMIF з датами
У цьому прикладі ви дізнаєтеся, як використовувати функцію SUMIF з датою. Критерії також можна надати у формі дати.
Крок 1) У таблиці показано різні номери рахунків, дати виставлення рахунків і платежі. Суму необхідно сплатити на відповідний рахунок протягом вказаної дати рахунку. За допомогою SUMIF можна спробувати знайти суму до сплати за червень.
| S# | Номер рахунку | Bill Дата | Оплата |
|---|---|---|---|
| 1 | 9160250011660588 | 1/4/2019 | 25000 |
| 2 | 9160250011689568 | 1/28/2019 | 15000 |
| 3 | 9160250012356588 | 1/31/2019 | 8500 |
| 4 | 9160250899560588 | 6/2/2019 | 9635 |
| 5 | 9160260231660588 | 2/10/2019 | 4582 |
| 6 | 9160250011658588 | 6/25/2019 | 10265 |
| 7 | 9160457811660588 | 3/1/2019 | 8132 |
| 8 | 9160250011670858 | 3/12/2019 | 9562 |
| 9 | 9160250011663569 | 6/15/2019 | 7895 |
| 10 | 9160250321660456 | 6/18/2019 | 2566 |
Крок 2) Оскільки дата є з вами, критерії можуть мати вигляд «>5», що відноситься до дат після травня. Тут оператор порівняння та дата використовуються одночасно з SUMIF.
Крок 3) Застосовується формула
=SUMIF (C2: C11,">5/31/2019", D2: D11)
- C2: C11 – це діапазон комірок, у яких указано дати, і це діапазон, у якому шукатимуть задані критерії
- «>5» — це критерій, де дата перевищує «31».
- D2: діапазон чисел D11, які потрібно додати після виконання умови
Крок 4) Нижче наведено значення клітинок, які задовольняють застосовану умову, і знайдемо суму цих значень.
Крок 5) Сума вказана в D13. І це загальна сума, яку потрібно сплатити на кілька рахунків за червень.
Завантажте наведений вище файл даних Excel
Підсумки
- Функція SUMIF створить суму значень відповідно до заданих критеріїв.
- Функція SUMIF сумісна з числовими даними, датами та текстовими даними
- Умови можуть виражатися з результату інших функцій або операторів
- Функцію SUMIF можна застосовувати лише для одного стовпця за раз
- Унікальну умову можна перевірити за допомогою функції SUMIF
- #VALUE буде результатом помилки, якщо діапазон не збігається з діапазоном критеріїв
- Частину слова або речення буде зіставлено за допомогою символу підстановки. Символ зірочок використовується разом із серією символів і знаком питання для одного символу.
- У разі використання тексту разом із числовими значеннями критерії повинні укладатися в подвійні лапки, але не якщо вони складаються лише з числового значення.
