Excel – função somarproduto

Dica da Alene para o blog espero que gostem

Ao invéz de ficar fazendo várias fórmulas SE em uma para obter várias condições de uma vez só vc pode usar apenas a fórmula do “SOMARPRODUTO” essa sim é mamão com açúcar no dia a dia da gente, olha só vou dar um exemplo (vou fazer um exemplo só com 2 condições pra ficar menor a fórmula, mas vc pode colocar qtas condições vc quiser, basta seguir o padrão):

……A………..B………..C…………….D………

1IMÓVEL…..COR……ENDEREÇO……..VALOR
2…CASA………ROSA….RUA BANGU……13.000,00
3…APTº……….ROSA….RUA BRINCO…..15.000,00
4…CASA………AZUL….RUA E…………….20.000,00
5…CASA………ROSA….RUA OURO……..50.000,00

Vamos lá suponhamos que os dados acima seja minha tabela e eu quero comprar todas as “casa” que sejam “rosa”, quanto ficaria pra comprar todas?

eis a fórmula mágica:

=SOMARPRODUTO(–(A2:A5=”CASA”);–(B2:B5=”ROSA”);(D2:D5))

é isso aí, reparem que sempre o último parenteses não começa com “–“, porque? pq a fórmula reconhece “–” antes do “()” como sendo mais uma condição, no caso acima ele vai somar o valor da coluna “D” toda vez que encontrar as condições na coluna “A” e na coluna “B” caso contrário ela nao soma,  para ter mais condições basta vc continuar com a sequência “;–(intervalo_de_linha e a condição)” prontinho.

LEMBRETES IMPORTANTES SOBRE A FÓRMULA:

*** se vc for avaliar valores numéricos não usa-se “” aspas na condição, usa-se somento o numero a ser avaliado.
exemplo:
=SOMARPRODUTO(–(A2:A5=1);–(B2:B5<5);(D2:D5))
neste caso se na coluna “A” for 1 e na coluna B estiver menor que 5 vai somar o valor da col D.

*** se a condição que vc quer varia basta substituir o =”casa” por =B5, assim ela vai procurar o que estiver na célula B5, claro que vc pode mudar pra qualquer outra célula.
exemplo:
=SOMARPRODUTO(–(A2:A5=B5);–(B2:B5<5);(D2:D5))
neste caso se na coluna “A” for igual a célula B5 e na coluna B estiver menor que 5 vai somar o valor da col D.

…lembrando que se vc não quer que a fórmula mude o B5 quando a fórmula for arrastada pra copiar pra outras células tem que add o $ antes do B e antes do 5, ficará assim: $B$5 (essa vale pra qualquer fórmula táh!?)

*** Os intervalos de linhas tem que ser sempre os mesmos.
exemplo:
=SOMARPRODUTO(–(A2:A100=1);–(B2:B100<5);(D2:D100))
se tiver com intervalos diferentes a fórmula vai retornar: #VALOR!
exemplo da fórmula errada:
=SOMARPRODUTO(–(A2:A100=1);–(B2:B100<5);(D2:D112))

É isso aí galerinha, aposto que vai ser muito útil essa fórmula!!!

Informática gratuita para você.

Spread the word. Share this post!

51 Comments

  1. Nivilton

    Responder

    Utilizei a formula do SomarProduto, e o resultado da soma vem negativo. Como corrigir?

    Formula montada: =SE(I9=0;””;SE(P17=2007;SOMARPRODUTO(-(‘2007’!B6:B1380=Q17);-(‘2007’!D6:D1380=”A”);-(‘2007’!I6:I1380=”C”);(‘2007’!J6:J1380));SE(P17=2008;SOMARPRODUTO(-(‘2008’!B6:B5006=Q17);-(‘2008’!D6:D5006=”A”);-(‘2008’!I6:I5006=”C”);(‘2008’!J6:J5006));SE(P17=2009;SOMARPRODUTO(-(‘2009’!B6:B5006=Q17);-(‘2009’!D6:D5006=”A”);-(‘2009’!I6:I5006=”C”);(‘2009’!J6:J5006));SE(P17=2010;SOMARPRODUTO(-(‘2010’!B6:B5006=Q17);-(‘2010’!D6:D5006=”A”);-(‘2010’!I6:I5006=”C”);(‘2010’!J6:J5006)))))))
    Resultado: -293

  2. Davidson

    Responder

    Pesquisei por aqui para achar um meio de fazer uma tabela de horarios de trabalho mensal, e nao consegui nada, tenho que criar uma planilha para 8 trabalhadores, que trabalham em turno, mas a chatice e que certas pessoas nao podem fazer determinados turnos, todos tem que ter um fim de semana, sao 5 turnos diferentes, queria aprender uma planilha que se auto organize a medida que e preenchida, tipo se joao faz horario b nenhum dos outros pode fazelo naquele dia, obrigado por qualquer dica

  3. Everton

    Responder

    Preciso somar a quantidade de votos por vendedor.
    Controle de qualidade de atendimento. Ex.:

    Vendedor Ótimo——–Bom———Regular

    Vendedor A x
    Vendedor B x
    Vendedor A x
    Vendedor B x
    Vendedor C x

    Em outra tabela preciso que seja informado:
    Vendedor Ótimo——–Bom———Regular

    Vendedor A = 2
    Vendedor B = 1 1
    Vendedor C = 1

  4. Gérson

    Responder

    Caros colegas.
    Segui as recomendações e não funciona. Certamente estou fazendo algo errado. Alguém pode me dizer o que tem de errado nesta fórmula?
    Obrigado.

    =SOMARPRODUTO(-(LISTA!$A$7:$A$6000=$C$6);-(LISTA!$F$7:$F$6000=J22);(LISTA!$H$7:$H$6000))

  5. Jorge

    Responder

    Gérson,

    Veja essa fórmula que você digitou:

    =SOMARPRODUTO(-(LISTA!$A$7:$A$6000=$C$6);-(LISTA!$F$7:$F$6000=J22);(LISTA!$H$7:$H$6000))

    Veja a correta:

    =SOMARPRODUTO(–(LISTA!$A$7:$A$6000=$C$6);–(LISTA!$F$7:$F$6000=J22);(LISTA!$H$7:$H$6000))

    A diferença está no sinal menos (-) que você usou. Use menos menos (–) que vai dar certo.

    Abs.

    Obs.: embora já tenha passado bastante tempo da postagem do Gérson, essa dica pode ser útil para outras pessoas que lerem os posts. 😉

  6. Jorge

    Responder

    Ah… mais uma coisa… quando eu digo menos menos, significa dois sinais de menos seguidos. Aqui as postagens podem aparecer como se fosse um tracinho só mais comprido.

    Abs.

  7. Bianini

    Responder

    Ola pessoal, encontrei esse forum e achei muito bom. Sera que alguem aqui poderia me dar uma luz na seguinte questao: Precisa criar uma tabela geral de vendas para minha empresa de servicos em telecomunicaçoes, onde preciso de uma tabela geral que forneça dados gerais sobre todas as operaçoes, desde tipo de serviço vendido, se foi para cpf ou cnpj, nome do vendedor, um calculo interno de seus pontos.. enfim… massssss…. preciso que … ao clicar em determinado intem do balanço geral, ele redirecione para a planilha equivalente de todos aqueles dados, no entanto mais detalhados… deu para entender? Ou seja, preciso clicar, e entrar em outra planilha mais elaborada daquelas informaçoes….
    Alguem poderia me dar essa luz..?.
    Obrigado… QQ coisa, meu msn bianini_jns@hotmail.com…. No aguardo

  8. Jocielmo

    Responder

    olha,tá de parabens,mais é que eu estou em um conteudo não muito complicado,mais é dificil de passar pra galara,entende?
    é que se trata de pessoas que nunca ouviram falar no excel,e quando vc mostra uma forma simples,já causa espanto.como faço pra tornar minhas aulas mais dinamicas?

  9. José Luiz Júnior

    Responder

    preciso de uma formula que me indique quando os produtos do meu estoque estao abaixo do minimo necessário.

    exemplo:

    ITEM SALDO MINIMO ESTOQUE
    alcool 10 02 (ESTA CELULA
    EU QUERO EM
    VERMELHO )

    NA CELULA DO ESTOQUE CONTEM A SEGUINTE FORMULA:
    =SOMA(+G4;+H4;+I4;+J4;+K4;+L4;-M4;-N4;-O4;-P4;-Q4;-R4)

    SE QUISER EU MANDO UMA COPIA DA MINHA PLANILHA POR EMAIL PARA ANALIZAR MELHOR.

    E SE POSSIVEL ME DAR MAIS IDEIAS DE COMO POSSO TRABALHAR ESSE CONTROLE

    OBRIGADO

  10. Eric

    Responder

    Olá, a todos

    para o caso acima, tente utilizar a Formatação Condicional…vc coloca uma regra para a cedula, Caso seja menor de 10, por exemplo, ela fica vermelha, se for maior, fica verde…Ajuda muitoo

  11. Débora

    Responder

    olá, bom dia

    preciso de ajuda

    estou com uma tabela constando a data e o valor da venda
    preciso somar o valor vendido mês a mês

    espero obter ajuda.
    desde já obrigada

  12. josue santos

    Responder

    oi …
    ainda nao conseguir assimilar a formula para minha tabela.

    To querendo fazer somarproduto com condicoes.

    Tabela de Materias para construcao.

    ex:

    A……………B……………C……………D………….
    Maretial Quantidade Valor Lucro

    areia 5 85,00 10,00
    seixo 2 100,00 15,00
    cimento 3 66,00 3,00
    areia 2 30,00 3,00

    *** queria saber qual a Formula, so com a condicao so do material que esta na Coluna “A” e o valor q ta na Coluna “C”.

    Agradeco de ja

    Esperando Resposta ^^ …

  13. Patropi

    Responder

    Para o Josue Santos.
    Sei que já faz tempo que vc postou mas pode servir para outra pessoa também.

    Vc pode usar SOMAR PRODUTO
    =SOMARPRODUTO(–(A2:A5=”areia”);(C2:C5))

    ou pode usar SOMASE

    =SOMASE(A2:A5;”areia”;C2:C5)

  14. Responder

    Prezados,

    (urgente)

    Tenho a seguinte planilha de funcionários (+-7000 pessoas): Nome – UF – Cidade – Localização (se desligado, aposentado, na filial ou na matriz) – Cargo – Gerência.

    Necessito com urgência montar uma planila com número, da seguinte forma: quantas pessoas preenchem o requisito: RJ – Macaé – Filial – Gerente administrativo – Gerência de Contabilidade. Quantas preenchem o requisito: RJ – São Gonçalo – Aposentado – Assistente – Gerência Financeira.

    Agradeço imensamente!

  15. adriano

    Responder

    Tenho um acumulado com mais de 20.000 linhas, quando ultrapasso essa quantidade ocorre!! A pergunta é, tem limites de procura???
    =SOMARPRODUTO(-(ACUMULADO!D3:D16000=”jul”);-(ACUMULADO!B3:B16000=”CLIPTECH”);-(ACUMULADO!S3:S16000=”SEM DEFEITO”))*-1

  16. Juliana

    Responder

    O post pode ser antigo, mas a dica é super válida! hehehe Me ajudou a corrigir uma planilha! 🙂

  17. Michael

    Responder

    Para resolver o problema do sinal ficar negativo (invertido), coloquei dois e funcionou.

    =SOMARPRODUTO(–(C3:C5=”CASA”);(D3:D5);E3:E5)

    Na verdade é possivel inclusive multiplicar as matrizes antes de somar. Neste exemplo acima multiplique a matriz da coluna D pela matriz da coluna E dai somou POREM APENAS NAS LINHAS ONDE A COLUNA C TINHA O TEXTO “CASA”.

    Adorei isso! Valeu!

  18. Responder

    Fiz a seguinte formula : =SOMARPRODUTO(–(F7:F49=”Cred.Conta”);–(B7:B49=”Rec.Cartão”);(I7:I68))

    mas não está somando … o q fiz de errado ??? podem ajudar-me ???

  19. isabela

    Responder

    Belo post,

    Procurei em vários sites como resolver meu problema e com esse post consegui, mexi um pouco na formular e deu certo. obrigada.

  20. isabela

    Responder

    Uma dúvida, Como eu faria para fazer algo proximo a isso:

    Tenho essa tabela:

    c……………e…………………….
    crachá tempo de atraso
    01 00:10:12
    02 03:05:09
    01 02:00:25

    quero saber quantas vezes o cada cracha teve um atraso, ou seja quero uma formula que conta, com a condição de crachá = 01 e crachá 02;

    Caso possa ajudar agradeço, tentei usar o conta.valores usando esse — para condição mas não deu certo….

    =CONT.VALORES(–(Plan1!$C$3:$C$353=Plan3!C3);(Plan1!E3:E353))

  21. Alan

    Responder

    Tenho a seguinte condição

    A B C
    100,00 20/05 DINHEIRO
    200,00 21/05 CHEQUE
    100,00 20/05 DINHEIRO
    200,00 20/05 CHEQUE

    Gostaria de pegar e somar o que é dinheiro no dia 20/05

    Tenho a seguinte formula mais não soma?
    =somarprouto(–(E3:E8=”dinheiro”);–(D3:D8=”20/05/11″);(C3:C8))

    Favor ajudar, Obrigado!

  22. Alan

    Responder

    Tenho a seguinte condição

    A ……………..B…………. C
    100,00 20/05/11 DINHEIRO
    200,00 21/05/11 CHEQUE
    100,00 20/05/11 DINHEIRO
    200,00 20/05/11 CHEQUE

    Gostaria de pegar e somar o que é dinheiro no dia 20/05

    Tenho a seguinte formula mais não soma?
    =somarprouto(–(E3:E8=”dinheiro”);–(D3:D8=”20/05/11″);(C3:C8))

    Favor ajudar, Obrigado!

  23. Márcio

    Responder

    Bom dia a todos, a exemplo das formulas somarproduto acima, conforme SOMARPRODUTO(–(A2:A5=”CASA”);–(B2:B5=”ROSA”);(D2:D5)), simplesmente não funciona. Já fiz várias alterações mas a fórmula não deu certo. Não sei como está funcionando para os demais postados. Cheguei a verificar se depende da configuração em VBA análise, e está tudo ok

  24. Márcio

    Responder

    complementando a postagem anterior. A colagem da fórmula somarproduto está corretamente com os sinais “-” duplo, para que a formula funcione corretamente, contudo não funcionou também. Alguém sabe o que poderia estar acontecendo?

    • Rodrigo

      Responder

      Márcio,

      Esse comando que todos comentaram aqui só funciona para excel 2003, para versão acima de 2007, repita os comandos sem os sinais de menos , da seguinte forma: SOMARPRODUTO((A2:A5=”CASA”);(B2:B5=”ROSA”);(D2:D5))

  25. wagner

    Responder

    boa noite gostaria de saber se alguem pode me ajudar:
    eu preciso da seguinte formula

    quero somar um valor de uma outra celula caso ela esteja negativo:

    tipo se A1 360 subtrair b25

  26. wagner

    Responder

    boa noite gostaria de saber se alguem pode me ajudar:
    eu preciso da seguinte formula

    quero somar um valor de uma outra celula caso ela esteja negativo:

    tipo se A1 for menor que 0 somar b25 e se a1 for maior que 360 subtrair b25

  27. wagner

    Responder

    Alguem pode me ajudar:
    Gostaria de saber como fazer essa formula

    Se A12 FOR MAIOR QUE 360 QUERO SUBTRAIR B2 E SE A12 FOR MENOR QUE zERO(0) SOMAR MAIS B 3

    ALGUEM PODE ME AJUDAR, TO PRECISANDO URGENTE

  28. Rodrigo

    Responder

    Márcio,

    Esse comando que todos comentaram aqui só funciona para excel 2003, para versã oaciam de 2007, repita os comandos sem os sinais de menos , da seguinte forma: SOMARPRODUTO((A2:A5=”CASA”);(B2:B5=”ROSA”);(D2:D5))

  29. Leandro

    Responder

    preciso saber a seguinte fórmula:
    se b3 for menor que b2 quero q o valor de uma determinada célula apareça em vermelho,s e não azul.

  30. Responder

    Quanto a aparecer cores nas células pode usar a formatação condicional. Quanto a formula de somarproduto, no excel 2010 só funcionou usando o sinal * ao invés de ; conforme abaixo:
    =SOMARPRODUTO((A2:A5=”CASA”)*(B2:B5=”ROSA”)*(D2:D5))

  31. Fred

    Responder

    Boa noite pessoal, preciso de uma ajuda..
    como faço para somar um produto ex: Ft001 em varias planilhas diferentes, existe uma formula simples?

  32. Lucas

    Responder

    oi eu tentei reproduzir a planilha do somaproduto usando o seguinte código SOMARPRODUTO((A2:A5=”CASA”);(B2:B5=”ROSA”);(D2:D5)) porem n funciona eu tentei de tudo e n consigo poderia me enviar a planilha para poder analisa-la !

    obs.: uso office 2010

  33. CAROLINE NASCIMENTO

    Responder

    EU PRECISO DE AJUDA TENHO A SEGUINTE PLANILHA

    Aluno Escola Valor Pg
    Rafael CPMG R$ 174,00 X
    Bruna CPMG R$ 150,00 X
    Sharon CPMG R$ 160,00 X
    Nayara CPMG R$ 160,00 X

    PRECISO QUE ELA SOME A CADA VEZ QUE EU DIGITE O “X” IDENTIFICANDO QUE O CLIENTE PAGOU, E ASSIM VÁ SOMANDO E ME DE O VALOR TOTAL DE QUANTO RECEBI DE QUEM TEM O “X’

    ALGUÉM PODE ME AJUDAR PELOO AMOR DE DEUS, TO FICANDO DOIDA JA COM ESSA FORMULA

  34. Leandro

    Responder

    Boa tarde,

    Preciso realizar SOMARPRODUTO cruzando um intervalo que está numa linha e outro intervalo que está numa coluna com a mesma quantidade, porém está voltando erro.

    Alguma idéia?

  35. Bruno Teixeira

    Responder

    Gente esse somarproduto de casas rosa fica mais fácil no somases Ex =somases(D2:D5;A2:A5,”CASA”;B2:B5;”ROSA”)

  36. Bruno Teixeira

    Responder

    A minha dúvida é a mesma do Leandro que ninguém respondeu. Quero saber como somar produto de uma linha com colunas ex: =somarproduto(A1:A5;B1:F1), quem souber favor deixe um comentário..

Leave Comment

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *