Como usar subtotal com sumproduct no excel

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

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

Esta fórmula específica permite somar o produto dos valores do intervalo C2:C11 e do intervalo D2:D11 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 SUMPRODUCT no Excel

Suponha que temos o seguinte conjunto de dados contendo informações sobre vendas de vários produtos em dois supermercados diferentes:

A seguir, vamos filtrar os dados para mostrar apenas as linhas cujo valor na coluna Loja é B.

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

Em seguida, clique na seta suspensa ao lado de Loja e certifique-se de que apenas a caixa ao lado de B esteja marcada e clique em OK :

Os dados serão filtrados automaticamente para mostrar apenas as linhas onde a coluna Store for igual a B :

Se tentarmos usar a função SUMRPODUCT() para somar o produto dos valores das colunas Vendas e Preço, ela retornará na verdade a soma do produto dessas duas colunas no conjunto de dados original:

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

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

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

Esta fórmula retorna a soma correta de 77,5 .

Podemos confirmar que isso está correto calculando manualmente a soma do produto dos valores entre as colunas Vendas e Preço:

Soma do produto dos valores entre vendas e preço: (3*2) + (12*2,5) + (5*3,5) + (8*3) = 77,5 .

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 somar linhas filtradas no Excel