Como usar subtotal com sumif no excel

Você pode usar a seguinte fórmula para combinar as funções SUBTOTAL e SUMIF no Excel:

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

Esta fórmula específica permite somar os valores no intervalo C2:C11 onde o valor correspondente no intervalo B2:B11 é igual a “Manter” mesmo depois que esse intervalo de células tiver sido filtrado de alguma forma.

O exemplo a seguir mostra como usar esta fórmula na prática.

Exemplo: Como usar SUBTOTAL com SUMIF no Excel

Suponha que temos o seguinte conjunto de dados contendo informações sobre vários jogadores de basquete:

A seguir, vamos filtrar os dados para mostrar apenas as linhas onde os jogadores da Conferência Oeste estão localizados.

Para fazer isso, destaque o intervalo de células A1:C11 . Em seguida, clique na guia Dados na faixa superior e clique no botão Filtro .

Em seguida, clique na seta suspensa ao lado de Conferência e certifique-se de que apenas a caixa ao lado de Oeste esteja marcada e clique em OK :

Os dados serão filtrados automaticamente para mostrar apenas as linhas onde a coluna Conferência for igual a West :

Se tentarmos usar a função SUMIF() para somar os valores na coluna Pontos onde o valor da coluna Posição é igual a “Guarda”, ela na verdade retornará a soma dos pontos para linhas iguais a “Guarda” na função SUMIF() função. conjunto de dados original:

Em vez disso, precisamos usar a seguinte fórmula:

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

A captura de tela a seguir mostra como usar esta fórmula na prática:

Esta fórmula retorna a soma correta de 85 .

Podemos confirmar que isso está correto calculando manualmente a soma dos valores dos pontos para as linhas onde a coluna Posição é igual a “Guarda”:

Soma dos pontos de guarda: 12 + 28 + 30 + 15 = 85 .

Recursos adicionais

Os tutoriais a seguir explicam como realizar outras operações comuns no Excel:

Como excluir linhas filtradas no Excel
Como contar linhas filtradas no Excel
Como adicionar linhas filtradas no Excel