Somar no Excel? Conheça as Funções de SOMA do Excel

Como somar no Excel? Conheça as funções de Excel para SOMAR

Quer aprender definitivamente como somar no excel? Neste artigo são demonstradas muitas das funções de somas do Excel e como utilizá-las em 20 situações de todos os tipos de somas.

Clique nos links abaixo para ir diretamente nos exemplos, e assista também os 4 vídeos desta série seguindo os vídeos acima.

1. Soma básico
2. Soma tabela
3. Soma com auto incremento
4. Soma com desloc
5. Soma com auto ajuste
6. Soma entre planilhas
7. Somar células visíveis
8. Somar com um critério
9. Som com vários critérios
10. Somar com operadores e coringas
11. Somar dias
12. Somar mês
13. Somar horas
14. Somar valor hora
15. Soma condicional OU
16. Soma matricial
17. Somar TOP X
18. SomarProduto
19. Subtotal automático
20. Somar agregar

SOMA BÁSICO

A função SOMA do Excel tem por finalidade realizar a soma de um intervalo de dados.

Esta função pode ser aplicada de várias formas:

• Digitando a função
  • Digite =SOMA( e selecione o intervalo de dados e feche com ), deve ficar como =SOMA(C10:C20)
• Utilizando um procedimento do Excel
  • Selecione o intervalo que deseja somar e clique na guia Fórmulas sobre o botão AutoSoma e selecione a opção SOMA.
• Utilizando um atalho
  • Você pode também utilizar um atalho para realizar a soma automática, para tanto basta selecionar o intervalo que deseja somar e pressionar ALT+=, esta é a forma mais prática de realizar esta soma.

 SOMA TABELA

Quando uma lista está formatada como tabela ela se comporta de forma diferente que uma lista normal de dados no Excel.

Uma das mudanças é a forma como a soma é criada de forma automática, veja:

• Digitando a função SOMA na coluna
  • Ao digitar a função na coluna você terá uma função semelhante á =SOMA([Jan]) criada. Esta função está se relacionando ao nome da coluna, sendo no casso desta coluna Jan.
  • Ao copiar esta fórmula para as demais colunas a fórmula é mantida.
  • Então, para copiar você pode arrastar esta função pela alça e assim ela criará a fórmula relativamente para cada uma das colunas.
• Digitando a função SOMA na linha
  • Ao criar a soma na linha horizontal o sistema já cria a mesma fórmula automaticamente para todas as linhas, pois é uma das funções da tabela ter a mesma fórmula para todas as linhas.
• Criando uma função de subtotal na tabela
  • Selecionando a última linha logo abaixo da tabela e pressionando ALT+= você cria automaticamente a função SUBTOTAL para todas as colunas com a SOMA.
  • Esta função poderá ser trocada facilmente para outras como CONT.NÚM e MÉDIA.
  • A vantagem é que ao filtrar os dados, esta soma é atualizada somando apenas os valores que estiverem visívewis na tabela.
  • Para a coluna usando o ALT+= É criada a função de SOMA normalmente.

Somar com auto incremento

Para realizar uma soma adicionando o valor da linha atual aos anteriores você deve travar a célula inicial e digitar a célula seguinte como variável e arrastar para as demais.

• Somar uma lista de valores incluindo a linha atual á soma
  • Crie uma lista na coluna A com valores aleatórios nas células de A1 á A12
  • Na célula B1 digite a fórmula: =SOMA($B$1:B1)

    

Soma com desloc

Para somar intervalos diferentes mudando apenas uma combo de valores você pode utilizar a fórmula seguinte que utiliza SOMA, DESLOC e CORRESP.

• Mudar o intervalo de soma com combo
  • Temos no nosso exemplo a lista abaixo, queremos somar a coluna de mês mudando somente o mês e alterando a coluna de soma. Considere o início dos dados em B9.

    

  • Em uma célula criamos uma pequena tabela com os campos Mês, Total e duas células ao lado para o preenchimento.

    

  • Clique na célula ao lado do mês e clique em guia Dados->Validação de dados e selecione a lista os meses do cabeçalho da tabela.

    

  • Digite a seguinte fórmula no campo ao lado de Total:
    • =SOMA(DESLOC($C$10:$C$20;0;CORRESP($Q$9;$C$9:$N$9;0)-1;;))
  • A função SOMA está somando o endereço alterado pela fórmula DESLOC.
  • A fórmula DESLOC está alterando o intervalo inicial de C10 á C20 utilizando para isso a função CORRESP no parâmetro relativo ao deslocamento de colunas e o -1 está reduzindo em um a coluna, pois a primeira coluna também é contada no campo CORRESP.
  • O Corresp realiza o deslocamento conforme o valor que estiver na célula Q9 aonde os meses são alterados e busca a sua posição na lista de C9 á N9.

    

    
    

SOMAR AUTO AJUSTE

Para a situação em que você precisa que a fórmula se autoajuste para incluir as novas linhas que foram digitadas. Para realizar esta tarefa veja a função abaixo com DESLOC:

• Somar uma lista com auto ajuste das linhas que estão sendo somadas
  • Digite a fórmula  =SOMA(DESLOC($C$10;;;CONT.VALORES($C$10:$C$1048576)))
    • O Desloc está referenciando apenas a célula C10 e é aumentada a altura desta referência utilizando a função Cont.valores que está referenciando o intervalo de C10 á C1048576, ou seja, a cada valor incluso na lista a altura da referência é aumentada automaticamente.

SOMA ENTRE PLANILHAS

É possível somar entre planilhas indicando um intervalo de planilhas. Isto facilita muito a soma, pois ao invés de selecionar em todas as planilhas você consegue fazer rapidamente esta soma e ainda incluindo uma nova planilha ela já estará dentro da soma se estiver entre estas planilhas.

• Somar o mesmo intervalo de células em uma sequência de planilhas
  • Selecione a célula aonde você digitará a soma, digite =SOMA( e selecione a primeira planilha.
  • A sua função ficará como: =SOMA(‘Soma básico’!C10, então digite na barra de fórmulas :, logo após a fórmula, ficando assim: =SOMA(‘Soma básico’!C10:
  • Selecione a última planilha do intervalo que deseja somar. Sua fórmula deve ficar: =SOMA(‘Soma básico’!C10:’Soma com desloc’!C10
  • Feche os parênteses e pressione ENTER, veja que a fórmula retorna um erro.
  • Altere a fórmula de =SOMA(‘Soma básico’!C10:’Soma com desloc’!C10) para =SOMA(‘Soma básico:Soma com desloc’!C10), veja que automaticamente o intervalo é somado.

SOMAR CÉLULAS VISÍVEIS – SUBTOTAL

O Excel permite que você some células visíveis de forma simples e automática.

• Somar somente células visíveis filtradas
  • Apontando para uma tabela com auto filtro aplique a função =SUBTOTAL(109;C12:C22).
  • Veja que o 109 que está na função é uma função SOMA, e permite também outras variáveis como a contagem de valores, média, mínimo, máximo, etc.
  • Na medida que os valores são filtrados o valor é automaticamente atualizado.

SOMAR COM UM CRITÉRIO – SOMASE

Para realizar uma soma utilizando critérios, como por exemplo, somar somente os valores do presidente Ernesto Geisel, conforme a lista abaixo utilizamos a função SOMASE.

• Somar somente os valores conforme uma condição
  • No exemplo temos uma lista com os presidentes e os valores e estamos somando os valores conforme o nome de cada um deles.
  • Utilize a função SOMASE com os parâmetros =SOMASE(intervalo de critérios;critério;intervalo de soma)
  • No caso do nosso exemplo utilizamos a função desta forma: =SOMASE(B:B;F10;C:C), sendo o intervalo B:B a lista de presidentes, a célula F10 o nome do presidente na lista de resumo e C:C a lista de valores a serem somados.

SOMAR COM VÁRIOS CRITÉRIOS – SOMASES

Da mesma forma que a função SOMASE a função SOMASES permite a soma com critério, mas neste caso com diversos critérios, como por exemplo somar os valores por presidente conforme a região.

• Somar valores com várias condições
  • Para realizar a soma com várias condições no Excel você pode utilizar a função SOMASES.
  • A sintaxe desta função é =SOMASES(Intervalo de soma, Intervalo de critérios, Critério;Intervalo de critérios 2; Critério 2;…)
  • No exemplo temos a função =SOMASES($D:$D;$B:$B;$F10;$C:$C;H$9) aonde o intervalo D:D é o intervalo de Valor, o B:B é o intervalo de critérios do nome do presidente, F10 é o nome do presidente que será filtrado, e o outro valor filtrado é C:C é a região e a célula H9 é o filtro de região.

SOMA COM CRITÉRIOS E CORINGAS

As funções para somar condicional, como outras do Excel, permitem que sejam utilizados coringas e operadores. Por exemplo, somar os valores aonde o presidente comece com L, ou somar os valores com o valor maior que 100, maior ou igual á 200, diferente de 500, etc.

• Somar com critérios utilizando critérios e coringas
  • Os coringas e operadores do sistema são os seguintes:
    • “L*” -> O * permite que sejam somados os valores aonde os mesmos comecem ou contenham um determinado caracter ou conjunto de caracteres apenas mudando o * , por exemplo *L, comece com qualquer letra e termine com L ou *LU*  que contenha em qualquer lugar da cadeia de caracteres as letras LU.
    • “>309” -> Que contenha um valor maior que 309, lembrando de sempre colocar entre aspas duplas os valores.
    • “<309” -> Que o valor seja menor do que 309.
    • “>=309” -> Que o valor seja maior ou igual a 309.
    • “<=309” -> Que o valor seja menor ou igual a 309.
    • “<>309” -> Que o valor seja diferente de 309, podendo ser usado também uma cadeia de caracteres como por exemplo “<> Michel Temer”
    • “?M*” -> O coringa ? determina que haja uma quantidade x de caracteres antes, sendo que cada ? significa que deve haver um caracter, por exemplo ?M* significa que no texto tenha uma letra qualquer antes do M e quaisquer quantidade de caracteres após o M, se fossem JOS?, seria qualquer cadeia de caracteres que comece com JOS e termine com qualquer letra, mas somente uma.

SOMAR DIAS

As operações para somar dias no Excel são bastante simples, precisam apenas de algumas configurações e entendimentos.

As datas no Excel são entendidas como números pelo programa. Sendo que o número 0 é entendido como a data 00/01/1900, o 1 como 01/01/1900 e o valor 42.837 como o dia 12/04/2017.

Veja abaixo como realizar as somas de datas no Excel.

• Data + dias no Excel
  • Selecione a data e utilize o + para digiar a quantidade de dias ou selecionar uma célula com esta quantidade: = C10+E10, sendo C10=30/10/1969 e E10=10 o resultado é 09/11/1969
• Dias entre datas
  • Para calcular a quantidade de dias entre duas datas basta usar o operador -.
    • Digite a função =D10-C10, sendo D10 a maior data e C10 a menor data. Altere o formato da célula de resultado para numérico, veja que ele conta a quantidade de dias entre as duas datas.
• Dias úteis
  • Para calcular os dias úteis entre duas datas use a função DIATRABALHOTOTAL.
  • Esta função permite que sejam somados somente os dias úteis entre duas datas, e ainda há o parâmetro que permite que sejam excluídas datas de feriados por exemplo á partir de uma data.

SOMAR MÊS

Uma das formas para somar um intervalo de um mês é a utilização da função SOMASES em conjunto com os coringas e a função FIMMÊS.

A função SOMASES, como já vimos permite que sejam incluídas diversas condições para somar, neste caso queremos somar os valores em que a lista de critérios estejam entre a data inicial e final de um mês.

Para identificar a data final do mês usamos a função FIMMÊS, que tem como critério uma data, e a quantidade de meses á partir desta data que queremos a data final. No nosso caso 0, pois queremos que retorne a data final do mês corrente.

• Somar os valores do mês no Excel
  • Para somar valores de um mês você pode usar a seguinte função =SOMASES(C:C;B:B;”>=”&E10;B:B;”<=”&FIMMÊS(E10;0)). Sendo:
    • C:C : Intervalor de valores;
    • B:B : Intervalo de critérios;
    • “>=”&E10 : Sendo E10 a data inicial, no caso 01/10/2017;
    • “<=”&FIMMÊS(E10;0) : A função FIMMÊS retorna a data 31/10/2017 pegando a célula E10 como referência que o valor é 01/10/2017.

SOMAR HORAS

A soma de horas no Excel segue a mesma lógica das operações com datas podendo usar operadores matemáticos diretamente nestes cálculos.

O Excel identifica os horários da mesma forma que as datas, ela é entendida como uma fração dos valores, por exemplo: 31/03/2017 06:00 é entendido como 42.825,25 pelo Excel. Esta parte decimal ,25 são as horas do dia, no caso de 6/24 = 0,25.

• Somar horários
  • Para operações envolvendo horários o operador utilizado é o – para identificar a quantidade de horas entre duas datas.
  • É necessário formatar a célula que recebe este horário com o formato de Hora: 37:30:55, que fará uma soma corrida do valor, ou seja, entenderá que devem ser mostradas as horas entre o intervalo e não a próxima data considerando este valor.

SOMAR VALOR HORA

Conforme visto, as datas e horas no Excel são entendidos como números.

Isto permite que sejam realizadas outras operações envolvendo números, como aumentar a quantidade de dias entre duas datas, contar a quantidade de horas de um intervalo entre outras, como por exemplo somar o valor hora que um funcionário deve receber.

• Somar o valor hora
  • Para somar o valor hora que um funcionário deve receber identifique o total de horas trabalhadas conforme visto no tópico anterior.
  • Multiplique esta célula pelo valor que o funcionário recebe por hora e multiplique novamente por 24, pois a quantidade de horas 24:00:00 para o Excel é 1 e 48:00:00 é 2, por isso multiplicando por 24 você tem o valor correto para a multiplicação dos valores.

SOMA CONDICIONAL OU – SOMASE SOMASES

Por padrão as funções SOMASE e SOMASES não possuem a condicional OU. Ou seja, não é possível por padrão somar utilizando estas funções se uma ou outra condição forem atendidas.

Para resolver este problema utilizamos neste exemplo as funções SOMA e SOMASES aplicadas em uma fórmula MATRICIAL.

• Soma condicional com a cláusula OU
  • No nosso exemplo temos uma lista aonde gostaríamos de somar os valores se estiverem em um conjunto de regiões e se for um determinado produto;
  • Para a resolução deste problema utilizamos a fórmula
    • {=SOMA(SOMASES(D:D;B:B;F10:G10;C:C;H10))}
    • Na fórmula SOMASES temos:
      • D:D : Intervalo a ser somado;
      • B:B : Intervalo com os nomes das regiões;
      • F10:G10 : Intervalo com as condicionais 1 e 2, conforme o gif abaixo;
      • C:C : Coluna dos produtos;
      • H10 : Célula com o produto que deseja somar
    • A função SOMA é aplicada sobre toda a função SOMASES e ao aplicar a fórmula ao invés de pressionar ENTER é pressionado Ctrl+Shift+Enter, tornando assim a fórmula matricial. Visualmente você verá colchetes {} na fórmula;
    • Uma função matricial permite que os valores sejam tratados como listas, comparando um a um os valores de uma lista com a outra neste caso;
    • No caso a fórmula está comparando os valores da lista B:B com os valores das células F10:G10.

SOMA MATRICIAL – SOMA+SE

Em versões abaixo da 2007 no Excel não existia a função SOMASES, exigindo desta forma o uso de funções matriciais para resolver este problema.

Neste exemplo você verá como é realizada a soma com condições sem o uso da função SOMASES.

• Somar matricial com funções SOMA e SE
  • No nosso exemplo iremos somar os valores de uma região e produto;
  • Para isto utilizamos a fórmula:
    • {=SOMA(SE($B$10:$B$218=F10;SE($C$10:$C$218=F11;$D$10:$D$218)))}
    • Na fórmula estamos utilizando apenas as funções SOMA e SE de modo matricial;
    • Entendendo a fórmula:
      • SOMA : Esta função irá realizar a soma do resultado dos SEs na equação
      • SE($B$10:$B$218=F10; : O sistema verifica se os valores das células de B10:B218 são iguais á F10 que é a região, este SE só funciona porque é matricial;
      • SE($C$10:$C$218=F11;$D$10:$D$218) : Este SE é aplicado apenas se o primeiro for verdadeiro, sendo a região a mesma selecionada, o sistema verifica se a coluna C possui o produto selecionado, e se possuir ele retorna o valor da mesma linha no intervalo de D10:D218;
    • Feito isso são fechados os parênteses e pressionado Ctrl+Shift+Enter para que o sistema entenda que esta fórmula é matricial;
    • Se pressionarmos F9 somente nas células de SE teremos o seguinte resultado 

      

    • Perceba que na fórmula temos os valores apenas aonde a lista atende aos parâmetros e nos demais é retornado FALSO. Assim temos uma matriz com os valores que atendem as situações das funções SE e a função SOMA realiza esta soma.

SOMAR TOP – SOMA MAIOR

Neste exemplo veremos como realizar uma soma dos primeiros X números, utilizando as funções SOMA, MAIOR, INDIRETO e matriz.

Nesta soma demonstramos como fazer esta fórmula de 3 formas diferentes, com um sequencial fixo, um sequencial maior com a função INDIRETO e uma função variável que permite que sejam digitadas as quantidades que desejam ser exibidas.

• Somar TOP x valores maiores
  • Para realizar a soma dos 3 maiores valores utilizamos a função:
    • =SOMA(MAIOR($D$10:$D$218;{1;2;3})), aonde:
      • MAIOR($D$10:$D$218;{1;2;3}) : a função MAIOR retorna o maior valor de uma função conforme a sua posição. Se digitar MAIOR(D10:D218;1) teremos o primeiro maior valor, se digitarmos MAIOR(D10:D218;2) o segundo maior valor, e assim por diante
      • Desta forma nesta função utilizamos {1;2;3} ao invés do número da posição. E assim selecionando esta função e pressionando F9 temos como restulado {995;988;987} trazendo assim os 3 maiores valores em uma só função.
      • A fórmula SOMA é utilizada somando então esta matriz e retorna o valor de 2970.
  • Para realizar a soma dos 10 primeiros valores é utilizada a função matricial
    • {=SOMA(MAIOR($D$10:$D$218;LIN(INDIRETO(“1:10”))))}
    • Perceba que é utilizada no lugar de {1;2;3} as funções LIN(INDIRETO(“1:10”))
    • Em conjunto estas funções retornam os números de linhas de um intervalo: {1;2;3;4;5;6;7;8;9;10}, mas nestes caso a função não entende eles como linhas e sim como uma matriz da mesma forma que {1;2;3}, mas neste caso sem a necessidade de digitar os valores.
    • E no final também ao fechar a função pressionamos Ctrl+Shift+Enter para que a mesma seja entendida como matricial.
  • Para realizar a soma de um intervalo variável de células utilizamos a seguinte função matricial:
    • {=SOMA(MAIOR($D$10:$D$218;LIN(INDIRETO(“1:”&F12))))}
    • Nesta função perceba que utilizamos uma concatenação de valores em “1:”&F12, sendo F12 a célula na qual digitamos os valores que são alterados;
    • Veja no exemplo abaixo como a soma é alterada na medida em que os TOP x são alterados.

SOMARPRODUTO

A função SOMARPRODUTO realiza a soma de uma multiplicação entre listas de valores.

Estas listas de valores tem que ter a mesma quantidade de itens e é uma função com uma enorme utilidade dentro do Excel e é muito explorada em funções avançadas.

No exemplo que criamos queremos saber o valor do estoque de um determinado produto. Temos então uma lista com o nome do produto, uma coluna com o valor unitário deste produto e uma coluna com a quantidade.

A ideia é não criar uma coluna auxiliar para esta lista, mas fazer uma só fórmula que ao alterar o produto já some o valor da multiplicação entre as colunas Valor e Qtde deste.

• SOMARPRODUTO
  • Para realizar a soma da multiplicação do valor com a quantidade conforme o produto utilizamos a seguinte função:
    • =SOMARPRODUTO(–($B$10:$B$81=$G$9);$C$10:$C$81;$D$10:$D$81)
      • –($B$10:$B$81=$G$9) : Neste campo temos um SE, isso mesmo, um SE. No caso a função SOMARPRODUTO utiliza — para converter o resultado de uma verificação em 1 e 0, e neste caso estão sendo verificados se os valores da coluna B são semelhantes ao valor da lista em G9, sendo o resultado {0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0}, ou seja, ele retorna 1 apenas quando atende aquela validação;
      • $C$10:$C$81 : Temos aqui a coluna de valor que será multiplicada pela primeira coluna de validação;
      • $D$10:$D$81 : Este intervalo é a quantidade de itens que temos deste produto, e temos a multiplicação então da coluna de produto 1, vezes a coluna de valor 263, vezes a coluna qtde 13, retornando a soma destes valores, inclusive se houverem vários produtos iguais na lista.

SUBTOTAL AUTOMÁTICO

O Excel permite que seja realizado um subtotal automaticamente com apenas um clique.

• SUBTOTAL AUTOMÁTICO
  • Para agrupar valores e criar um subtotal automático para cada grupo você deve realizar as seguintes tarefas;
  • 1. Classificar a sua lista conforme os valores que deseja agrupar;
  • 2. Selecionar a lista;
  • 3. Clicar em Dados->Subtotal e marcar o item a cada qual deseja agrupar e incluir a soma;
  • 4. Selecionar a função que deseja utilizar para o seu subtotal;
  • 5. Marque as opções, se deseja que o subtotal seja acima da lista ou abaixo, se deve quebrar páginas na impressão e se devem ser substituídos os valores atuais.
• Após estas etapas o Excel cria automaticamente uma lista agrupada e totalizada conforme a coluna selecionada.

SOMA AGREGAR

A função Agregar é bastante parecida com a função SUBTOTAL, permitindo outras opções, não somar subtotais, não somar ocultas, ignorar erros e itens agrupados.

• SOMA AGREGAR
  • Para realizar a soma você deve utilizar a soma com a função agregar e ignorar os subtotais, erros, somas e agregações utilizamos como exemplo uma lista com o subtotal automático
    • Digite a função =AGREGAR(9;3;D:D);
    • O primeiro parâmetro 9 é o parâmetro que define que a função realizará a soma;
    • O parâmetro 3 é o que define como esta função funcionará, no caso do 3 ela define que irá ignorar células ocultas, erros, subtotal, agregação e aninhadas.
    • Desta forma, veja o exemplo abaixo aonde o valor da soma é exatamente igual ao total geral da lista de subtotal automático e não dá erro apesar do erro forçado na célula.

Baixe a planilha

Abraço

Marcos Rieper

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: