Как использовать промежуточный итог с суммесли в excel

Вы можете использовать следующую формулу для объединения функций ПРОМЕЖУТОЧНЫЙ ИТОГ и СУММЕСЛИ в Excel:

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

Эта конкретная формула позволяет суммировать значения в диапазоне C2:C11, где соответствующее значение в диапазоне B2:B11 равно «Сохранить» даже после того, как этот диапазон ячеек был каким-либо образом отфильтрован.

Следующий пример показывает, как использовать эту формулу на практике.

Пример: как использовать ПРОМЕЖУТОЧНЫЙ ИТОГО с СУММЕСЛИ в Excel

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

Далее давайте отфильтруем данные, чтобы отображались только те строки, в которых находятся игроки Западной конференции.

Для этого выделите диапазон ячеек A1:C11 . Затем нажмите вкладку «Данные» на верхней ленте и нажмите кнопку «Фильтр» .

Затем щелкните стрелку раскрывающегося списка рядом с пунктом «Конференция » и убедитесь, что установлен только флажок рядом с пунктом « Запад» , затем нажмите «ОК» :

Данные будут автоматически отфильтрованы, чтобы отображать только строки, в которых столбец «Конференция» равен West :

Если мы попытаемся использовать функцию СУММЕСЛИ() для суммирования значений в столбце «Точки», где значение столбца «Позиция» равно «Охрана», она фактически вернет сумму баллов для строк, равных «Охрана» в СУММЕСЛИ(). функция. исходный набор данных:

Вместо этого нам нужно использовать следующую формулу:

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

На следующем снимке экрана показано, как использовать эту формулу на практике:

Эта формула возвращает правильную сумму 85 .

Мы можем подтвердить, что это правильно, вручную рассчитав сумму значений баллов для строк, где столбец «Позиция» равен «Охранник»:

Сумма очков охраны: 12 + 28 + 30 + 15 = 85 .

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:

Как удалить отфильтрованные строки в Excel
Как посчитать отфильтрованные строки в Excel
Как добавить отфильтрованные строки в Excel