Ir para o conteúdo principal

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

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:

=SUMIF(range,"*text*",sum_range)
  • 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:

=SUMIF(range,"*"&cell&"*",sum_range)
  • 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,"*T-shirt*",$B$2:$B$12)                     (Type the criteria manually)
=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 é:

=SUMPRODUCT(sum_range *(ISNUMBER(SEARCH(criteria,range))))
  • 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("T-Shirt",$A$2:$A$12))))          (Type the criteria manually)
=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 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.

As melhores ferramentas de produtividade para escritório

Kutools para Excel - ajuda você a se destacar na multidão

🤖 Assistente de IA do Kutools: Revolucionar a análise de dados com base em: Execução Inteligente   |  Gerar Código  |  Crie fórmulas personalizadas  |  Analise dados e gere gráficos  |  Invocar funções do Kutools...
Recursos mais comuns: Encontre, destaque ou identifique duplicatas  |  Excluir linhas em branco  |  Combine colunas ou células sem perder dados  |  Rodada sem Fórmula ...
Super VLookup: Vários critérios  |  Valor múltiplo  |  Em várias folhas  |  Pesquisa Difusa...
Av. Lista suspensa: Lista suspensa fácil  |  Lista suspensa de dependentes  |  Lista suspensa de seleção múltipla...
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas  |  Alternar status de visibilidade de colunas ocultas  Comparar colunas com Selecionar células iguais e diferentes ...
Recursos em destaque: Foco da Grade  |  Vista de Design  |  Grande Barra de Fórmula  |  Gerenciador de pastas de trabalho e planilhas | Biblioteca (Auto texto)  |  Data Picker  |  Combinar planilhas  |  Criptografar/Descriptografar Células  |  Enviar e-mails por lista  |  Super Filtro  |  Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens ...)  |  50+ de cores Tipos (Gráfico de Gantt ...)  |  Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário ...)  |  19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho ...)  |  12 Conversão Ferramentas (Números para Palavras, Conversão de moedas ...)  |  7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células do Excel ...)  |  ... e mais

Kutools para Excel possui mais de 300 recursos, Garantindo que o que você precisa está a apenas um clique de distância...

Descrição


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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations