Somar se as células contiverem texto específico noutra coluna

Este tutorial mostra-lhe como somar valores quando as células contêm texto específico ou parcial noutra coluna. Tomando como exemplo o seguinte intervalo de dados, tanto a função SOMASE como a função SOMARPRODUTO permitem obter os montantes totais dos produtos que incluem o texto «T-Shirt» no Excel.

• Somar valores se a célula contiver texto específico ou parcial com a função SOMASE
• Somar valores se a célula contiver texto específico ou parcial com a função SOMARPRODUTO

Somar valores se a célula contiver texto específico ou parcial com a função SOMASE

Para somar valores quando uma célula contém texto específico noutra coluna, utilize a função SOMASE com o caráter universal (*). As sintaxes genéricas são:

Fórmula genérica com texto codificado diretamente:

• range: O Intervalo de Dados que pretende avaliar utilizando os critérios;
• *text*: Os critérios com base nos quais pretende somar os valores. Aqui, o caráter universal * é utilizado para encontrar qualquer número de caracteres; para corresponder a todos os itens que contenham um texto específico, coloque esse texto entre dois caracteres *. ()Tenha em atenção que tanto o texto como o caráter universal devem ser colocados entre aspas duplas.)
• sum_range: O intervalo de células com os valores numéricos correspondentes que pretende somar.

Fórmula genérica com referência de célula:

• range: O Intervalo de Dados que pretende avaliar utilizando os critérios;
• "*"&cell&"*": Os critérios com base nos quais pretende somar os valores;
  • *: O carácter universal que encontra qualquer número de caracteres.
  • célula: A célula contém o texto específico que pretende procurar.
  • &: Este operador de concatenação (&) é utilizado para juntar uma referência de célula com asteriscos.
• sum_range: O intervalo de células com valores numéricos correspondentes que pretende somar.

Depois de compreender a operação básica da função, utilize qualquer uma das seguintes fórmulas conforme necessário e, em seguida, prima a tecla Enterpara obter o resultado:

Nota: A função SOMASE não faz distinção entre maiúsculas e minúsculas.

Somar valores se a célula contiver texto específico ou parcial com a função SOMARPRODUTO

A função SOMARPRODUTO também pode ajudá-lo a concluir esta tarefa sem recorrer a quaisquer caracteres universais. Neste caso, deverá incorporar as funções ÉNÚMERO e PESQUISAR dentro da função SOMARPRODUTO. A sintaxe genérica é:

• sum_range: O intervalo de células com valores numéricos correspondentes que pretende somar;
• criteriaOs critérios com base nos quais pretende somar os valores podem ser uma referência de célula ou um texto específico que definiu.
• range: O Intervalo de Dados que pretende avaliar utilizando os critérios;

Introduza qualquer uma das fórmulas seguintes numa célula vazia e prima a tecla Enterpara obter o resultado:

Explicação desta fórmula:

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(«T-Shirt»,$A$2:$A$12))))

• SEARCH(«T-Shirt»,$A$2:$A$12): A função PESQUISAR devolve a posição do texto específico «T-Shirt» no intervalo de dados A2:A12, resultando numa matriz como esta: {5;#VALOR!;#VALOR!;7;#VALOR!;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}): A função ÉNÚMERO testa quais valores são numéricos e devolve uma nova matriz: {VERDADEIRO;FALSO;FALSO;VERDADEIRO;FALSO;VERDADEIRO;FALSO;FALSO;FALSO;FALSO;VERDADEIRO}.
• $B$2:$B$12*(ISNUMBER(SEARCH(«T-Shirt»,$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{VERDADEIRO;FALSO;FALSO;VERDADEIRO;FALSO;VERDADEIRO;FALSO;FALSO;FALSO;FALSO;VERDADEIRO}: Ao multiplicar estas duas matrizes, a operação matemática converte automaticamente os valores VERDADEIRO e FALSO em 1 e 0, respetivamente. Assim, a multiplicação resulta em: {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}): Por fim, a função SOMARPRODUTO soma todos os valores da matriz para obter o resultado: 1605.

Função relativa utilizada:

Mais artigos: