Як користуватися функцією Excel COUNTIFS -

Зміст

Related videos

Резюме

Функція Excel COUNTIFS повертає кількість клітинок, які відповідають одному або декільком критеріям. COUNTIFS можна використовувати з критеріями на основі дат, цифр, тексту та інших умов. COUNTIFS підтримує логічні оператори (>, <,, =) та символи підстановки (* ,?) для часткового збігу.

Призначення

Підрахувати клітинки, які відповідають кільком критеріям

Повернене значення

Кількість виконання критеріїв

Синтаксис

= COUNTIFS (діапазон1, критерій1, (діапазон2), (критерій2),…)

Аргументи

range1 - Перший діапазон для evaulate.
критерії1 - критерії, які слід використовувати для діапазону1.
range2 - (необов’язково) Другий діапазон для оцінки.
критерії2 - (необов’язково) Критерії, які слід використовувати для діапазону2.

Версія

Excel 2007

Примітки щодо використання

Функція COUNTIFS в Excel підраховує кількість комірок у діапазоні, що відповідає одному з поданих критеріїв. На відміну від попередньої функції COUNTIF, COUNTIFS може застосовувати більше ніж одну умову одночасно. Умови постачаються з парами діапазону / критерію, і потрібна лише перша пара. Для кожної додаткової умови ви повинні вказати іншу пару діапазону / критеріїв. Допускається до 127 пар діапазонів / критеріїв.

Критерії можуть включати логічні оператори (>, <,, =) та символи підстановки (* ,?) для часткового збігу. Критерії також можуть базуватися на значенні з іншої комірки, як пояснено нижче.

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

Основний приклад

У наведеному прикладі COUNTIFS можна використовувати для підрахунку записів, використовуючи 2 критерії, як показано нижче:

=COUNTIFS(C5:C14,"red",D5:D14,"tx") // red and TX =COUNTIFS(C5:C14,"red",F5:F14,">20") // red and>20

Зверніть увагу, що функція COUNTIFS не враховує регістр.

Подвійні лапки ("") у критеріях

Загалом, текстові значення потрібно укладати у подвійні лапки, а цифри - ні. Однак, коли логічний оператор включається в число, номер і оператор повинні бути в лапках, як показано нижче:

=COUNTIFS(A1:A10,100) // count equal to 100 =COUNTIFS(A1:A10,">50") // count greater than 50 =COUNTIFS(A1:A10,"jim") // count equal to "jim"

Примітка: показ однієї умови лише для простоти. Додаткові умови повинні відповідати тим самим правилам.

Значення з іншої комірки

При використанні значення з іншої комірки в умові, посилання на клітинку має бути об'єднане в оператор, коли використовується. У наведеному нижче прикладі COUNTIFS підраховуватиме значення в A1: A10, які менші за значення в комірці B1. Зверніть увагу, що оператор менше ніж (який є текстом) укладено у лапки, але посилання на клітинку не є:

=COUNTIFS(A1:A10,"<"&B1) // count cells less than B1

Примітка: COUNTIFS - це одна з кількох функцій, яка розділяє умови на дві частини: діапазон + критерії. Це спричиняє деякі невідповідності щодо інших формул та функцій.

Не дорівнює

Для побудови критеріїв "не дорівнює" використовуйте оператор "", оточений подвійними лапками (""). Наприклад, формула, наведена нижче, буде враховувати клітини, не рівні "червоному" в діапазоні A1: A10:

=COUNTIFS(A1:A10,"red") // not "red"

Пусті клітини

COUNTIFS can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10:

=COUNTIFS(A1:A10,"") // not blank =COUNTIFS(A1:A10,"") // blank

Dates

The easiest way to use COUNTIFS with dates is to refer to a valid date in another cell with a cell reference. For example, to count cells in A1:A10 that contain a date greater than a date in B1, you can use a formula like this:

=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1

Notice we concatenate the ">" operator to the date in B1, but and are no quotes around the cell reference.

The safest way hardcode a date into COUNTIFS is with the DATE function. This guarantees Excel will understand the date. To count cells in A1:A10 that contain a date less than September 1, 2020, you can use:

=COUNTIFS(A1:A10,"<"&DATE(2020,9,1)) // dates less than 1-Sep-2020

Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character, and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in a A1:A5 that contain the text "apple" anywhere, you can use a formula like this:

=COUNTIFS(A1:A5,"*apple*") // count cells that contain "apple"

The tilde (~) is an escape character to allow you to find literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).

Notes

Multiple conditions are applied with AND logic, i.e. condition 1 AND condition 2, etc.
Each additional range must have the same number of rows and columns as range1, but ranges do not need to be adjacent. If you supply ranges that don't match, you'll get a #VALUE error.
Non-numeric criteria needs to be enclosed in double quotes but numeric criteria does not. For example: 100, "100", ">32", "jim", or A1 (where A1 contains a number).
The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters.
To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).