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

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

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

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

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

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

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

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

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

Потім клацніть стрілку розкривного меню поруч із Store та переконайтеся, що позначено лише поле біля B , а потім натисніть OK :

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

Якщо ми спробуємо використати функцію SUMRPODUCT() , щоб підсумувати добуток значень стовпців Sales і Price, вона фактично поверне суму добутку цих двох стовпців у вихідному наборі даних:

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

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

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

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

Ми можемо підтвердити, що це правильно, вручну обчисливши суму добутку значень між стовпцями Sales і Price:

Сума добутку значень між продажем і ціною: (3*2) + (12*2,5) + (5*3,5) + (8*3) = 77,5 .

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

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

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