У цьому підручнику наведено детальну демонстрацію того, як використовувати функції SUMIF та SUMIFS у Google Таблицях із формулами та прикладами.
SUMIF — одна з математичних функцій у Google Таблицях, яка використовується для умовного підсумовування комірок. По суті, функція SUMIF шукає конкретну умову в діапазоні комірок, а потім складає значення, які відповідають заданій умові.
Наприклад, у вас є список витрат у таблицях Google, і ви хочете лише підсумувати витрати, які перевищують певне максимальне значення. Або у вас є список позицій замовлення та їх відповідні суми, і ви хочете знати лише загальну суму замовлення певного товару. Ось тут і стає в нагоді функція SUMIF.
SUMIF можна використовувати для підсумовування значень на основі умов числа, умов тексту, умови дати, символів підстановки, а також на основі порожніх і непорожніх клітинок. У Google Sheets є дві функції для підсумовування значень на основі критеріїв: SUMIF і SUMIFS. Функція SUMIF складає числа на основі однієї умови, тоді як SUMIFS підсумовує числа на основі кількох умов.
У цьому підручнику ми пояснимо, як використовувати функції SUMIF і SUMIFS у Google Таблицях для підсумовування чисел, які відповідають певним умовам.
Функція SUMIF в Google Sheets – синтаксис і аргументи
Функція SUMIF — це просто комбінація функцій SUM і IF. Функція IF сканує діапазон комірок для заданої умови, а потім функція SUM підсумовує числа, що відповідають клітинкам, які відповідають умові.
Синтаксис функції SUMIF:
Синтаксис функції SUMIF у Google Таблицях такий:
=SUMIF(діапазон, критерії, [діапазон_суми])Аргументи:
діапазон - Діапазон клітинок, у якому ми шукаємо клітинки, які відповідають критеріям.
критерії – Критерії, які визначають, які клітинки потрібно додати. Ви можете заснувати критерій на основі числа, текстового рядка, дати, посилання на клітинку, виразу, логічного оператора, символу підстановки, а також інших функцій.
діапазон_сум – Цей аргумент необов’язковий. Це діапазон даних зі значеннями, які підсумовуються, якщо відповідний запис діапазону відповідає умові. Якщо ви не включите цей аргумент, натомість підсумовується «діапазон».
Тепер давайте подивимося, як використовувати функцію SUMIF для підсумовування значень за різними критеріями.
Функція SUMIF з числовими критеріями
Ви можете підсумовувати числа, які відповідають певним критеріям, у діапазоні клітинок, використовуючи один із наступних операторів порівняння для створення критеріїв.
- більше ніж (>)
- менше ніж (<)
- більше або дорівнює (>=)
- менше або дорівнює (<=)
- дорівнює (=)
- не дорівнює ()
Припустимо, що у вас є така електронна таблиця, і вас цікавить загальний обсяг продажів, який становить 1000 або більше.
Ось як ви можете ввести функцію SUMIF:
Спочатку виділіть клітинку, у якій має відображатися результат суми (D3). Щоб підсумувати числа в B2:B12, які більше або дорівнюють 1000, введіть цю формулу та натисніть «Enter»:
=SUMIF(B2:B12,">=1000",B2:B12)У цьому прикладі формули аргументи діапазону та суми_діапазону (B2:B12) однакові, оскільки показники продажів і критерії застосовуються до одного діапазону. І ми ввели число перед оператором порівняння та взяли його в лапки, оскільки критерії завжди мають бути узяті в подвійні лапки, за винятком посилання на клітинку.
Формула шукала числа, що перевищують або дорівнюють 1000, а потім складала всі відповідні значення та показувала результат у клітинці D3.
Оскільки аргументи range і sum_range однакові, ви можете досягти того ж результату без аргументів sum_range у формулі, наприклад:
=SUMIF(B2:B12,">=1000")
Або ви можете вказати посилання на клітинку (D2), яке містить число замість критерію числа, і приєднати оператор порівняння з цим посиланням на клітинку в аргументі критерію:
=SUMIF(B2:B12,">="&D2)Як ви можете бачити, оператор порівняння все ще вводиться в подвійних лапках, а оператор і посилання на клітинку об’єднуються амперсандом (&). І вам не потрібно брати посилання на клітинку в лапки.
Примітка: Коли ви посилаєтеся на клітинку, яка містить критерії, переконайтеся, що не залишаєте пробілів на початку або в кінці значення в клітинці. Якщо у вашому значенні є непотрібний пробіл до або після значення в клітинці, на яку посилається, то в результаті формула поверне «0».
Ви також можете використовувати інші логічні оператори таким же чином, щоб зробити умови в аргументі критерію. Наприклад, щоб підсумувати значення менше 500:
=SUMIF(B2:B12,"<500")
Сума, якщо числа дорівнюють
Якщо ви хочете додати числа, що дорівнюють певному числу, ви можете або ввести лише число, або ввести число зі знаком рівності в аргумент критерію.
Наприклад, щоб підсумувати відповідні суми продажу (стовпець B) для кількостей (стовпець C), значення яких дорівнюють 20, спробуйте будь-яку з цих формул:
=SUMIF(C2:C12,"=20",B2:B12)
=SUMIF(C2:C12,"20",B2:B12)
=SUMIF(C2:C12,E2,B2:B12)
Щоб підсумувати числа в стовпці B з кількістю, що не дорівнює 20, у стовпці C, спробуйте цю формулу:
=SUMIF(C2:C12,"20",B2:B12)
Функція SUMIF з текстовими критеріями
Якщо ви хочете додати числа в діапазон комірок (стовпець або рядок), що відповідає клітинкам, які мають певний текст, ви можете просто включити цей текст або клітинку, яка містить текст, до аргументу критерію вашої формули SUMIF. Зверніть увагу, що текстовий рядок завжди має бути взятий у подвійні лапки (" ").
Наприклад, якщо вам потрібна загальна сума продажів у «Західному» регіоні, ви можете використати наведену нижче формулу:
=SUMIF(C2:C13,"Захід",B2:B13)У цій формулі функція SUMIF шукає значення «Захід» у діапазоні комірок C2:C13 і додає відповідну вартість продажів у стовпці B. Потім відображає результат у клітинці E3.
Ви також можете посилатися на клітинку, яка містить текст замість використання тексту в аргументі критерію:
=SUMIF(C2:C12,E2,B2:B12)
Тепер давайте отримаємо загальний дохід усіх регіонів, крім «Західного». Для цього ми будемо використовувати не дорівнює оператору () у формулі:
=SUMIF(C2:C12,""&E2,B2:B12)
SUMIF з підстановочними знаками
У наведеному вище методі функція SUMIF з текстовими критеріями перевіряє діапазон на відповідність точно вказаному тексту. Потім він підсумовує числа, які співпадають, до точного тексту та ігнорує всі інші числа, включаючи частково відповідний текстовий рядок. Щоб підсумувати числа з частково відповідними текстовими рядками, ви повинні налаштувати один із наступних символів підстановки у своїх критеріях:
?(знак питання) використовується для відповідності будь-якому окремому символу в будь-якому місці текстового рядка.*(зірочка) використовується для пошуку відповідних слів разом із будь-якою послідовністю символів.~(тильда) використовується для поєднання текстів зі знаком питання (?) або символом зірочки (*).
Ми наведемо цей приклад електронної таблиці для продуктів та їх кількості для підсумовування чисел із символами підстановки:
Зірочка (*) Підстановка
Наприклад, якщо ви хочете підсумувати кількість всіх продуктів Apple, скористайтеся цією формулою:
=SUMIF(A2:A14,"Apple*",B2:B14)Ця формула SUMIF знаходить усі продукти зі словом «Apple» на початку та будь-якою кількістю символів після нього (позначається «*»). Як тільки збіг знайдено, він підсумовує Кількість числа, що відповідають відповідним текстовим рядкам.
У критеріях також можна використовувати декілька символів підстановки. Ви також можете вводити символи підстановки з посиланнями на клітинки замість прямого тексту.
Для цього символи підстановки повинні бути узяті в подвійні лапки (“ “) і з’єднані з посиланням на клітинку:
=SUMIF(A2:A14,"*"&D2&"*",B2:B14)Ця формула підсумовує кількість всіх продуктів, у яких є слово «Redmi», незалежно від того, де це слово розташоване в рядку.
Знак питання (?) Підстановка
Ви можете використовувати знак питання (?), щоб зіставити текстові рядки з будь-якими окремими символами.
Наприклад, якщо ви хочете знайти кількість всіх варіантів Xiaomi Redmi 9, ви можете використовувати цю формулу:
=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)Наведена вище формула шукає текстові рядки зі словом «Xiaomi Redmi 9», за яким слідують будь-які окремі символи та підсумовує відповідні Кількість числа.
Тильда (~) Підстановка
Якщо ви хочете знайти відповідність дійсному знаку питання (?) або символу зірочки (*), вставте символ тильди (~) перед символом підстановки в частині умови формули.
Щоб додати кількості в стовпці B з відповідним рядком зі знаком зірочки в кінці, введіть формулу нижче:
=SUMIF(A2:A14,"Samsung Galaxy V~*",B2:B14)
Щоб додати кількості в стовпці B, які мають знак питання (?) у стовпці A в тому самому рядку, спробуйте наступну формулу:
=SUMIF(A2:A14,"~?",B2:B14)
Функція SUMIF з критеріями дати
Функція SUMIF також може допомогти вам умовно підсумувати значення на основі критеріїв дати – наприклад, чисел, що відповідають певній даті, або до дати, або після дати. Ви також можете використовувати будь-який з операторів порівняння зі значенням дати, щоб створити критерії дати для підсумовування чисел.
Дата має бути введена у форматі дати, який підтримується в таблицях Google, або як посилання на клітинку, що містить дату, або за допомогою функції дати, наприклад DATE() або TODAY().
Ми будемо використовувати цей приклад електронної таблиці, щоб показати вам, як працює функція SUMIF з критеріями дати:
Припустимо, ви хочете підсумувати суми продажів, які відбулися 29 листопада 2019 року або раніше (<=) у наведеному вище наборі даних, ви можете додати ці цифри продажів за допомогою функції SUMIF одним із цих способів:
=SUMIF(C2:C13,"<=29 листопада 2019 року",B2:B13)Наведена вище формула перевіряє кожну клітинку від C2 до C13 і відповідає лише тим клітинкам, які містять дати до 29 листопада 2019 року (29.11.2019). Потім підсумовує суму продажів, що відповідає відповідним клітинкам із діапазону комірок B2:B13, і відображає результат у клітинках E3.
Дату можна вказати до формули в будь-якому форматі, який розпізнається Google Таблицями, наприклад «29 листопада 2019 року», «29 листопада 2019 року» або «29/11/2019» тощо. Запам’ятайте значення дати, а оператор повинен завжди брати в подвійні лапки.
Ви також можете використовувати функцію DATE() у критеріях замість прямого значення дати:
=SUMIF(C2:C13,"<="&DATE(2019,11,29),B2:B13)
Або ви можете використовувати посилання на клітинку замість дати в частині критеріїв формули:
=SUMIF(C2:C13,"<="&E2,B2:B13)
Якщо ви хочете додати суми продажів на основі сьогоднішньої дати, ви можете використовувати функцію СЬОГОДНІ() у аргументі критерію.
Наприклад, щоб підсумувати будь-які суми продажів на сьогоднішню дату, скористайтеся цією формулою:
=SUMIF(C2:C13,СЬОГОДНІ(),B2:B13)
Функція SUMIF з пустими або непустими клітинками
Іноді може знадобитися підсумувати числа в діапазоні клітинок з пустими або непустими клітинками в одному рядку. У таких випадках можна використовувати функцію SUMIF для підсумовування значень на основі критеріїв, де клітинки порожні чи ні.
Сума, якщо пуста
У Google Таблицях є два критерії, щоб знайти порожні клітинки: «» або «=».
Наприклад, якщо ви хочете підсумувати всю суму продажу, яка містить рядки нульової довжини (візуально виглядає пустою) у стовпці C, використовуйте подвійні лапки без пробілів у формулі:
=SUMIF(C2:C13,"",B2:B13)
Щоб підсумувати всю суму продажів у стовпці B із заповненими пустими клітинками в стовпці C, додайте «=» як критерій:
=SUMIF(C2:C13,"=",B2:B13)
Сума, якщо не пуста:
Якщо ви хочете підсумувати клітинки, які містять будь-яке значення (не порожнє), ви можете використовувати «» як критерій у формулі:
Наприклад, щоб отримати загальну суму продажів з будь-якими датами, скористайтеся цією формулою:
=SUMIF(C2:C13,"",B2:B13)
SUMIF На основі кількох критеріїв з логікою АБО
Як ми вже бачили, функція SUMIF призначена для підсумовування чисел лише за одним критерієм, але можна підсумовувати значення на основі кількох критеріїв за допомогою функції SUMIF в Google Таблицях. Це можна зробити, об’єднавши більше ніж одну функцію SUMIF в одну формулу з логікою АБО.
Наприклад, якщо ви хочете підсумувати суму продажів у регіоні «Захід» або «Південний» регіон (логіка АБО) у зазначеному діапазоні (B2:B13), використовуйте цю формулу:
=SUMIF(C2:C13,"Захід",B2:B13)+SUMIF(C2:C13,"Південь",B2:B13)Ця формула підсумовує клітинки, якщо хоча б одна з умов TRUE. Тому це відоме як «логіка АБО». Він також підсумовує значення, коли виконуються всі умови.
Перша частина формули перевіряє діапазон C2:C13 на наявність тексту «Захід» і підсумовує значення в діапазоні B2:B13, коли відповідність знайдено. Секундна частина перевіряє текстове значення «Південь» у тому самому діапазоні C2:C13, а потім підсумовує значення з відповідним текстом у тому самому діапазоні суми B2:B13. Потім обидві суми додаються разом і відображаються в клітинці E3.
У випадках, коли виконується лише один критерій, він повертає лише це значення суми.
Ви також можете використовувати декілька критеріїв замість одного чи двох. І якщо ви використовуєте кілька критеріїв, краще використовувати посилання на клітинку як критерій, а не писати пряме значення у формулі.
=ПІДСУМКА(C2:C13,E2,B2:B13)+СУМКА(C2:C13,E3,B2:B13)+СУМКА(C2:C13,E4,B2:B13)
SUMIF з логікою АБО додає значення, коли виконується хоча б один із зазначених критеріїв. Але якщо ви хочете підсумовувати значення лише тоді, коли виконуються всі вказані умови, вам потрібно використовувати його нову функцію SUMIFS().
Функція SUMIFS у таблицях Google (кілька критеріїв)
Коли ви використовуєте функцію SUMIF для підсумовування значень на основі кількох критеріїв, формула може стати занадто довгою та складною, і ви схильні робити помилки. Крім того, SUMIF дозволить вам підсумовувати значення лише в одному діапазоні і коли будь-яка з умов TRUE. Ось тут і з’являється функція SUMIFS.
Функція SUMIFS допомагає підсумовувати значення на основі кількох критеріїв відповідності в одному або кількох діапазонах. І він працює за логікою І, тобто може лише підсумовувати значення лише тоді, коли виконуються всі задані умови. Навіть якщо одна умова помилкова, вона поверне «0».
Синтаксис і аргументи функції SUMIFS
Синтаксис функції SUMIFS такий:
=SUMIFS(сума_діапазон, критерій_діапазон1, критерій1, [діапазон_критеріїв2, ...], [критерій2, ...])де,
- діапазон_суму – Діапазон комірок, що містить значення, які потрібно підсумувати, коли виконуються всі умови.
- діапазон_критеріїв1 – Це діапазон комірок, де ви перевіряєте критерії1.
- критерії1 – Це умова, яку вам потрібно перевірити за критерієм_range1.
- крiteria_range2, Criterion2, …– Додаткові діапазони та критерії для оцінки. І ви можете додати більше діапазонів та умов до формули.
Ми використаємо набір даних на наступному знімку екрана, щоб продемонструвати, як функція SUMIFS працює з різними критеріями.
SUMIFS з текстовими умовами
Ви можете підсумовувати значення на основі двох різних текстових критеріїв у різних діапазонах. Наприклад, скажімо, ви хочете дізнатися загальну суму продажів доставленого намету. Для цього скористайтеся такою формулою:
=SUMIFS(D2:D13,A2:A13,"Намет",C2:C13,"Доставлено")У цій формулі ми маємо два критерії: «Намет» і «Доставлено». Функція SUMIFS перевіряє елемент «Намет» (критерії1) у діапазоні A2:A13 (діапазон_критеріїв1) і перевіряє статус «Доставлено» (критерії2) у діапазоні C2:C13 (діапазон_критеріїв2). Коли обидві умови виконуються, він підсумовує відповідне значення в діапазоні комірок D2:D13 (діапазон_суми).
SUMIFS з числовими критеріями та логічними операторами
Ви можете використовувати умовні оператори, щоб створити умови з числами для функції SUMIFS.
Щоб знайти загальний обсяг продажів більше ніж 5 кількостей будь-якого товару в штаті Каліфорнія (CA), скористайтеся цією формулою:
=SUMIFS(E2:E13,D2:D13,">5",B2:B13,"CA")Ця формула має дві умови: «>5» і «CA».
Ця формула перевіряє кількість (кількість) більше 5 в діапазоні D2:D13 і перевіряє стан «CA» в діапазоні B2:B13. І коли обидві умови виконуються (це означає, що вони знаходяться в одному рядку), сума підсумовується в E2:E13.
SUMIFS з критеріями дати
Функція SUMIFS також дозволяє перевіряти кілька умов в одному діапазоні, а також у різних діапазонах.
Припустимо, ви хочете перевірити загальну суму продажів поставлених товарів після 31.05.2021 і до дати 06.10.2021, тоді скористайтеся цією формулою:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)Наведена вище формула має три умови: 31.05.2021, 5.10.2021 і Доставлено. Замість використання прямих значень дати та тексту ми посилалися на клітинки, що містять ці критерії.
Формула перевіряє наявність дат після 31.05.2021 (G1) і дат до 6.10.2021 (G2) у тому самому діапазоні D2:D13, а також перевіряє статус «Доставлено» між цими двома датами. Потім підсумовує відповідну суму в діапазоні E2:E13.
SUMIFS з пустими та непустими клітинками
Іноді може знадобитися знайти суму значень, коли відповідна клітинка порожня чи ні. Для цього ви можете використовувати один із трьох критеріїв, які ми обговорювали раніше: «=», «» і «».
Наприклад, якщо ви бажаєте лише підсумувати кількість товарів «Намет», для яких дата доставки ще не підтверджена (порожні клітинки), ви можете використовувати критерії «=»:
=SUMIFS(D2:D13,A2:A13,"Намет",C2:C13,"=")Формула шукає елемент «Намет» (критерії1) у стовпці A з відповідними пробілами (критеріїв2) у стовпці C, а потім підсумовує відповідну суму в стовпці D. «=» представляє повністю порожню клітинку.
Щоб знайти загальну кількість товарів «Намет», для яких підтверджена дата доставки (не порожні клітинки), використовуйте «» як критерій:
=SUMIFS(D2:D13,A2:A13,"Намет",C2:C13,"")У цій формулі ми просто поміняли «=» на «». Він знаходить суму елементів намету з непустими клітинками в стовпці C.
SUMIFS з логікою АБО
Оскільки функція SUMIFS працює за логікою І, вона підсумовує лише тоді, коли виконуються всі умови. Але що робити, якщо ви хочете підсумувати значення на основі кількох критеріїв, коли виконується будь-який із критеріїв. Хитрість полягає у використанні кількох функцій SUMIFS.
Наприклад, якщо ви хочете додати суму продажів для «Велосипедного багажника» АБО для «Рюкзака», коли їх статус «Замовлено», спробуйте цю формулу:
=SUMIFS(D2:D13,A2:A13,"Велосипедна стійка",C2:C13,"Замовлено") +SUMIFS(D2:D13,A2:A13,"Рюкзак",C2:C13,"Замовлено")Перша функція SUMIFS перевіряє два критерії «Рюкзак» і «Замовлено» та підсумовує значення сум у стовпці D. Потім друга SUMIFS перевіряє два критерії «Рюкзак» і «Замовлено» та підсумовує значення суми в стовпці D. А потім , обидві суми додаються разом і відображаються на F3. Простіше кажучи, ця формула підсумовується, коли замовлено «стійка для велосипеда» або «рюкзак».
Це все, що вам потрібно знати про функції SUMIF і SUMIFS в Google Таблицях.