Функція Excel COUNTIF – це категорія статистичних функцій, що призначена для підрахунку числових значень, дат або текстових значень, які відповідають певному критерію (наприклад, скільки разів філіал з’явлється у списку клієнтів).
Функцію COUNTIF можна використовувати для вирішення різних завдань. Наприклад, COUNTIF Excel можна застосувати для підрахунку клітинок, що містять число, більше або менше зазначеної величини. Інше стандартне використання COUNTIF – це для підрахунку клітинок з певним словом або з певною літерою (літерами). Ми підготували для вас приклади роботи з цією функцією та її опис.
- Функція COUNTIF та її синтаксис
- Рекомендації для правильної роботи функції COUNTIF
- Підрахунок числових значень з одним критерієм
- Підрахунки тексту з одним критерієм з COUNTIF
- COUNTIF з кількома умовами та критерієм у вигляді посилання
- Використання COUNTIF для підрахунку дат
- Функція COUNTIFS
- Використання COUNTIF у Google Sheets
Функція COUNTIF та її синтаксис
Функція Excel COUNTIF має наступний синтаксис:
COUNTIF (діапазон; критерій)
З синтаксису видно, що функція містить 2 (два) аргументи і вони обидва є обов’язковими:
Діапазон – вказується діапазон, у якому потрібно підрахувати клітинки, що містять числа, текст чи дати.
Критерій – умова у вигляді числа, виразу, посилання на клітинку чи тексту, що визначає, які клітинки потрібно підрахувати. Наприклад, критерій може бути представлений у такому вигляді: 64, “72”, “>40”, “персик” або F16.
Як бачимо з опису, синтаксис дуже простий. Однак він допускає багато різних варіацій умов, у тому числі символи підстановки, значення інших клітинок і навіть інші функції Excel. Ця різноманітність робить функцію COUNTIF справді потужною, а разом з функцією SUMIF дозволяє вирішувати багато завдань.
Рекомендації для правильної роботи функції COUNTIF
Перед початком роботи з функцією COUNTIF слід ознайомитися з деякими порадами щодо її використання:
- якщо функція COUNTIF посилається на діапазон в іншій книзі, то ця книга має бути відкритою;
- функція не враховує регістр символів;
- при створенні умови можна використовувати знак питання (?) – замінює будь-який 1 (один) символ або знак зірочка (*) – замінює будь-яку кількість символів. Щоб формула шукала ці знаки, ставимо перед ними знак тильди (~).
- щоб не було помилок у клітинках з текстом, потрібно уникнути наявності недрукованих знаків та пробілів на початку та в кінці.
Підрахунок числових значень з одним критерієм
Розглянемо використання функції на конкретних прикладах. Припустимо, є таблиця з даними продажу деяких товарів.
Наприклад, нам потрібно знайти замовників, які продали товари на суму понад 2000. Шукати такі товари очима, а потім рахувати вручну не дуже зручно, тому тут буде доречна функція COUNTIF.
Виділяємо клітинку, де буде розрахунок, наприклад, H2. Далі викликаємо Майстер функцій. Це значок fx у рядку формул та шукаємо у списку функцій COUNTIF, натискаємо на неї.
Відкривається вікно, в якому потрібно вказати 2 (два) аргументи:
- У полі «Діапазон» вказуються клітинки таблиці Excel, де є всі вихідні значення для пошуку. У нашому випадку це D2:D16, вказується зазвичай за допомогою мишки.
- У полі «Критерій» вказується та умова, за якою формула проводитиме відбір, у нашому випадку «>2000». Якщо не поставити лапки, вони потім самі з’являться.
Таким чином, функція COUNTIF Excel бере критерій і починає відбирати всі значення із зазначеного діапазону, що задовольняють заданому критерію. Після цього усі відібрані значення складаються. Так працює алгоритм функції.
Заповнивши всі поля, натиснути Enter чи «ОК» у вікні майстра. На місці функції (клітинка H2), повинно з’явитися розраховане значення. У розглянутому прикладі вийшло 3 шт. (До речі, це число можна побачити видно в нижньому лівому кутку вікна Майстра ще до натискання «ОК»).
Можна використовувати не лише знаки більше чи менше. Також застосовуються знаки (>=) – більше або дорівнює, та (<>) – не дорівнює.
Підрахунки тексту з одним критерієм з COUNTIF
Розглянемо приклад, коли критерієм (умовою) є текст. Припустимо, потрібно знайти кількість замовлень від фірми Ашан.
Для використання функції COUNTIF потрібно:
- Встановлюємо курсор у клітинку для підрахунку, наприклад, H4 і викликаємо Майстер функцій. У вікні “Вставлення функцій” виділяємо нашу функцію та натискаємо ОК.
- У рядку «Діапазон» вказуємо діапазон, де міститься критерій, у нашому випадку це стовпець із замовниками (A2:A16). У рядку «Критерій» у лапках пишемо «Ашан».
- Натискаємо кнопку ОК. В результаті отримаємо 3 замовлення від Ашан.
Критерій не чутливий до регістру літер, тому можна вводити як великі, так і малі літери.
COUNTIF з кількома умовами та критерієм у вигляді посилання
Розглянемо приклад, коли потрібно дізнатися кількість замовлень всіх представників, і щоб у якості критерію був не текст, а посилання на клітинку. Для цього потрібно окремо створити таблицю зі списком замовників. Назви замовників мають повністю збігатися з назвами у вихідній таблиці.
Після цього прописується формула для першої групи (у нашому випадку клітинка H7) і протягується на всі інші. Але тут важливо звернути увагу на відносність посилань.
Діапазон має бути абсолютним посиланням, щоб при копіюванні формули його адреса не «поїхала вниз», тобто вона не повинна змінюватися. А ось сам критерій має бути відносним посиланням (змінюваним). У критерію будуть назви замовників у окремій таблиці.
Щоб закріпити діапазон із виділеними клітинками, на клавіатурі потрібно натиснути клавішу F4.
Після заповнення всіх полів натискаємо ОК. Далі на клітинці H7 беремо за нижній правий куточок, затискаємо ліву кнопку миші і тягнемо формулу вниз. Готовий результат з формулою будуть виглядати, як показано на знімку з крана нижче.
Використання COUNTIF для підрахунку дат
Оскільки будь-якій даті в MS EXCEL відповідає певне числове значення, то налаштування функції COUNTIF для дат не відрізняється від роботи з числами. Наприклад, у раніше розглянутій таблиці потрібно відобразити кількість замовлень, отриманих починаючи з 01.03.2021.
Для цього викликаємо Майстер функцій (значок fx) запускаємо потрібну нам функцію та заповнюємо поля.
Крім цих стандартних способів, можна використати функцію COUNTIF у поєднанні з функціями дати та часу, наприклад, TODAY (повертає системну дату).
| Критерій | Формула |
| Відповідає поточній даті | = COUNTIF(E2:E22; TODAY()) |
| До поточної дати, тобто менше, ніж сьогодні | = COUNTIF(E2:E22;”<“&TODAY()) |
| Після поточної дати, тобто більше, ніж сьогодні | = COUNTIF(E2:E22;”>”&TODAY()) |
| Дати, які мають наступити за тиждень | = COUNTIF(E2:E22,”=”&TODAY()+7) |
Функція COUNTIFS
Починаючи з версії Excel 2007, з’явився множинний аналог функції COUNTIF, вона називається COUNTIFS. У ній можна вказувати кілька діапазонів критеріїв, і до кожного з них своя умова.
Наприклад, у нашій таблиці потрібно знайти кількість замовлень від Ашана у місті Київ. Для цього викликаємо Майстер функцій (значок fx) знаходимо функцію COUNTIFS та заповнюємо поля.
Після заповнення натискаємо ОК. В результаті Excel порахує дані та виведе число 2 шт. Заповнені дані та результат будуть виглядати як знімку з екрана нижче.
Використання COUNTIF у Google Sheets
Сьогодні багато користувачів використовують COUNTIF Excel у таблицях Google. Вона має ті ж аргументи та працює аналогічно, але функцію доведеться прописати вручну. Спочатку вказується діапазон пошуку, потім критерій.
Розглянемо приклад пошуку кількості замовників, які продали товарів на суму < 2000. COUNTIF у Google Sheets працює наступним чином:
- Встановлюємо курсор у клітинку, де буде підраховано суму, наприклад H4;
- Натискаємо знак “=” (дорівнює), повністю пишемо назву функції COUNTIF і відкриваємо дужки;
- Мишкою вказуємо діапазон для пошуку (D4:D18);
- Ставимо крапку з комою, вказуємо нашу умову в лапках – «<2000»;
- Закриваємо дужку та натискаємо клавішу Enter на клавіатурі.
В результаті отримаємо число 12 шт. Як бачимо, принцип роботи з COUNTIF in Google Sheets з формулами такий самий, як і в звичайному Excel.
Ми розглянули різні варіанти використання функції COUNTIF (рахувати, якщо виконана умова). Звісно різних випадків може бути багато. Тому якщо у вас залишилися питання або є досвід використання функції, то пропонуємо поділитись цим у коментарях.
