Resumo
A função SUMPRODUCT multiplica intervalos ou matrizes e retorna a soma dos produtos. Isso parece enfadonho, mas SUMPRODUCT é uma função incrivelmente versátil que pode ser usada para contar e somar como COUNTIFS ou SUMIFS, mas com mais flexibilidade. Outras funções podem ser facilmente usadas dentro do SUMPRODUCT para estender a funcionalidade ainda mais.
Objetivo
Multiplique e some as matrizesValor de retorno
O resultado de matrizes múltiplas e somadasSintaxe
= SUMPRODUCT (matriz1, (matriz2), …)Argumentos
- matriz1 - a primeira matriz ou intervalo a multiplicar e, em seguida, adicionar.
- array2 - (opcional) O segundo array ou intervalo a multiplicar e depois adicionar.
Versão
Excel 2003Notas de uso
A função SUMPRODUCT funciona com arrays, mas não requer a sintaxe normal de array (Ctrl + Shift + Enter) para entrar. O objetivo da função SUMPRODUCT é multiplicar e, em seguida, somar arrays. Se apenas uma matriz for fornecida, SUMPRODUCT simplesmente somará os itens na matriz. Até 30 matrizes podem ser fornecidas.
Quando você encontra SUMPRODUCT pela primeira vez, pode parecer chato, complexo e até mesmo sem sentido. Mas SUMPRODUCT é uma função incrivelmente versátil com muitos usos. Como ele lidará com matrizes de maneira elegante e sem reclamação, você pode usá-lo para processar intervalos de células de maneiras inteligentes e elegantes (consulte os links para exemplos de fórmulas nesta página).
Para ilustrar como o SUMPRODUCT funciona, aqui estão alguns exemplos comuns.
SUMPRODUCT para somas e contagens condicionais
Suponha que você tenha alguns dados do pedido em A2: B6, com Estado na coluna A, Vendas na coluna B:
| UMA | B | |
| 1 | Estado | Vendas |
| 2 | UT | 75 |
| 3 | CO | 100 |
| 4 | TX | 125 |
| 5 | CO | 125 |
| 6 | TX | 150 |
Usando SUMPRODUCT, você pode contar as vendas totais do Texas ("TX") com esta fórmula:
=SUMPRODUCT(--(A2:A6="TX"))
E você pode somar as vendas totais do Texas ("TX") com esta fórmula:
=SUMPRODUCT(--(A2:A6="TX"),B2:B6)
Nota: Não se confunda com o duplo negativo. Este é um truque comum usado em fórmulas mais avançadas do Excel para converter os valores VERDADEIRO e FALSO em 1s e 0s. Veja mais abaixo…
Para o exemplo de soma acima, aqui está uma representação virtual das duas matrizes conforme primeiro processado pelo SUMPRODUCT:
| array1 | array2 |
| FALSO | 75 |
| FALSO | 100 |
| VERDADE | 125 |
| FALSO | 125 |
| VERDADE | 150 |
Cada array possui 5 itens. A primeira matriz contém os valores TRUE / FALSE que resultam da expressão A2: A6 = "TX" e a segunda matriz é o conteúdo de B2: B6. Cada item na primeira matriz será multiplicado pelo item correspondente na segunda matriz. No entanto, no estado atual, o resultado de SUMPRODUCT será zero porque os valores TRUE e FALSE serão tratados como zero. Precisamos que os itens em array1 sejam numéricos - eles precisam ser "coagidos" em uns e zeros. É aqui que entra o duplo negativo.
Ao usar o duplo negativo - (duplo unário, para seus tipos técnicos), somos capazes de forçar o VERDADEIRO / FALSO nos valores numéricos um e zero, conforme mostrado na representação virtual abaixo. A última coluna "Produto" representa o resultado da multiplicação das duas matrizes. O resultado somado, 275, é o valor que SUMPRODUCT retorna.
| array1 | array2 | produtos | ||
| 0 | * | 75 | = | 0 |
| 0 | * | 100 | = | 0 |
| 1 | * | 125 | = | 125 |
| 0 | * | 125 | = | 0 |
| 1 | * | 150 | = | 150 |
| Soma | 275 | |||
Usando a sintaxe de chaves para matrizes, o exemplo fica assim após a coerção:
=SUMPRODUCT((0,0,1,0,1),(75,100,125,125,150))
e assim após a multiplicação:
=SUMPRODUCT((0,0,125,0,150))
Este exemplo expande as idéias acima com mais detalhes.
SUMPRODUCT com outras funções
SUMPRODUCT pode usar outras funções diretamente. Você pode ver SUMPRODUCT usado com a função LEN para contar o total de caracteres em um intervalo, ou com funções como ISBLANK, ISTEXT, etc. Estas não são funções de array normalmente, mas quando recebem um intervalo, eles criam um "array de resultados". Como o SUMPRODUCT foi criado para funcionar com arrays, ele é capaz de realizar cálculos diretamente nos arrays. Essa pode ser uma boa maneira de economizar espaço em uma planilha, eliminando a necessidade de uma coluna "auxiliar".
Por exemplo, suponha que você tenha 10 valores de texto diferentes em A1: A10 e deseja contar o total de caracteres para todos os 10 valores. Você pode adicionar uma coluna auxiliar na coluna B que usa esta fórmula: LEN (A1) para calcular os caracteres em cada célula. Então você pode usar SUM para somar todos os 10 números. No entanto, usando SUMPRODUCT, você pode escrever uma fórmula como esta:
=SUMPRODUCT(LEN(A1:A10))
Quando usado com um intervalo como A1: A10, LEN retornará uma matriz de 10 valores. Então SUMPRODUCT irá simplesmente somar todos os valores e retornar o resultado, sem a necessidade de coluna auxiliar.
Veja os exemplos abaixo de muitas outras maneiras de usar o SUMPRODUCT.
Notas:
- SUMPRODUCT trata itens não numéricos em matrizes como zeros.
- Os argumentos da matriz devem ter o mesmo tamanho. Caso contrário, SUMPRODUCT gerará um #VALUE! valor de erro.
- Os testes lógicos dentro de matrizes criarão valores TRUE e FALSE. Na maioria dos casos, você desejará forçá-los a 1 e 0.
- SUMPRODUCT muitas vezes pode usar o resultado de outras funções diretamente (consulte os exemplos de fórmulas abaixo)
