Як використовувати subtotal з countif в excel

Щоб поєднати функції SUBTOTAL і COUNTIF в Excel, можна використати таку формулу:

 =SUMPRODUCT(SUBTOTAL(3,OFFSET( B2:B11 ,ROW( B2:B11 )-ROW( B2 ),0,1)),--( B2:B11 ="Guard"))

Ця конкретна формула дозволяє підрахувати кількість комірок у діапазоні B2:B11, що дорівнює “Guard”, навіть після того, як цей діапазон комірок було відфільтровано певним чином.

У наступному прикладі показано, як використовувати цю формулу на практиці.

Приклад: як використовувати SUBTOTAL з COUNTIF в Excel

Припустимо, що ми маємо наступний набір даних, що містить інформацію про різних баскетболістів:

Далі давайте відфільтруємо дані, щоб відобразити лише ті рядки, де знаходяться гравці Західної конференції.

Для цього виділіть діапазон клітинок A1:C11 . Потім натисніть вкладку «Дані» на верхній стрічці та натисніть кнопку «Фільтр» .

Потім клацніть стрілку спадного меню поруч із Conference (Конференція) і переконайтеся, що позначено лише прапорець біля West (Захід) , а потім натисніть OK :

Дані буде автоматично відфільтровано, щоб відобразити лише ті рядки, у яких стовпець Conference дорівнює West :

Якщо ми спробуємо використати функцію COUNTIF() , щоб підрахувати кількість рядків, у яких Position дорівнює “Guard”, вона фактично поверне кількість рядків, що дорівнює “Guard” у вихідному наборі даних:

Замість цього нам потрібно використовувати таку формулу:

 =SUMPRODUCT(SUBTOTAL(3,OFFSET( B2:B11 ,ROW( B2:B11 )-ROW( B2 ),0,1)),--( B2:B11 ="Guard"))

На наступному знімку екрана показано, як використовувати цю формулу на практиці:

Ця функція підраховує лише кількість видимих ліній, де Позиція дорівнює «Охоронця», тобто 4 лінії.

Додаткові ресурси

У наступних посібниках пояснюється, як виконувати інші типові операції в Excel:

Як видалити відфільтровані рядки в Excel
Як підрахувати відфільтровані рядки в Excel
Як додати відфільтровані рядки в Excel