Вы можете использовать следующую формулу для объединения функций ПРОМЕЖУТОЧНЫЙ ИТОГ и СУММЕСЛИ в Excel:
=SUMPRODUCT(SUBTOTAL(109,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),,1)),--( B2:B11 ="Guard"))
Эта конкретная формула позволяет суммировать значения в диапазоне C2:C11, где соответствующее значение в диапазоне B2:B11 равно «Guard» даже после того, как этот диапазон ячеек был каким-то образом отфильтрован.
В следующем примере показано, как использовать эту формулу на практике.
Пример: как использовать ПРОМЕЖУТОЧНЫЕ ИТОГО с СУММЕСЛИ в Excel
Предположим, у нас есть следующий набор данных, содержащий информацию о различных баскетболистах:
Далее давайте отфильтруем данные, чтобы отображались только те строки, в которых игроки находятся на западной конференции.
Для этого выделите диапазон ячеек A1:C11.Затем щелкните вкладку « Данные » на верхней ленте и нажмите кнопку « Фильтр ».
Затем щелкните стрелку раскрывающегося списка рядом с Конференция и убедитесь, что установлен только флажок рядом с Западом , затем нажмите OK :
Данные будут автоматически отфильтрованы, чтобы отображались только строки, в которых столбец Conference равен 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