Soma se as células contêm texto específico em outra coluna
Este tutorial mostrará como somar valores se as células contiverem texto específico ou parcial em outra coluna. Tome o intervalo de dados abaixo como exemplo, para obter as quantidades totais dos produtos que contêm o texto “T-Shirt”, tanto a função SUMIF quanto a função SUMPRODUCT podem lidar com esta tarefa no Excel.
- Soma os valores se a célula contiver texto específico ou parcial com a função SUMIF
- Soma os valores se a célula contiver texto específico ou parcial com a função SUMPRODUCT
Soma os valores se a célula contiver texto específico ou parcial com a função SUMIF
Para somar valores se uma célula contiver texto específico em outra coluna, você pode usar a função SOMASE com um caractere curinga (*), as sintaxes genéricas são:
Fórmula genérica com texto codificado:
- range: O intervalo de dados que você deseja avaliar usando os critérios;
- *text*: Os critérios nos quais você deseja somar os valores. Aqui, o caractere curinga * é usado para encontrar qualquer número de caracteres, para combinar todos os itens que contêm um texto específico, coloque o texto entre os dois * caracteres. (Observe que você deve colocar o texto e o curinga entre aspas duplas.)
- sum_range: O intervalo de células com valores numéricos correspondentes que você deseja somar.
Fórmula genérica com referência de célula:
- range: O intervalo de dados que você deseja avaliar usando os critérios;
- "*"&cell&"*": Os critérios nos quais você deseja somar os valores;
- *: O curinga que encontra qualquer número de caracteres.
- célula: A célula contém o texto específico a ser procurado.
- &: Este operador de concatenação (&) é usado para unir uma referência de célula com os asteriscos.
- sum_range: O intervalo de células com valores numéricos correspondentes que você deseja somar.
Depois de conhecer o funcionamento básico da função, por favor, use qualquer uma das seguintes fórmulas que você precisa e, em seguida, pressione Entrar chave para obter o resultado:
=SUMIF($A$2:$A$12,"*"&D2&"*",$B$2:$B$12) (Use a cell reference)
Note: Esta função SOMASE não faz distinção entre maiúsculas e minúsculas.
Soma os valores se a célula contiver texto específico ou parcial com a função SUMPRODUCT
A função SUMPRODUCT também pode ajudá-lo a terminar este trabalho sem usar nenhum caractere curinga. Aqui, você deve envolver as funções ISNUMBER e SEARCH dentro de uma função SUMPRODUCT, a sintaxe genérica é:
- sum_range: O intervalo de células com valores numéricos correspondentes que você deseja somar;
- criteria: Os critérios nos quais você deseja somar os valores. Pode ser uma referência de célula ou um texto específico que você definiu;
- range: O intervalo de dados que você deseja avaliar usando os critérios;
Use qualquer uma das fórmulas abaixo em uma célula em branco e pressione Entrar chave para retornar o resultado:
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(D2,$A$2:$A$12)))) (Use a cell reference)
Explicação desta fórmula:
= SUMPRODUCT ($ B $ 2: $ B $ 12 * (ISNUMBER (SEARCH ("Camiseta", $ A $ 2: $ A $ 12))))
- PESQUISA ("Camiseta", $ A $ 2: $ A $ 12): A função SEARCH retorna a localização do texto específico “T-Shirt” do intervalo de dados A2: A12, então, você obterá uma matriz como esta: {5; #VALUE!; # VALUE!; 7; #VALUE! ; 7; #VALOR!; # VALOR!; # VALOR!; # VALOR!; 7}.
- ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): Esta função ISNUMBER é usada para testar os valores numéricos e retorna uma nova matriz: {TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE}.
- $B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}: Para multiplicar essas duas matrizes, a operação matemática força os valores TRUE e FALSE para 1s e 0s. Portanto, a multiplicação das duas matrizes será assim: {347; 428; 398; 430; 228; 379; 412; 461; 316; 420; 449} * {1; 0; 0; 1; 0; 1; 0; 0; 0; 0; 1} = {347; 0; 0; 430; 0; 379; 0; 0; 0; 0; 449}.
- SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}): Finalmente, a função SUMPRODUCT soma todos os valores na matriz para obter o resultado: 1605.
Função relativa usada:
- SUMIF:
- A função SUMIF pode ajudar a somar células com base em um critério.
- SUMPRODUCT:
- A função SUMPRODUCT pode ser usada para multiplicar duas ou mais colunas ou matrizes e obter a soma dos produtos.
- NÚMERO:
- A função ISNUMBER do Excel retorna TRUE quando uma célula contém um número e FALSE se não.
- Pesquisar:
- A função SEARCH pode ajudá-lo a encontrar a posição de um caractere específico ou substring da string de texto fornecida
Mais artigos:
- Soma se começar ou terminar com texto ou caracteres específicos
- Para somar valores se as células correspondentes começarem ou terminarem com um valor específico, você pode aplicar a função SOMASE com um caractere curinga (*) para retirá-lo. Este artigo irá apresentar como usar a fórmula em detalhes.
- Soma os valores de N menores ou inferiores
- No Excel, é fácil somar um intervalo de células usando a função SUM. Às vezes, você pode precisar somar os 3, 5 ou n números menores ou mais baixos em um intervalo de dados, conforme a captura de tela mostrada abaixo. Neste caso, o SUMPRODUCT em conjunto com a função PEQUENO pode ajudá-lo a resolver este problema no Excel.
- Soma os valores de N menores ou inferiores com base em critérios
- No tutorial anterior, discutimos como somar os menores valores de n em um intervalo de dados. Neste artigo, realizaremos uma operação avançada adicional - somar os valores n mais baixos com base em um ou mais critérios no Excel.
As melhores ferramentas de produtividade para escritório
Kutools para Excel - ajuda você a se destacar na multidão
Kutools para Excel possui mais de 300 recursos, Garantindo que o que você precisa está a apenas um clique de distância...
Guia Office - Habilite leitura e edição com guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques do mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz guias eficientes para o Office (inclui Excel), assim como Chrome, Edge e Firefox.