18 de março de 2012

Como eu faço para somar utilizando vários critérios.

O Excel já possui uma função para somar valores utilizando critérios neste caso a função SOMASE().
Mas quando você necessitava incluir mais critérios, sempre tinha que ativar a criatividade e utilizar algumas funções complementares até mesmo funções de banco de dados mas nem sempre resolviam de forma satisfatória.
Com o Excel 2007 esta situação pode ser resolvida com a função SOMASES().

Vamos ter o seguinte exemplo: em uma planilha temos a coluna TIPO DE PRODUTO, ESTADO e VALOR, e gostaria somas os valores do estado de “SP”.
A função SOMASE(intervalo; critérios; [intervalo_soma])
Intervalo: a coluna onde estão os estados (C3:C9);
Critério: o estado desejado para somar “SP”;
Intervalo_soma: a coluna onde estão os valores a serem somados (D3:D9);
O resultado será: R$ 414,24 (os valores 121,45; 157,71; 135,08).

Mas se eu desejar somar os valores do estado de “SP” e dos produtos “Modelo A”?
A função SOMASES(intervalo_soma;intervalo_critérios1;critérios1; [intervalo_critérios2;critérios2];…)
Intervalo_soma: a coluna onde estão os valores a serem somados (D3:D9);
intervalo_critérios1: a coluna onde estão os estados (C3:C9);
critérios1: o estado desejado para somar “SP”;
intervalo_critérios2: a coluna onde estão os produtos (B3:B9);
critérios2: o produto desejado para somar “Modelo A”;
O resultado será: R$ 256,53 (os valores 121,45; 135,08).

Mas se eu desejar somar os valores do estado de “SP” e dos produtos “Modelo A” com valores maiores que R$ 130,00?
A função SOMASES(intervalo_soma;intervalo_critérios1;critérios1; [intervalo_critérios2;critérios2];…)
Intervalo_soma: a coluna onde estão os valores a serem somados (D3:D9);
intervalo_critérios1: a coluna onde estão os estados (C3:C9);
critérios1: o estado desejado para somar “SP”;
intervalo_critérios2: a coluna onde estão os produtos (B3:B9);
critérios2: o produto desejado para somar “Modelo A”;
intervalo_critérios3: a coluna onde estão os valores que serão o novo critério (D3:D9);
critérios3: o valores desejado para somar “> 130,00”.
O resultado será: R$ 135,08.

A partir daqui vale a sua necessidade e criatividade para utilizar a função SOMASE() ou SOMASES(), atenção apenas na hora de definir os intervalos e os critérios.

41 comentários:

  1. Olá, boa tarde.
    Não tem mesmo como utilizar esta fórmula em outra versão do Excel??
    Aqui na empresa ainda uso o 2003, tentei usar a fórmula e nada.
    Abraços.

    ResponderExcluir
  2. Olá, bom dia!
    E se eu quiser somar dois critérios no mesmo intervalo de critérios. Por exemplo, somar valor para SP e RJ independente do produto?

    Obrigada. Abraço!

    ResponderExcluir
    Respostas
    1. Se você deseja somar todas as vendas de SP e RJ independente do produto faça assim(mais fácil):
      SOMASE(C3:C9; "SP";D3:D9) + SOMASE(C3:C9; "RJ";D3:D9).

      Mas vou verificar se existe outra forma...

      Excluir
  3. O que há de errado na minha fórmula?
    =soma(SE(E(H5<="";I5<="";J5<="");SOMA(H4:J4);0)+SE(E(K5="";L5="";M5="");SOMA(K4:M4);0)+se(e(N5="";O5="";P5="");soma(N4:P4);0)+se(e(Q5="";R5="";S5="";soma(Q4:S4);0)
    Não consigo finalizá-la.
    Obrigada

    andreaherman@hotmail.com

    ResponderExcluir
  4. Andreaheman,
    a formula correta é :
    =SOMA(SE(E(H5<="";I5<="";J5<="");SOMA(H4:J4);0)+SE(E(K5="";L5="";M5="");SOMA(K4:M4);0)+SE(E(N5="";O5="";P5="");SOMA(N4:P4);0)+SE(E(Q5="";R5="";S5="");SOMA(Q4:S4);0)).

    Estava faltando um ")" no ultimo SE.
    ....";S5="");soma(Q4:S4);0)"

    ResponderExcluir
  5. quando uso a fórmula somases, com várias abas com intervalos a serem somados da erro #valor, tipo:

    =somases('Plan1:Plan10'!C1:C20;'Plan1:Plan10'!B1:B20;"JANEIRO";'Plan1:Plan10'!A1:A20;"CARRO 3")

    é uma planilha com várias abas para calcular quantidade de passageiros/mês de vários ônibus de uma frota, cada aba representa um ônibus.

    ResponderExcluir
    Respostas
    1. Então está estranha a sua fórmula principalmente 'Plan1:[Plan10]Plan10'! este trecho deveria identificar o diretório onde esta o arquivo Excel onde esta a pasta o que não me parece ser o que esta acontecendo. REFAÇA a fómula deverá fica rtipo assim: 'C:\Users\Documentos\[CLIENTE_A.xls]Revenue'!
      * O diretório onde esta o arquivo: C:\Users\Documentos\
      * O nome do arquivo: [CLIENTE_A.xls]
      * a planilha aonde estão os valores: Revenue'!

      Excluir
  6. BOA NOITE PRECISO DE AJUDA SR. EXCEL.
    TENHO A TABELA ABAIXO ONDE PRECISO QUE TODA AS VEZES QUE DIGITAR O SERVIÇO NA COLUNA D, ELE INFORME NA COLUNA F O VALOR TOTAL. CONSEGUI FAZER APENAS PARA UMA CELULA E NÃO CONSEGUI PARA A TABELA TODA.
    =SE($D$2:$D$47=I2;K2;"").TEMOS UM SALAO DE CABELEREIRO E ESTOU INFORMATIZANDO ELE.




    ResponderExcluir
    Respostas
    1. Procure retirar o $ da formula, isso fixa os valores o que pode estar gerando erros nos valores.

      Excluir
  7. Boa Tarde
    Eu tenho uma dúvida se me puder ajudar.
    Eu preciso usar a função CONTAR.VAL num mapa que uso para agendar as férias de 100 funcionários. O Objectivo é contar o total de dias consecutivos de férias, contando os valores em cada linha quer sejam numericos (usamos o "1" para cada dia de férias e o "F" para dia de folga), tem de somar estes dois critérios mas não pode somar outras letras que usamos para determinar outras situações.
    Como faço? Já tentei de várias formas e dá erro. Obrigada!

    Silvia Pires

    ResponderExcluir
    Respostas
    1. Para contar o número de vezes que o valor aparece em uma celula seria a função CONT.VALORES().

      Porém se os valores estão na mesma coluna você deve utilizar a função CONT.SE() ou CONT.SES() vai dar certo.

      Excluir
  8. Tava quebrando a cabeça para fazer estas fórmulas, muito obrigado, consegui o q queria.

    ResponderExcluir
  9. Eu estou fazendo uma planilha que contem várias abas, e eu estou usando a função somases utilizando 03 critérios, porém a formúla não está puxando os valores. Você sabe me dizer o que poderia ser?
    A formula é a seguinte:
    =SOMASES('O2 5156 PVBN'!$Q:$Q;'O2 5156 PVBN'!$A:$A;Detalhada!D$3;'O2 5156 PVBN'!$D:$D;Detalhada!$B12;'O2 5156 PVBN'!$H:$H;Detalhada!D$5)

    Obrigado.

    ResponderExcluir
  10. Então Caroline!
    Simulei aqui e funcionou corretamente.
    Você que somar valores da coluna 'Q' da planilha 'O2 5156 PVBN'=Ok!
    1.CRITÉRIO: A informação que esta na célula 'D3' da planilha 'Detalhada' tem que existir na coluna 'A' da planilha 'O2 5156 PVBN'=Ok!
    2.CRITÉRIO: A informação que esta na célula 'B12' da planilha 'Detalhada' tem que existir na coluna 'D' da planilha 'O2 5156 PVBN'=Ok!
    3.CRITÉRIO: A informação que esta na célula 'D5' da planilha 'Detalhada' tem que existir na coluna 'H' da planilha 'O2 5156 PVBN'=Ok!

    Se for isso que você quer então a formula esta certa! Porém faça um teste verifique se todos os critérios são verdadeiros porque se um for falso a função SOMASES() vai retornar ZERO.

    Como verificar de os critérios são verdadeiro:
    1. A informação que esta na célula 'D3' da planilha 'Detalhada' exite na coluna 'A' da planilha 'O2 5156 PVBN' ?
    2. A informação que esta na célula 'B12' da planilha 'Detalhada' existi na coluna 'D' da planilha 'O2 5156 PVBN'?
    3. A informação que esta na célula 'D5' da planilha 'Detalhada' existir na coluna 'H' da planilha 'O2 5156 PVBN'?

    Lembre-se que se um resultar em FALSO o SOMASES() vai ser igual a zero.

    E se depois de verificar se todos são VERDADEIRO, analise se o conteúdo das células dos intervalo e conteúdo das células dos critérios não tem espaço em branco. Pode ser isso.

    QQ coisa retorne.

    ResponderExcluir
  11. Sr Excel,
    Estou desenvolvendo uma fórmula com dois critérios, mas não estão sendo puxados os valores. Pode me ajudar?

    =SOMASES(Maio_13!G283:G1263;Maio_13!D283:D1263;"AGUA";Maio_13!E283:E1263;4/5/2013)

    ResponderExcluir
    Respostas
    1. Caro Amigo!
      O detalhe para não estar "puxando" os valores é que porque ao utilizar DATA nos critérios você deve utilizar " (aspas).
      Verifique também como você digitou as DATAS e se estão formatadas corretamente, isso poderá resultar em valores "zerados" para sua soma.

      A formula correta fica:
      SOMASES(Maio_13!G283:G1263;Maio_13!D283:D1263;"AGUA";Maio_13!E283:E1263;"4/5/2013")

      Um abraço

      Excluir
  12. Este comentário foi removido pelo autor.

    ResponderExcluir
  13. Sr. Excel, Tudo bem?
    Me ajuda em uma coisa... Quero somar utilizando 2 critérios, ex. O que embarcou por transportadora em determinada data.
    Data (diversas datas); Transp (diversas transportadoras); Cubagem (valores). Então quero saber a soma de cubagem só do que embarcou com data de ontem da transportadora 01. É possível?

    ResponderExcluir
  14. Ana Paula, Supondo que:
    as DATAS estão na coluna A
    as TRANSPORTADORAS estão na coluna B
    as CUBAGENS estão na coluna C

    A formula dica assim:
    =SOMASES($C:$C;$A:$A;"16/07/2012";$B:B;"Transportadora 01") perceba que tanto a data como o nome da transportadora estão entre aspas.
    isso se você preferir digitar os valores para data e transportadora.

    Caso você utilize os valores em células a formula dica assim:
    =SOMASES($C:$C;$A:$A;E2;$B:B;F2) perceba que tanto a data como o nome da transportadora foram trocados pelos endereços aonde estão os valores para data e transportadora.

    ResponderExcluir
    Respostas
    1. Deu super certo! Você realmente é demais! Obrigada!

      Excluir
  15. Como.usar somases com duas pasta por que aparece este sibolo !

    ResponderExcluir
    Respostas
    1. Muito bem....

      O Excel utiliza alguns símbolos para identificar a informação que compõe toda sua formula, mas vamos começar do começo:

      1. Números para identificar a linha na planilha;
      2. Letras para identificar a coluna na planilha;
      3. Célula que é o endereço aonde esta o valor que você estará utilizando na formula. Neste caso é representada por uma letra e um número ou seja uma coluna e uma linha. ex1. B4, C567, AA56 (neste último com duas letras uma vez que as colunas são nomeadas de A até XFD combinando entre si. Ou seja depois da coluna de letra Z vem a coluna AA e assim por diante.
      4. O ";" ponto e virgula serve para separar os parâmetros de uma formula. Neste caso o intervalo e o critério;
      5. O ":" dois pontos serve para identificar o intervalo de células, ou seja aonde começa e aonde termina o intervalo. Neste caso começa na célula B3 e termina na célula B12;
      6. O "!" a exclamação (que você perguntou!) representa o nome da planilha que você esta utilizando na sua formula, ela vem sempre precedida do nome da planilha em questão. ex.=SOMASE(Plan2!B3:B12;Plan3!B4), neste exemplo o intervalo "B3:B12" esta na planilha "Plan2" já o critério "B4" esta na planilha "Plan3". Caso você retire a "!" a formula poderá apresentar erros. Essa identificação é feita pelo próprio EXCEL quando você esta escrevendo a formula.
      7. Os "[", "]" colchetes representa o nome da pasta(arquivo) que você esta utilizando com sua formula, eles são sempre separados pelo do nome da pasta(arquivo)em questão. ex.=SOMASE(Plan2!B3:B12;[critérios.xlsx]Plan3!B4), neste exemplo o critério "B4" esta na planilha "Plan3" que esta na pasta(arquivo) critérios.xlsx;
      8. O "'" apostrofe representa o caminho aonde se encontra a pasta(arquivo) que você esta utilizando na sua formula, ele vem sempre separados pelo caminho do arquivo em questão.
      ex.=SOMASE(Plan2!B3:B12;'C:\SenhorExcel\Documents\[critérios.xlsx]Plan3'!B4), neste exemplo o critério "B4" esta na planilha "Plan3" que esta na pasta(arquivo) critérios.xlsx que por sua vez esta no caminho C:\SenhorExcel\Documents\ (local aonde se encontra o arquivo);


      É acho que é isso,
      Atenção 1: Fique atendo para "editar" as suas formulas e não excluir acidentalmente alguns destes símbolos, a formula poderá apresentar erros.
      Atenção 2: Quando você utilizar arquivos que estão armazenados em caminhos que você esta utilizando na formula tome cuidado para não apagar acidentalmente o arquivo, isso poderá apresentar erros.

      Senhor Excel.


      Excluir
    2. No questionamento sobre somar todas as vendas de SP e RJ tem um jeito de colocar numa única função sem ter que fazer SOMASE(C3:C9; "SP";D3:D9) + SOMASE(C3:C9; "RJ";D3:D9)?

      Excluir
  16. É possível sim, utilizando o conceito de MATRIZES.

    1. Coloque os valores de "SP" e "RJ" em um intervalo, denominado aqui de intervalo de critério. (H3:I3)
    2. Digite a função =SOMA(SE(C3:C9=H3:I3;D3:D9))
    3. Ao Confirmar a Função digitada pressione Shift+Ctrl+Enter
    4. O resultado deve ficar assim {=SOMA(SE(C3:C9=H3:I3;D3:D9))} (com colchetes), isso identifica que a formula SOMASE() esta como MATRIZ.
    5. Pronto.

    Caso deseje altere as siglas de estados ou os valores de cada linha... Dá certo.
    Se precisar de mais detalhes escreva novamente.

    ResponderExcluir
  17. Olá, preciso somar os valores de várias abas, porém tenho que localizar esse valor através de um critério. Como se eu fosse consolidar 20 abas em 1 só, porém, tem produto que pode ter em uma aba que nao tem na outra. Vc consegue me ajudar?

    ResponderExcluir
  18. Sim,
    1. A aba vai consolidar os valores das demais abas deve conter todos os produtos que existam em todas as outras 20 abas, independente se o produto aparece em todas as abas ou não. Apareceu em apenas em 1 aba ele devem constar na aba consolidada;
    2. Identifique com a função CONT.SE() ou SOMASE() os valores que voce quer consolidar.

    Pronto! Feito
    (caso deseje envie a planilha em questão que retorno com a solução).

    ResponderExcluir
  19. Boa tarde,

    Gostaria de solucionar uma duvida...tenho uma planilha q na coluna A1:A20 tem as datas que se repetem ou não e na coluna B1:B20 os valores, como faço pra somar todos os valores somente das datas do mes. ex:

    01/10/2013 50,00
    05/10/2013 100,00
    05/11/2013 75,00
    01/10/2013 500,00

    ResponderExcluir
    Respostas
    1. Marcio Roberto,
      Esta é solução simples quando você utiliza o conceito de MATRIZES do EXCEL.
      Seguindo o seu exemplo apenas colocaria o mês que você deseja soma em uma célula tipo para facilitar a troca fica assim:

      As datas no intervalo A1:A20;
      Os respectivos valores no intervalo B1:B20
      O mês de pesquisa: D1
      O Resultado na D2: {=SOMA(SE(MÊS(A1:A20)=$D$1;B1:B20;0))}

      Lembre que ao final da digitação voce deve pressionar Crtl + Shift + ENTER para que a formula fique como matriz, caso contrário a formula ficara errada.
      Caso queira alterar o mês de pesquisa altere a celula D1;

      Pronto! Feito.
      Senhor Excel.

      Excluir
  20. Sr. Excel, preciso de ajuda: tenho uma planilha onde listo os descartes de resíduos por volume. Preciso somar os volumes utilizando dois critéios diferentes...e acima não consegui que nenhum exemplo funcionasse.
    A planilha tem esses dados: mes, nº da semana em cada mês (01 a 04 ou 05) volume. Preciso somar o volume da semana 1 do mes de outubro, por exemplo...help me, please!

    ResponderExcluir
  21. SanVal,

    Mais um exemplo para o BLOG... vamos lá...

    Na coluna A colocamos os meses;
    Na coluna B colocamos as semana;
    Na coluna C colocamos os volumes;

    A formula fica assim: =SOMASES(C:C;A:A;10;B:B;1)
    Onde 10 é o mês de outubro e 1 é a semana em questão.

    Você poderá trocar os valores fixo de Mês e Semana pra endereço de células por exemplo a formula ficaria assim:

    =SOMASES(C:C;A:A;G5;B:B;G6)
    Na células G5 colocamos o mês que você desejar pesquisar;
    Na células G6 colocamos a semana que você desejar pesquisar;


    Espero ter ajudado!
    Senhor Excel.

    ResponderExcluir
  22. Sr. Excel, preciso de ajuda. Tenho uma coluna que eu quero somar se dois critérios estiverem estabelecidos em 02 colunas ex:
    a 2007
    b luz
    c soma da luz em 2007
    obrigado

    ResponderExcluir
    Respostas
    1. Caro usuário.

      Acesse
      http://senhorexcel.blogspot.com.br/2013/12/novos-exemplos-como-eu-faco-para-somar.html

      Excluir
  23. Sr. Excel

    como devo proceder para fazer um SOMASE ou SOMASES onde eu quero que ele some todos os critérios com exceção de um critério. Ex: tem uma planilha com informações de vendas dos produtos: Produto A, Produto B, Produto C, Produto D. Pedido 101, 102, 103 e 104. Supondo que tenho vendas de todos os produtos em todos os pedidos, como faço para fazer um SOMASE ou SOMASES onde eu consiga somar as vendas de todos os produtos, exceto um deles?

    ResponderExcluir
    Respostas
    1. Caro usuário.

      Acesse
      http://senhorexcel.blogspot.com.br/2013/12/novos-exemplos-como-eu-faco-para-somar.html

      Excluir
  24. Sr Excel;

    Como fazer para somar os 10 maiores números de uma lista de 20 números?

    ResponderExcluir
    Respostas
    1. Olá Francisco,
      A sua solução é mais simples não necessita utilizar o SOMASE() ou SOMASES()
      O resultado da soma dos 10 maiores números de uma lista (qq tamanho) será:
      =SOMA(MAIOR(B4:B21;{1.2.3.4.5.6.7.8.9.10}))

      Onde temos:
      Função SOMA()
      Função MAIOR() - A função MAIOR reconhece números repetidos
      ex. se sua lista tiver [22,21,19,10,10,9,9,9,8,8] ele reconhece com 10 maiores e não 6 maiores.
      Intervalo da lista: B4:B21
      TOP 10: isso {1.2.3.4.5.6.7.8.9.10} na formula significa a quantidade de 'maiores que você precisa somar', lembre das chave'{' para ele entender que deve trabalhar como matriz. Você pode alterar conforme sua necessidade.

      Você pode somar os 10 menores trocando a função MAIOR() pela MENOR()
      Ou ainda somar todos que estão abaixo da média......

      Espero ter auxiliado.
      Senhor Excel

      Excluir
  25. Este comentário foi removido pelo autor.

    ResponderExcluir
  26. Olá por favor gostaria que me ajudasse em uma planilha... preciso somar por placa de veiculo, a data correspondente a cada mes e somente se não houver desconto. Estou utilizando a seguinte formula: =SE(E('2014'!$C$3:$C$800>="01/01/2014";'2014'!$C$3:$C$800<"01/02/2014");SOMASES('2014'!$G$5:$G$800;'2014'!$E$5:$E$800;"BKB-6557";'2014'!$H$5:$H$800;"NÃO");0) - onde a coluna '2014'!$C seria correspondente as datas a coluna '2014'!$G correspondente ao intervalo de soma, a coluna '2014'!$E correspondente a placa do veiculo, a coluna '2014'!$H correspondente a SIM ou NÃO, quando utilizo somente a função somase ela puxa os valores corretos, mas quando concilio com a função se para achar as datas em algumas linhas da planilha ela "puxa" correto em outras não... Poderia me ajudar por favor!

    ResponderExcluir
    Respostas
    1. Lisa,

      A solução é simples / complicada....
      Se entendi você quer somar valores (Coluna G) e com vários critérios.
      Como você comentou o SOMASES funciona já com o SE() não funciona, isso acontece porque o SE() trabalha em cada linha e não intervalo, para isso você teria que utilizar o conceito de matrizes e daria mais trabalho.

      Porém utilizando o SOMASES() e fazendo alguns ajustes você vai conseguir somar os valores, veja como fica a NOVA formula:

      =SOMASES('2014'!$G$1:$G$7;'2014'!$E$1:$E$7;"BKB-6557";'2014'!$H$1:$H$7;"NÃO";'2014'!$C$1:$C$7;">=01/01/2014";'2014'!$C$1:$C$7;"<01/02/2014")

      As condições que você tinha colocado no SE() eu incluí no SOMASES() com isso você vai conseguir somar.

      Você ainda poderá fazer alguns ajustes para tornar mais fácil outras consultas veja:
      1º Digite a DataInicio na célula M2
      2º Digite a DataFim na célula M3
      3º Digite a Placa na célula M5

      A nova formula ficaria:
      =SOMASES('2014'!$G$1:$G$7;'2014'!$E$1:$E$7;M5;'2014'!$H$1:$H$7;"NÃO";'2014'!$C$1:$C$7;CONCATENAR(">=";M2);'2014'!$C$1:$C$7;CONCATENAR("<";M3))

      Agora é só alterar os intervalos de data e trocar a placa para ter o novo resultado da consulta do SOMASES()

      Espero ter ajudado!
      Senhor Excel

      Excluir
  27. Tenho uma planilha, onde controle todas as cópias (xerox), impressões, dentre outras. Quero dividir os ganhos mensais entre eu e mais duas pessoas. O meu objetivo é saber quantos cada um vendeu durante o mês, levando em conta que cada venda colocamos a data, que fórmula utilizar.

    ResponderExcluir
  28. Olá! Adriano

    Para controlar as vendas é necessário para cada venda você ter o "vendedor", por isso é necessário criar uma coluna com essa informação.


    =SOMASES(B3:B17;A3:A17;F3;C3:C17;">="&F4;C3:C17;"<="&F5)
    onde:
    B3:B17 - Intervalo dos valores das vendas;
    A3:A17 - Intervalo aonde identifica os vendedores;
    F3 - celual onde você define o nome do vendedor a pesquisar;
    C3:C17 - intervalo das datas das vendas;
    ">="&F4 - limite de apuração de vendas (data inicial);
    "<="&F5 - limite de apuração de vendas (data final);
    lembre que o sinal de MAIOR igual tem que estar entre aspas duplas...ok!

    Senhor Excel.

    ResponderExcluir