Neste artigo, aprenderemos a SOMAR SE usando valores de pesquisa no Excel.
Em palavras simples, ao trabalhar com diferentes tabelas de dados, às vezes precisamos calcular valores na primeira tabela com base em valores em outra tabela. A condição é obter a soma dos valores na primeira tabela usando os valores da segunda tabela
Para este artigo, precisaremos usar as seguintes funções:
- Função SUMPRODUCT
- Função SUMIF
A função SUMPRODUCT é uma função matemática do Excel. Ele opera em vários intervalos. Ele multiplica os arrays correspondentes e, em seguida, os adiciona.
Total = (A1 * B1 * C1 * … + A2 * B2 * C2 * … + …)
Sintaxe:
= SUMPRODUCT (matriz1, [matriz2],…)
array: é o intervalo ou uma lista de valores.
A função SOMASE no Excel retorna a SOMA dos números que atenderam aos critérios especificados. O intervalo SUM e o intervalo de critérios podem ser iguais ou diferentes.
Fórmula SUMIF genérica do Excel:
= SUMIF (intervalo_critérios, critérios, intervalo_soma)
intervalo_de_critérios: intervalo onde a condição é aplicada.
critérios: condição a ser aplicada em intervalo_de_criterios.
intervalo_soma: intervalo de soma com base em critérios.
Agora faremos uma fórmula com essas funções. Aqui, forneceremos os dados e os resultados necessários da soma em que o valor corresponda ao valor na tabela de pesquisa.
Fórmula genérica:
= SUMPRODUCT (SUMIF (resultado, registros, sum_nums))
resultado : valores a serem correspondidos na tabela de pesquisa
registro : valores na primeira tabela
sum_nums : valores a serem somados de acordo com os valores correspondentes.
Vamos testar esta fórmula executando-a em um exemplo
Aqui, estamos calculando o total de pontos de uma equipe de futebol usando a tabela de pontos ou tabela de consulta.
Aqui o resultado (B8: B10) dado como intervalo nomeado e pontos (C8: C10) dado como intervalo nomeado.
Agora usaremos a fórmula abaixo para obter a SOMA dos pontos para o Mês1 na célula F3
Use a fórmula:
= SUMPRODUCT (SUMIF (resultado, B3: E3, pontos))
Explicação:
- A função SUMIF corresponde ao resultado com os valores em B3: E3.
- Em seguida, os pontos correspondentes ao resultado são devolvidos à função SUMPRODUCT.
- A função SUMPRODUCT retorna a soma dos valores fornecidos como array para a função.
Como você pode ver aqui, o total de pontos marcados pela equipe no primeiro mês é de 7.
3 (W) + 0 (L) + 1 (D) + 3 (W) = 7 pontos
Copie a fórmula em outras células, selecione as células tomando a primeira célula onde a fórmula já foi aplicada, use a tecla de atalho Ctrl + D ou use a opção de arrastar célula para baixo no excel.
Como você pode ver no instantâneo acima, os pontos marcados nos 4 meses diferentes.
Obtenha a soma de todos os pontos por 4 meses.
Isso significa que a equipe marcou um total de 18 pontos em 4 meses.
Método alternativo
Use o método SUM e array em vez de usar a função SUMPRODUCT.
Use a fórmula:
{= SOMA (SOMASE (resultado, B3: E3, pontos))}
Os colchetes são o método de matriz de sugerir que a função calcule todos os valores retornados pela função SOMASE.
Nota: Não use os símbolos de chaves manualmente. Use o Ctrl + Shift + Enter para usar o método de matriz no Excel.
Como você pode ver na fórmula acima, você pode obter o total de pontos usando a tabela de pesquisa condicional.
Notas:
- A fórmula só funciona com números.
- A fórmula funciona apenas quando não há duplicatas na tabela de pesquisa
- A função SUMPRODUCT considera os valores não numéricos como 0s.
- A função SUMPRODUCT considera o valor lógico TRUE como 1 e False como 0.
- A matriz do argumento deve ter o mesmo comprimento, senão a função
Espero que este artigo sobre como retornar SOMA somente de fórmulas no Excel seja explicativo. Encontre mais artigos sobre as funções SUMPRODUCT aqui. Por favor, compartilhe sua consulta abaixo na caixa de comentários. Nós iremos atendê-lo.
Como usar a função SUMPRODUCT no Excel: Retorna a soma após a multiplicação de valores em várias matrizes no Excel.
SUM se a data estiver entre : Retorna a SOMA dos valores entre datas ou períodos fornecidos no Excel.
Soma se a data for maior que a data fornecida: Retorna a soma dos valores após a data ou período especificado no Excel.
2 maneiras de somar por mês no Excel: Retorna a SOMA dos valores em um determinado mês específico no Excel.
Como somar várias colunas com condição: Retorna a soma dos valores em várias colunas com condição no Excel
Artigos populares
50 Atalho do Excel para aumentar sua produtividade
Editar uma lista suspensa
Referência absoluta no Excel
Se com formatação condicional
Se com curingas
Vlookup por data
Converter polegadas em pés e polegadas no Excel 2016
Junte-se ao nome e sobrenome no excel
Contar células que correspondam a A ou B