Функція Excel COUNTIF дозволяє підрахувати кількість комірок, які відповідають певним критеріям або умовам у заданому діапазоні.
Функція COUNTIF — одна зі статистичних функцій Excel, яка є комбінацією функцій COUNT і IF або функції COUNTA. При використанні у формулі функція підраховує кількість комірок, які відповідають певним критеріям чи умовам у тому самому чи кількох діапазонах. Функція COUNTIF допомагає підраховувати клітинки, що містять текст, числа або дати, які відповідають певним критеріям.
Ви можете підрахувати клітинки за допомогою функцій COUNTIF або COUNTIFS в Excel. Різниця між функціями COUNTIF і COUNTIFS полягає в тому, що COUNTIF використовується для підрахунку комірок, які відповідають одному критерію в одному діапазоні, тоді як COUNTIFS підраховує клітинки, які відповідають кільком умовам в одному або кількох діапазонах.
У цій статті буде показано, як використовувати дві функції COUNTIF і COUNTIFS в Excel.
Функція COUNTIF в Excel
Функція COUNTIF дозволяє виконувати підрахунок даних на основі певного критерію або умови. Умова, що використовується у функції, працює з логічними операторами (, , =, >=, <=) і символами підстановки (*, ?) для часткового збігу.
Синтаксис функції COUNTIF
Структура функції COUNTIF:
=COUNTIF(діапазон,критерії)Параметри:
діапазон– Діапазон клітинок для підрахунку.критерії– Умова визначає, які клітинки мають бути включені до числа у вказаному діапазоні. Критеріями можуть бути числове значення, текст, посилання на адресу комірки або рівняння.
Використання функції COUNTIF для підрахунку числових значень
Як ми обговорювали вище, критерій (другий аргумент) у функції COUNTIF визначає умову, яка вказує функції, які клітинки рахувати.
Ця функція допомагає вам підрахувати кількість комірок зі значеннями, які відповідають логічним умовам, таким як дорівнює, більше, менше або не дорівнюють заданому значенню тощо.
У наведеному нижче прикладі формула підраховує клітинки, які містять значення, рівне 5 (критерії). Ви можете безпосередньо вставити «5» у формулу або використати посилання на адресу комірки, яка має значення (комірка D2 у прикладі нижче).
=КІЛЬКИ(B2:B11,D2)
Наведена вище формула підраховує кількість клітинок у діапазоні комірок (B2:B11), які містять значення, що дорівнює значенню в клітинці D2.
Наступна формула підраховує клітинки, значення яких менше 5.
=COUNTIF(B2:B11,"<5")Оператор «менше ніж» (<) повідомляє формулі про підрахунок клітинок зі значенням менше «5» у діапазоні B2:B11. Кожен раз, коли ви використовуєте оператор у умові, не забудьте взяти його в подвійні лапки («»).
Іноді, коли потрібно порахувати клітинки, дослідивши їх за критерієм (значенням) у клітинці. У таких випадках створіть критерій, об’єднавши оператор і посилання на клітинку. Коли ви це зробите, вам потрібно взяти оператор порівняння в подвійні лапки («»), а потім помістити амперсанд (&) між оператором порівняння та посиланням на клітинку.
=COUNTIF(B2:B11,">="&D2)
На малюнку нижче показано кілька прикладів формул і їх результат.
Використання функції COUNTIF для підрахунку текстових значень
Щоб підрахувати клітинки, які містять певні текстові рядки, використовуйте цей текстовий рядок як аргумент критерію або клітинку, яка містить текстовий рядок. Наприклад, у наведеній нижче таблиці, якщо ми хочемо порахувати всі клітинки діапазону (B21:D27) з текстовим значенням у клітинці B21 (sam), ми можемо використовувати таку формулу:
=КІЛЬКИ(B21:D27,B21)
Як ми обговорювали раніше, ми можемо використовувати текст «sam» безпосередньо у формулі або використовувати посилання на клітинку, що має критерії (B21). Текстовий рядок завжди має бути взятий у подвійні лапки («»), коли він використовується у формулі в Excel.
=КІЛЬКИ(B21:D27,"сам")
Щоб підрахувати клітинки, які не містять вказаного тексту, скористайтеся наведеною нижче формулою:
=COUNTIF(B21:D27,""&B21)Обов’язково додайте «не дорівнює» "" оператор у подвійних лапках.
Якщо ви використовуєте текст «sam» безпосередньо у формулі, вам потрібно укласти оператор «» і текстовий рядок разом ("сем") у подвійних лапках.
=КІЛЬКИ(B21:D27,"сам") 
Використання підстановкових знаків у функції Excel COUNTIF (часткова відповідність)
Ви можете використовувати формулу COUNTIF із символами підстановки, щоб підрахувати клітинки, які містять конкретне слово, фразу або літери. У функції Excel COUNTIF можна використовувати три символи підстановки:
*(зірочка) – використовується для підрахунку клітинок з будь-якою кількістю початкових і кінцевих символів/букв. (наприклад, St* може означати Старк, Лелека, Стеки тощо.?(знак питання) – використовується для пошуку клітинок з будь-яким одним символом. (наприклад, St?rk може означати Старк або Лелека.~(тильда) – використовується для пошуку та підрахунку кількості клітинок, що містять знак питання або зірочку (~, *, ?) у тексті.
Підрахунок клітинок, які починаються або закінчуються певними символами
Щоб підрахувати клітинки, які починаються або закінчуються певним текстом з будь-якою кількістю інших символів у клітинці, використовуйте символ зірочки (*) у другому аргументі функції COUNTIF.
Використовуйте ці зразки формули:
=COUNTIF(A1:A10,"A*") – підрахувати клітинки, які починаються на «А».
=COUNTIF(A19:A28,"*er") – підрахувати кількість клітинок, які закінчуються символами «ер».
=COUNTIF(A2:A12,"*QLD*") – для підрахунку комірок, які містять текст «QLD» у будь-якому місці текстового рядка.
A ? представляє рівно один символ, використовуйте цей підстановковий знак у функції COUNTIF нижче, щоб підрахувати кількість клітинок, які містять рівно +1 символ, де «?' використовується.
=COUNTIF(A1:A10,"Par?s")
Підрахунок порожніх і непорожніх комірок за допомогою функції COUNTIF
Формула COUNTIF також корисна, коли справа доходить до підрахунку кількості порожніх або непорожніх комірок у заданому діапазоні.
Підрахуйте непусті клітинки
Якщо ви хочете рахувати лише клітинки, які містять будь-які значення «текст», скористайтеся наведеною нижче формулою. Ця формула розглядає клітинки з датами та номерами як порожні клітинки і не включатиме їх у підрахунок.
=COUNTIF(A1:B12,"*")Підстановка * відповідає лише текстовим значенням і повертає кількість усіх текстових значень у заданому діапазоні.
Якщо ви хочете підрахувати всі непорожні клітинки в заданому діапазоні, спробуйте цю формулу:
=COUNTIF(A1:B12,"")
Підрахуйте пусті клітинки
Якщо ви хочете підрахувати порожні клітинки в певному діапазоні, скористайтеся функцією COUNTIF з * символ підстановки та оператор у аргументі критерію для підрахунку порожніх комірок.
Ця формула підраховує клітинки, які не містять жодних текстових значень:
=COUNTIF(A1:B12,""&"*")Оскільки * символ підстановки збігається з будь-яким текстовим значенням, наведена вище формула підраховує всі клітинки, яким не дорівнюють *. Він також підраховує клітинки з датами та числами як пробіли.
Щоб підрахувати всі пробіли (усі типи значень):
=COUNTIF(A1:B12,"")Ця функція підраховує лише порожні клітинки в діапазоні.
Використання функції COUNTIF для підрахунку дат
Ви можете рахувати клітинки з датами (так само, як і з числовими критеріями), які відповідають логічній умові або вказаній даті чи даті у контрольній клітинці.
Щоб підрахувати клітинки, які містять вказану дату (05-05-2020), ми б використали цю формулу:
=COUNTIF(B2:B10,"05-05-2020")
Ви також можете вказати дату в різних форматах як критерії у функції COUNTIF, як показано нижче:
Якщо ви хочете підрахувати клітинки, які містять дати до або після певної дати, використовуйте оператори менше (до) або більше (після) разом із конкретною датою або посиланням на клітинку.
=COUNTIF(B2:B10,">=05/05/2020")
Ви також можете використовувати посилання на клітинку, що містить дату, об’єднавши її з оператором (у подвійних лапках).
Щоб підрахувати кількість клітинок у діапазоні A2:A14 з датою перед датою в E3, скористайтеся наведеною нижче формулою, де оператор більше (<) означає перед датою в E3.
=COUNTIF(A2:A14,"<"&E3)
Кілька прикладів формул і їх результат:
Дата підрахунку на основі поточної дати
Ви можете об’єднати функцію COUNTIF з певними функціями дати Excel, тобто TODAY(), щоб підрахувати клітинки, які мають поточну дату.
=КІЛЬКИ(A2:A14,">"&СЬОГОДНІ())Ця функція підраховує всі дати з сьогоднішнього дня в діапазоні (A2:A14).
Підрахунок дат між певним діапазоном дат
Якщо ви хочете підрахувати всі дати між двома датами, вам потрібно використовувати два критерії у формулі.
Ми можемо зробити це за допомогою двох методів: функцій COUNTIF і COUNTIFS.
Використання функції COUNTIF Excel
Вам потрібно використовувати дві функції COUNTIF, щоб підрахувати всі дати між двома вказаними датами.
Щоб підрахувати дати між ’09-02-2020′ і ’20-08-2021′, скористайтеся цією формулою:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">20-08-2021")Ця формула спочатку знаходить кількість клітинок, які мають дату після 2 лютого, і віднімає кількість клітинок із датами після 20 серпня. Тепер ми отримуємо номер. клітинок із датами після 2 лютого і до 20 серпня (кількість 9).
Якщо ви не хочете, щоб формула рахувалася як 2 лютого, так і 20 серпня, скористайтеся цією формулою:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">=20-08-2021")Просто замініть оператор «>» на «>=» у другому критерії.
Використання функції COUNTIFS Excel
Функція COUNTIFS також підтримує декілька критеріїв, і на відміну від функції COUNTIFS вона підраховує клітинки лише після виконання всіх умов. Якщо ви хочете підрахувати клітинки з усіма датами між двома вказаними датами, введіть цю формулу:
=COUNTIFS(A2:A14,">"&A11,A2:A14,"<"&A10)
Якщо ви хочете також включити вказані дати до підрахунку, використовуйте оператори «>=» і «<=». Ось, перейдіть за цією формулою:
=COUNTIFS(A2:A14,">=09-02-2020",A2:A14,"<=20-08-2021")У цьому прикладі ми використали дату безпосередньо в критеріях замість посилання на клітинку.
Як обробляти COUNTIF і COUNTIFS з кількома критеріями в Excel
Функція COUNTIF в основному використовується для підрахунку комірок з одним критерієм (умовою) в одному діапазоні. Але ви все одно можете використовувати COUNTIF для підрахунку клітинок, які відповідають кільком умовам в одному діапазоні. Проте функцію COUNTIFS можна використовувати для підрахунку комірок, які відповідають кільком умовам в однакових або різних діапазонах.
Як рахувати числа в діапазоні
Ви можете рахувати клітинки, що містять числа між двома вказаними числами, використовуючи дві функції: COUNTIF і COUNTIFS.
COUNTIF для підрахунку чисел між двома числами
Одним із поширених застосувань функції COUNTIF з кількома критеріями є підрахунок чисел між двома вказаними числами, напр. для підрахунку чисел, більших за 10, але менших за 50. Щоб підрахувати числа в межах діапазону, об’єднайте дві чи більше функцій COUNTIF в одну формулу. Давайте покажемо вам, як.
Скажімо, ви хочете підрахувати клітинки в діапазоні B2:B9, де значення більше 10 і менше 21 (не включаючи 10 і 21), скористайтеся цією формулою:
=COUNTIF(B2:B14,">10")-COUNTIF(B2:B14,">=21")Різницю двох чисел знаходять шляхом віднімання однієї формули від іншої. Перша формула підраховує числа, більші за 10 (що дорівнює 7), друга формула повертає кількість чисел, більших або рівних 21 (що дорівнює 4), а результат другої формули віднімається від першої формули (7 -4), щоб отримати кількість чисел між двома числами (3).
Якщо ви хочете підрахувати клітинки з числом більше 10 і менше 21 в діапазоні B2:B14, включаючи числа 10 і 21, скористайтеся цією формулою:
=COUNTIF(B2:B14,">=10")-COUNTIF(B2:B14,">21")
COUNTIFS для підрахунку чисел між 2 числами
Щоб підрахувати числа від 10 до 21 (за винятком 10 і 21), які містяться в клітинках B2-B9, використовуйте цю формулу:
=COUNTIFS(B2:B14,">10",B2:B14,"<21")
Щоб включити 10 і 21 до підрахунку, просто використовуйте оператори «більше або дорівнює» (>=) замість «більше» і «менше або дорівнює» (<=) замість операторів «менше ніж» у формулах .
COUNTIFS для підрахунку клітинок за кількома критеріями (Критерії І)
Функція COUNTIFS є множинним аналогом функції COUNTIF, яка підраховує клітинки на основі двох або більше критеріїв у тому самому або кількох діапазонах. Вона відома як логіка «І», оскільки функція призначена для підрахунку комірок лише тоді, коли всі дані умови є ІСТИННИМИ.
Наприклад, ми хочемо дізнатися, скільки разів (кількість клітинок) цей хліб (значення в стовпці A) було продано менше 5 (значення в стовпці C).
Ми можемо використовувати цю формулу:
=COUNTIFS(A2:A14,"Хліб",C2:C14,"<5")
COUNTIF для підрахунку осередків за кількома критеріями (критерії АБО)
Якщо ви хочете підрахувати кількість комірок, які відповідають кільком критеріям в одному діапазоні, об’єднайте дві чи більше функцій COUNTIF. Наприклад, якщо ви хочете дізнатися, скільки разів «Хліб» або «Сир» повторюються у зазначеному діапазоні (A2:A14), скористайтеся наведеною нижче формулою:
=COUNTIF(A2:A14,"Хліб")+COUNTIF(A2:A14,"Cheese")Ця формула підраховує клітинки, для яких принаймні одна з умов є TRUE. Тому це називається логікою АБО.
Якщо ви хочете оцінити більше одного критерію в кожній із функцій, краще використовувати COUNTIFS замість COUNTIF. У наведеному нижче прикладі ми хочемо отримати кількість статусів «Замовлено» та «Доставлено» для «Хліб», тому ми будемо використовувати цю формулу:
=COUNTIFS(A2:A14,"Хліб",C2:C14,"Замовлено")+COUNTIFS(A2:A14,"Хліб",C2:C14,"Доставлено")
Сподіваємося, що цей простий, але досить довгий посібник дасть вам уявлення про те, як використовувати функції COUNTIF і COUNTIF в Excel.