COUNTIFS (функція COUNTIFS)

Функція COUNTIFS застосовує умови до клітинок у кількох діапазонах і підраховує кількість разів, коли виконуються всі умови.

Це відео входить до навчального курсу Розширені функції IF.

Синтаксис

Синтаксис функції COUNTIFS має такі аргументи:

• діапазон_критерію1 – обов’язковий аргумент. Перший діапазон, у якому перевіряється виконання відповідних умов.
• критерій1 – обов’язковий аргумент. Умова у формі числа, виразу, посилання на клітинку або тексту, що визначає, які клітинки потрібно підрахувати. Наприклад, умова може мати такий вигляд: 32, “>32”, B4, “яблука” або “32”.
• діапазон_критерію2;критерій2;. – необов’язкові аргументи. Додаткові діапазони та пов’язані з ними умови. Можна вказати до 127 пар діапазонів і умов.

Увага!: Кількість рядків і стовпців в усіх додаткових діапазонах має бути однакова з аргументом діапазон_критерію1. Діапазони не обов’язково мають бути суміжні.

Примітки

• Кожна умова діапазону застосовується до однієї клітинки по черзі. Якщо всі перші клітинки відповідають пов’язаним із ними умовам, значення лічильника збільшується на 1. Якщо всі другі клітинки відповідають пов’язаним із ними умовам, значення лічильника знову збільшується на 1 і так далі, доки не буде перевірено всі клітинки.
• Якщо умова – посилання на пусту клітинку, функція COUNTIFS інтерпретує пусту клітинку як значення 0.
• Для умов можна використовувати символи узагальнення: знак питання (?) і зірочку (*). Знак питання відповідає будь-якому символу, а зірочка – будь-якій послідовності символів. Якщо потрібно знайти власне знак питання або зірочку, перед відповідним символом введіть тильду (~).

Приклад 1

Скопіюйте дані прикладу з наведених нижче таблиць і вставте їх у клітинку A1 нового аркуша Excel. Щоб відобразити результат обчислення формул, виберіть їх, натисніть клавішу F2, а потім – клавішу Enter. За потреби можна змінити ширину стовпців, щоб відобразити всі дані.

Чи перевищено квоту першого кварталу

Чи перевищено квоту другого кварталу

Чи перевищено квоту третього кварталу

COUNTIF (функція COUNTIF)

За допомогою функції COUNTIF, яка належить до статистичних функцій, можна порахувати кількість клітинок, які відповідають певній умові (наприклад, скільки разів якесь місто з’являється в списку клієнтів).

У найпростішому випадку COUNTIF працює за таким принципом:

діапазон (обов’язковий аргумент)

Група клітинок, які потрібно підрахувати. Аргумент діапазон може містити числа, масиви, іменовані діапазони або посилання з числами. Пусті та текстові значення ігноруються.

умова (обов’язковий аргумент)

Число, вираз, посилання на клітинку або текстовий рядок, що визначає, які клітинки потрібно підрахувати.

Наприклад, можна використати число, як-от 32, порівняння, як-от “>32”, клітинку, як-от B4, або слово, як-от “яблука”.

У функції COUNTIF використовується лише одна умова. Використовуйте функцію COUNTIFS, якщо потрібно застосувати кілька умов.

Приклади

Щоб скористатися цими прикладами в програмі Excel, скопіюйте дані в таблиці нижче та вставте їх у клітинку A1 нового аркуша.

Рахує кількість клітинок із текстом “яблука” в клітинках від A2 до A5. У результаті отримаємо 2.

Рахує кількість клітинок із текстом “кавуни” (значення в клітинці A4) у клітинках від A2 до A5. У результаті отримаємо 1.

Рахує кількість клітинок із текстом “яблука” (значення в клітинці A2) і “апельсини” (значення в клітинці A3) у клітинках від A2 до A5. У результаті отримаємо 3. Функцію COUNTIF використано у формулі двічі, щоб визначити кілька умов – по одній умові на вираз. Також можна скористатися функцією COUNTIFS.

Рахує кількість клітинок зі значенням, більшим за 55, у клітинках B2:B5. У результаті отримаємо 2.

Рахує кількість клітинок зі значенням, яке не дорівнює 75, у клітинках B2:B5. Амперсанд (&) об’єднує оператор порівняння “не дорівнює” (<>) і значення в клітинці B4 для прочитання функції =COUNTIF(B2:B5;”<>75″). У результаті отримаємо 3.

Рахує кількість клітинок зі значеннями, більшими (>) або рівними (=) 32 та меншими (<) або рівними (=) 85, у клітинках B2:B5. У результаті отримаємо 1.

Рахує кількість клітинок, що містять будь-який текст, у клітинках від A2 до A5. Зірочка (*) використовується як символ узагальнення для будь-яких символів. У результаті отримаємо 4.

Рахує кількість клітинок, які мають точно 6 символів і закінчуються буквами “ни”, у клітинках від A2 до A5. Знак питання (?) використовується як символ узагальнення для заміни будь-яких окремих символів. У результаті отримаємо 1.

Поширені проблеми

Для довгих рядків повернуто помилкове значення.

Функція COUNTIF повертає неправильні результати, якщо зіставляються рядки довжиною більше 255 символів

Щоб зіставити рядки, довші за 255 символів, використовуйте функцію CONCATENATE або оператор об’єднання &. Наприклад, =COUNTIF(A2:A5;”довгий рядок”&”інший довгий рядок”).

Не повернуто жодного значення, коли очікувалося значення.

Переконайтеся, що аргумент умова взято в лапки.

Формула COUNTIF отримує #VALUE! під час звернення до іншого аркуша.

Це стається, коли формула, яка містить функцію, посилається на клітинки або діапазон клітинок у закритій книзі та обчислює кількість цих клітинок. Щоб ця функція працювала, потрібно відкрити іншу книгу.

Практичні поради

Пам’ятайте, що функція COUNTIF ігнорує верхній і нижній регістри в текстових рядках.

Умови нечутливі до регістра. Іншими словами, рядкам “яблука” та “ЯБЛУКА” відповідатимуть одні й ті ж клітинки.

Використовуйте символи узагальнення.

Символи узагальнення – знак питання (?) і зірочку (*) – можна використовувати для умов. Знак питання відповідає будь-якому одному символу. Зірочка відповідає будь-якій послідовності символів. Якщо потрібно знайти власне знак питання або зірочку, перед відповідним символом введіть тильду (~).

Наприклад, =COUNTIF(A2:A5;”яблуко?”) підрахує всі екземпляри “яблука” з останньою буквою, яка може відрізнятися.

Переконайтеся, що ваші дані не містять помилкові символи.

Під час підрахунку текстових значень переконайтеся, що дані не містять пробілів на початку чи в кінці, а також неузгоджених прямих чи фігурних лапок або недрукованих символів. У таких випадках функція COUNTIF може повернути неочікуване значення.

Спробуйте використати функцію CLEAN або TRIM.

Для зручності використовуйте іменовані діапазони

Функція COUNTIF підтримує іменовані діапазони у формулі (наприклад, =COUNTIF(фрукти;”>=32″)-COUNTIF(фрукти;”>85″). Іменований діапазон може бути розташовано на поточному аркуші, на іншому аркуші тієї самої книги або в іншій книзі. Щоб створити посилання на іншу книгу, її також потрібно відкрити.

Примітка.: Під час підрахунку функція COUNTIF не враховує колір фону або шрифту клітинок. Проте Excel підтримує користувацькі функції (UDF) на основі операцій із клітинками Microsoft Visual Basic for Applications (VBA), які враховують колір фону або шрифту. Нижче наведено приклад того, як можна підрахувати кількість клітинок із певним кольором за допомогою VBA.

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.