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

Ви можете використати наведену нижче формулу, щоб поєднати функції SUBTOTAL і SUMIF в Excel:

 =SUMPRODUCT(SUBTOTAL(109,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),,1)),--( B2:B11 ="Guard"))

Ця конкретна формула дозволяє підсумовувати значення в діапазоні C2:C11, де відповідне значення в діапазоні B2:B11 дорівнює «Зберегти», навіть після того, як цей діапазон клітинок було відфільтровано певним чином.

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

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

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

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

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

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

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

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

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

 =SUMPRODUCT(SUBTOTAL(109,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),,1)),--( B2:B11 ="Guard"))

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

Ця формула повертає правильну суму 85 .

Ми можемо підтвердити, що це правильно, вручну обчисливши суму значень балів для рядків, де стовпець Position дорівнює «Guard»:

Сума очок гарда: 12 + 28 + 30 + 15 = 85 .

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

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

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