SOMASES com duas condições e casos E e OU

A função SOMASES é uma das mais utilizadas por aqueles que fazem análises no Excel, visto que ela permite somar valores que respeitem certos critérios. No entanto, é comum precisarmos fazer o SOMASES com duas ou mais condições e critérios, e é nesse ponto que muitos usuários ficam com dúvidas.

Sendo assim, para aqueles que utilizam essa fórmula (principalmente no início da utilização) a tentativa de usar o SOMASES com um conceito de “E” ou “OU”:

  • Caso uma coluna tenha que respeitar um critério E outro critério ou;
  • Caso uma coluna tenha que respeitar um critério OU outro critério

A fórmula, no seu estado natural não é preparada para estes dois cenários, mas podemos usar o Excel de modo criativo para chegar nos nossos resultados.

entrevista de emprego

Além disso, esse artigo tem vídeo disponível no nosso canal no YouTube. Caso queira, veja a versão em vídeo, logo abaixo. Não se esqueça de nos seguir por lá para sempre receber conteúdo!

A fórmula SOMASES

Em resumo, a fórmula SOMASES serve para somar os dados de um determinado intervalo, desde que existam outros intervalos de critérios que respeitarão critérios estabelecidos na fórmula.

Por exemplo, é possível saber a soma de todas as vendas realizadas por um vendedor desde que, via SOMASES, coloquemos para somar a coluna de “valor da venda”, respeitado o critério da coluna “nome do vendedor” ser igual ao nome do vendedor em questão.

Para a abordagem deste artigo, usaremos um exemplo em que é possível aplicar a fórmula SOMASES. É uma base onde temos 3 colunas: nome, salário e estado. Nela, estão contidas essas informações de 22 pessoas. A fórmula SOMASES pode ser usada para, por exemplo, saber qual a soma de salários de todos os moradores do estado de Minas Gerais. Essa fórmula ficaria: =SOMASES(B:B;C:C;”Minas Gerais”), partindo da premissa que “B” é a coluna dos salários e “C” a coluna dos estados. A tabela está na Figura 1.

Figura 1

O conceito “E” no SOMASES com duas condições

A princípio, na figura 2, temos um trecho da tabela da figura 1 representado. A diferença é que (além de ser apenas um trecho) temos o nome das colunas para facilitar a compreensão.

somases duas condições
Figura 2

Usamos o conceito “E” em um SOMASES quando temos mais de um critério envolvendo a mesma coluna.

entrevista de emprego

Caso queiramos somar todos os salários que estejam entre 2.500 e 3.500 (inclusives), teremos que repetir por duas vezes a coluna B, mas neste caso como “intervalo de critérios”. A fórmula que representa isso é a seguinte:

=SOMASES(B:B;B:B;”>=”&2500;B:B;”<=”&3500)

Perceba que a coluna B aparece 3 vezes, as quais detalhamos de acordo com a cor:

  • Verde: cumpre função de intervalo de soma
  • Vermelho: cumpre função de intervalo de critério, representando o intervalo inferior da série
  • Azul: cumpre função de intervalo de critério, representando o intervalo superior da série

Para que uma coluna tenha que respeitar um critério E outro critério, basta incluirmos o “;” para adicionar nova condição (as condições não são limitadas a uma utilização por coluna).

Geralmente, essa utilização está restringida a números (pois é com números que facilmente conseguimos que um mesmo valor cumpra mais de uma condição – no caso, maior que 2.500 e menor que 3.500).

No nosso exemplo, tivemos a coincidência de a coluna onde temos os critérios ser exatamente a coluna da soma, mas nem sempre é assim. Poderíamos ter uma coluna final com a idade de cada pessoa, e desejarmos somar o salário (coluna B) desde que a idade esteja entre 25 e 45 anos (coluna D, com condição “E”).

Por fim, note que neste caso o SOMASES com duas condições é resumido por condições na mesma coluna.

O conceito “OU” no SOMASES com duas condições

Do mesmo modo que no caso E, vamos entender o caso OU. A princípio, imagine a situação onde queremos, na mesma base apresentada, somar todos os que têm salário acima de 3.000 (inclusive), que sejam dos estados de Minas Gerais ou São Paulo. O nosso problema é que o cenário de “OU” não é contemplado pela fórmula SOMASES.

Podemos resolver isso de dois jeitos:

  • 1 – o jeito simples e rápido para poucos “OU”
  • 2 – o jeito complexo, mas que resolve qualquer quantidade de “OU”

Na sequência temos a resolução pelos dois métodos.

1 – o jeito simples e rápido para poucos “OU” no SOMASES com duas condições

Primordialmente, no nosso exemplo temos pouca variação nos critérios: queremos que o estado seja Minas Gerais ou São Paulo. Para este caso, vale aplicar uma solução simples: somar as fórmulas separadas.

Caso quiséssemos encontrar a soma dos salários de quem ganha mais que 3.000 de Minas Gerais, a fórmula seria:

=SOMASES(B:B;B:B;”>=”&3000;C:C;”Minas Gerais”)

De maneira análoga, para a mesma situação, mas envolvendo São Paulo, teríamos:

=SOMASES(B:B;B:B;”>=”&3000;C:C;”São Paulo”)

Como queremos saber qual a soma desde que o estado seja Minas Gerais OU São Paulo, a fórmula resultante seria a soma das duas fórmulas citadas, ficando:

=SOMASES(B:B;B:B;”>=”&3000;C:C;”Minas Gerais”) + SOMASES(B:B;B:B;”>=”&3000;C:C;”São Paulo”)

Na figura 3 a fórmula é mostrada. Note que na barra de fórmulas está mostrada a fórmula da célula G6, que é justamente a citada acima. Além disso, nas células G2 e G3 estão as fórmulas feitas individualmente para Minas Gerais e São Paulo, também mostradas acima. Em ambos os casos (ou fazendo a fórmula “resultante”, ou fazendo a soma das fórmulas separadas), chegamos na soma de 7.000.

somases duas condições
Figura 3

2 – o jeito complexo, mas que resolve qualquer quantidade de “OU”

O racional do jeito 1 citado anteriormente nos atende perfeitamente pois temos apenas duas condições: o estado deve ser Minas Gerais ou São Paulo.

No caso de muitos outros “ou”, a tarefa fica um pouco mais árdua (mas ainda possível): teríamos que “copiar e colar” a fórmula diversas vezes, para que no fim pudéssemos mudar o nome do estado em cada uma das fórmulas coladas. Teríamos o resultado que precisamos mas de uma forma bem trabalhosa.

entrevista de emprego

Para isso, podemos lançar mão de um método auxiliar, criando uma nova coluna que trará para nós, com uma única palavra, todos os estados que nos interessam. Os passos estão abaixo

a) Primeiramente, copie todos os estados para uma nova tabela. Para isso, selecionamos os estados que estão na coluna C e colamos ele em outro local. Removemos duplicadas (para deixar apenas valores únicos) em Dados > Ferramentas de Dados > Remover Duplicadas. É interessante também deixar a tabela em ordem alfabética, selecionando os estados restantes e indo em Dados > Classificar e Filtrar > A a Z. Após isso, crie uma coluna na frente da coluna trabalhada. O resultado é uma tabela como a da figura 4.

Figura 4

b) Em seguida, na coluna B dessa nova tabela (exatamente à frente dos nomes), vamos criar uma referência de “positivo” e “negativo” para cada nome, onde o positivo representa os estados que queremos somar. Pode ser um “sim” vs “não”, ou “ok” vs “não ok”, etc… neste exemplo, usaremos um “ok” vs “não ok”: “ok” para os estados que queremos considerar na soma (no caso, Minas Gerais e São Paulo); “não ok” para os estados que não queremos (todos os demais). A figura 5 representa a tabela resultante

somases duas condições
Figura 5

Continuando… o modo complexo é realmente complexo!

c) Logo depois de criada a tabela auxiliar, voltamos à nossa tabela original, mais precisamente na coluna que criamos, citada no início dessa etapa. Para fins de didática, a coluna está pintada de cinza. Nela colocaremos uma fórmula que procurará o nome do estado (coluna C) na tabela auxiliar que fizemos há pouco, retornando a segunda coluna (ok/não ok). O processo está representado na figura 6.

Figura 6

d) Então, é possível notar que a coluna resultante agora traz os valores “ok” e “não ok”. As únicas ocorrências de “ok” são aquelas que têm como estado Minas Gerais ou São Paulo, como desejado

e) Com isso, o nosso problema está resolvido. A única fórmula que deve ser considerada é =SOMASES(B:B;B:B;”>=”&3000;D:D;”ok”), conforme figura 7. O valor resultante (7.000) é o mesmo que encontrado pelo outro método.

somases duas condições
Figura 7

Como resultado, perceba que este segundo método é de mais fácil uso. Caso desejássemos tirar Minas Gerais e incluir Amazonas e Goiás, por exemplo, basta irmos na tabela, marcamos Minas Gerais como não ok e colocar ok para Amazonas e Goiás. O novo valor seria calculado rapidamente.

O método de criar uma tabela resumo das informações, incluir uma coluna que qualifica os dados do ponto de vista de utilização e cruza essa nova tabela com a tabela original é bem clássico e útil nas análises em Excel.

entrevista de emprego

Por fim, note que neste caso o SOMASES com duas condições é resumido por condições de dois valores diferentes na mesma coluna.

Quer aprender mais?

Assim, encerramos o nosso artigo! Hoje você aprendeu sobre como utilizar a função SOMASES com duas ou mais condições no Excel!

Ainda assim, você acredita que pode saber mais de Excel? Certamente você quer aprender Excel aprendendo o que realmente interessa e te destaca! Dessa forma, montamos o Trincheiras do Excel para te passar o Excel que vai chamar a atenção do seu chefe, aumentar suas chances de promoção e te trazer reconhecimento financeiro! Conheça mais detalhes clicando no banner abaixo.

entrevista de emprego