Este tutorial explica a diferença entre as funções SOMASE e SOMASE em termos de sintaxe e uso e fornece uma série de exemplos de fórmulas para somar valores com vários critérios AND / OR no Excel 2019, 2016, 2013, 2010, 2007, 2003 e inferior.
Como todos sabem, o Microsoft Excel fornece uma matriz de funções para realizar vários cálculos com dados. Alguns artigos atrás, exploramos COUNTIF e COUNTIFS, que são projetados para contar células com base em uma única condição e em várias condições, respectivamente. Na semana passada, abordamos o SUMIF do Excel que adiciona valores que atendem aos critérios especificados. Agora é hora de revisar a versão plural de SOMASE – SOMASE do Excel que permite somar valores por vários critérios.
Aqueles que estão familiarizados com a função SOMASE podem pensar que convertê-la em SOMASE exige apenas um extra “S” e alguns critérios adicionais. Isso pareceria bastante lógico … mas “lógico” nem sempre é o caso ao lidar com a Microsoft:)
- Função SUMIF – sintaxe & uso
- função SUMIFS – sintaxe & uso
- Como usar SUMIFS no Excel (lógica E)
- Excel SUMIF com múltiplos critérios (lógica OR)
- Excel SUMIFS com múltiplas condições OR
- Usando Excel SUM em fórmulas de matriz
Função SUMIF do Excel – sintaxe & uso
A função SUMIF é usada para somar condicionalmente valores com base em um único critério. Discutimos a sintaxe SUMIF em detalhes no artigo anterior, então deixe-me dar um rápido resumo agora.
-
range– o intervalo de células a ser avaliado por seus critérios, obrigatório. -
criteria– a condição que deve ser atendida, obrigatório. -
sum_range– as células a serem somadas se a condição for atendida, opcional.
Como você vê, o A sintaxe da função SUMIF do Excel permite apenas uma condição. E ainda, dizemos que Excel SUMIF pode ser usado para somar valores com vários critérios. Como pode ser? Adicionando os resultados de várias funções SUMIF e usando fórmulas SUMIF com critérios de matriz, conforme demonstrado nos exemplos a seguir.
Função SUMIFS do Excel – sintaxe & uso
Use SOMASE no Excel para encontrar uma soma condicional de valores com base em vários critérios. A função SUMIFS foi introduzida no Excel 2007, para que você possa usá-la em todas as versões modernas do Excel 2019, 2016, 2013, 2010 e 2007.
Em comparação com SUMIFS, a sintaxe SUMIFS é um pouco mais complexa :
Os primeiros 3 argumentos são obrigatórios, os intervalos adicionais e seus critérios associados são opcionais.
-
sum_range– uma ou mais células para somar, obrigatório. Pode ser uma única célula, um intervalo de células ou um intervalo nomeado. Apenas células com números são somadas; os valores em branco e de texto são ignorados. -
criteria_range1– o primeiro intervalo a ser avaliado pelos critérios associados, obrigatório. -
criteria1– a primeira condição que deve ser atendida, obrigatória. Você pode fornecer os critérios na forma de um número, expressão lógica, referência de célula, texto ou outra função do Excel. Por exemplo, você pode usar critérios como 10, “> = 10”, A1, “cerejas” ou TODAY (). -
criteria_range2, criteria2, …– esses são intervalos e critérios adicionais associados a eles, opcionais. Você pode usar até 127 pares de intervalo / critério nas fórmulas SUMIFS.
E agora, vamos ” s dê uma olhada na fórmula SUMIFS do Excel com duas condições. Suponha que você tenha uma tabela listando as remessas de frutas de diferentes fornecedores. Você tem os nomes das frutas na coluna A, os nomes dos fornecedores na coluna B e a quantidade na coluna C. O que você deseja é descobrir a soma dos valores relativos a uma determinada fruta e fornecedor, por exemplo todas as maçãs fornecidas por Pete.
Quando você está aprendendo algo novo, é sempre uma boa ideia começar com coisas simples. Portanto, para começar, vamos definir todos os argumentos para nossa fórmula SOMASE:
- intervalo_soma – C2: C9
- intervalo_de_criterios1 – A2: A9
- critérios1 – “maçãs”
- intervalo_de_critérios2 – B2: B9
- critérios2 – “Pete”
Agora reúna os parâmetros acima e você obterá a seguinte fórmula SOMASE:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Para refinar além da fórmula, você pode substituir os critérios de texto “maçãs” e “Pete” por referências de células.Nesse caso, você não precisará alterar a fórmula para calcular a quantidade de outra fruta de um fornecedor diferente:
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
Usando SOMASE e SOMASE no Excel – coisas a lembrar
Uma vez que o objetivo deste tutorial é cobrir todas as maneiras possíveis de somar valores por várias condições, discutiremos exemplos de fórmulas com ambas as funções – SOMASE e SOMASE do Excel com vários critérios. Para usá-los corretamente, você precisa entender claramente o que essas duas funções têm em comum e de que forma são diferentes.
Embora a parte comum seja clara – destino e parâmetros semelhantes – as diferenças não são tão óbvios, embora muito essenciais.
1. A ordem dos argumentos
Nas funções SOMASE e SOMASE do Excel, a ordem dos argumentos é diferente. Em particular, intervalo_soma é o primeiro parâmetro em SUMIFS, mas é o terceiro em SUMIF.
À primeira vista, pode parecer que a Microsoft complica deliberadamente a curva de aprendizado de seus usuários. er, ao olhar mais de perto, você verá o raciocínio por trás disso. A questão é que intervalo_soma é opcional em SUMIF. Se você omiti-lo, não há problema, sua fórmula SOMASE somará os valores no intervalo (primeiro parâmetro).
Em SOMASE, intervalo_soma é muito importante e obrigatório, e é por isso que vem primeiro. Provavelmente os caras da Microsoft pensaram que depois de adicionar o 10º ou 100º par de intervalo / critério, alguém poderia esquecer de especificar o intervalo a somar:)
2. O intervalo de soma e o intervalo de critérios devem ter o mesmo tamanho
Na função SUMIF, o argumento intervalo_soma não precisa necessariamente ser do mesmo tamanho que o argumento de intervalo, desde que você tenha a célula superior esquerda à direita. No Excel SUMIFS, cada intervalo_critérios deve conter o mesmo número de linhas e colunas que o parâmetro intervalo_soma.
Por exemplo, =SUMIF(A2:A9,F1,C2:C18) retornará o resultado correto porque o Excel considera apenas a célula superior mais à esquerda no argumento intervalo_soma (C2 neste exemplo, que está correto) e inclui tantas colunas e linhas quantas as contidas no argumento intervalo.
Tudo bem, estratégia suficiente (ou seja, teoria) , vamos entrar nas táticas (por exemplo, exemplos de fórmula:)
Como usar SOMASE no Excel – exemplos de fórmula
Um momento atrás, discutimos uma fórmula SOMASE simples com dois textos critérios. Da mesma maneira, você pode usar SOMASE do Excel com vários critérios expressos por números, datas, expressões lógicas e outras funções do Excel.
Exemplo 1. SOMASE do Excel com operadores de comparação
Em nossa tabela de fornecedores de frutas, suponha que você deseja somar todas as entregas de Mike com Qtd. 200 ou mais. Para fazer isso, você usa o operador de comparação “maior que o r igual a “(> =) no critério 2 e obtém a seguinte fórmula SOMASE:
=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")
Cobrimos todos os operadores de comparação possíveis em detalhes ao discutir a função SOMASE do Excel, os mesmos operadores pode ser usado em critérios SUMIFS. Por exemplo, a seguinte fórmula com retorna a soma de todos os valores nas células C2: C9 que são maiores ou iguais a 200 e menores ou iguais a 300.
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
Exemplo 2. Usando SOMASE do Excel com datas
Caso queira somar valores com vários critérios com base na data atual, use a função TODAY () em seu SOMASE critérios, conforme demonstrado abaixo. A fórmula a seguir soma os valores na coluna D se uma data correspondente na coluna C cair nos últimos 7 dias, incluindo hoje:
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
De maneira semelhante, você pode usar a função SOMASE do Excel para somar valores em um determinado intervalo de datas. Por exemplo, a seguinte fórmula SOMASE adiciona os valores nas células C2: C9 se uma data na coluna B estiver entre 1 de outubro de 2014 e 31 de outubro de 2014, inclusive.
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
O mesmo resultado pode ser obtido calculando a diferença de duas funções SOMASE, conforme demonstrado neste exemplo – Como usar SOMASE para somar valores em um determinado intervalo de datas. No entanto, SUMIFS do Excel é muito mais fácil e compreensível, não é?
Exemplo 3. SUMIFS do Excel com células em branco e não em branco
Ao analisar relatórios e outros dados, você pode muitas vezes precisar somar valores correspondentes a células vazias ou não vazias.
E agora, vamos ver como você pode usar uma fórmula SOMASE com critérios “em branco” e “não em branco” em real dados.
Suponha que você tenha uma data de pedido na coluna B, data de entrega na coluna C e Qty. na coluna D. Como você encontra o total de produtos que ainda não foram entregues? Ou seja, você deseja saber a soma dos valores correspondentes às células não vazias da coluna B e às células vazias da coluna C.
A solução é usar a fórmula SUMIFS com 2 critérios:
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
Usando SOMASE do Excel com vários critérios OR
Conforme observado no início deste tutorial, a função SOMASE é projetada com lógica AND. Mas e se você precisar somar valores com vários critérios OR, ou seja, quando pelo menos uma das condições for atendida?
Exemplo 1. SOMASE + SOMASE
A solução mais simples é somar os resultados retornados por várias funções SUMIF. Por exemplo, a fórmula a seguir demonstra como encontrar o total de produtos entregues por Mike e John:
=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
Como você pode ver, a primeira função SOMASE adiciona as quantidades correspondentes a “Mike”, a outra função SOMASE retorna os valores relativos a “João” e, em seguida, você adiciona esses 2 números.
Exemplo 2. SUM & SUMIF com um argumento de matriz
A solução acima é muito simples e pode fazer o trabalho rapidamente quando houver apenas alguns critérios. Mas uma fórmula SUMIF + SUMIF pode crescer enormemente se você quiser somar valores com várias condições OR. Nesse caso, uma abordagem melhor é usar um argumento de critério de matriz na função SUMIF. Vamos examinar essa abordagem agora.
Você pode começar listando todas as suas condições separadas por vírgulas e, em seguida, incluir a lista separada por vírgulas resultante entre {chaves}, que é tecnicamente chamada de matriz.
O argumento de matriz que consiste em 3 valores força sua fórmula SOMASE a retornar três resultados separados, mas como escrevemos a fórmula em uma única célula, ela retornaria apenas o primeiro resultado – ou seja, o total de produtos entregues por John . Para fazer com que essa abordagem de critérios de matriz funcione, você precisa usar mais um pequeno truque – incluir sua fórmula SUMIF em uma função SUM, como esta:
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
Como você pode ver, um critério de matriz torna a fórmula muito mais compacta em comparação com SOMASE + SOMASE e permite que você adicione tantos valores quanto que você gosta na matriz.
Esta abordagem funciona com números e também com valores de texto. Por exemplo, se em vez dos nomes dos fornecedores na coluna C, você tinha IDs de fornecedor como 1, 2, 3 etc. ., então sua fórmula SUMIF seria semelhante a esta:
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
Ao contrário dos valores de texto, os números não precisam ser colocados entre aspas duplas argumentos de matriz.
Exemplo 3. SUMPRODUCT & SUMIF
No caso, sua forma preferida é listar os critérios em algumas células, em vez de especificá-los diretamente na fórmula, você pode usar SUMIF em conjunto com a função SUMPRODUCT, que multiplica os componentes nas matrizes fornecidas e retorna a soma desses produtos.
=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))
Onde G2: G4 são as células que contêm seus critérios, os nomes dos fornecedores em nosso caso, conforme ilustrado na imagem abaixo.
Mas é claro, nada o impede de listar os valores em um critério de matriz de sua função SOMASE se desejar:
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
O resultado retornado por ambas as fórmulas será idêntico ao que você vê na captura de tela:
Excel SUMIFS com critérios OR múltiplos
Se desejar somar valores condicionalmente no Excel não simplesmente com várias condições OR, mas com vários conjuntos de condições, você terá que usar SOMASE em vez de SOMASE. As fórmulas serão muito semelhantes ao que acabamos de discutir.
Como de costume, um exemplo pode ajudar a ilustrar melhor o ponto. Em nossa tabela de fornecedores de frutas, vamos adicionar a Data de Entrega (coluna E) e encontre a quantidade total entregue por Mike, John e Pete em outubro.
Exemplo 1. SUMIFS + SUMIFS
A fórmula produzida por esta abordagem inclui muitas repetições e aparências complicado, mas é fácil de entender e, o mais importante, funciona:)
Exemplo 2. SUM & SUMIFS com um argumento de matriz
Eu tentei explicar a essência dessa abordagem no exemplo SOMASE, então agora podemos simplesmente copiar essa fórmula, alterar a ordem dos argumentos (como você deve se lembrar, é diferente em SOMASE e SOMASE) e adicionar critérios adicionais. a fórmula resultante é mais compacta do que SUMIFS + SUMIFS:
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
O resultado retornado por esta fórmula é exatamente o mesmo que você vê em a imagem acima.
Exemplo e 3. SUMPRODUCT & SUMIFS
Como você se lembra, a abordagem SUMPRODUCT difere das duas anteriores na maneira como você insere cada um dos seus critérios em uma célula separada em vez de especificá-los diretamente na fórmula. No caso de vários conjuntos de critérios, a função SUMPRODUCT não será suficiente e você terá que empregar ISNUMBER e MATCH também.
Portanto, supondo que os nomes dos suprimentos estejam nas células H1: H3, a data de início está na célula H4 e a data final na célula H5, nossa fórmula SUMPRODUCT assume a seguinte forma:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
Muitas pessoas se perguntam por que usar traço duplo (-) em fórmulas SUMPRODUCT. A questão é que o Excel SUMPRODUCT ignora tudo, exceto os valores numéricos, enquanto os operadores de comparação em nossa fórmula retornam valores booleanos (VERDADEIRO / FALSO), que não são numéricos. Para converter esses valores booleanos em 1 “se 0” s, você usa o sinal de menos duplo, que é tecnicamente chamado de operador unário duplo. O primeiro unário coage TRUE / FALSE para -1/0, respectivamente. O segundo unário nega os valores, ou seja, inverte o sinal, transformando-os em +1 e 0, que a função SUMPRODUCT pode compreender.
Espero que a explicação acima faça sentido. E mesmo que isso não aconteça, lembre-se desta regra: use o operador unário duplo (-) quando estiver usando operadores de comparação em suas fórmulas SUMPRODUCT.
Usando Excel SUM em fórmulas de matriz
Como você se lembra, a Microsoft implementou a função SUMIFS no Excel 2007. Se alguém ainda usa o Excel 2003, 2000 ou anterior, você terá que usar uma fórmula de matriz SUM para adicionar valores com vários critérios E. Naturalmente, esta abordagem funciona em versões modernas do Excel 2013 – 2007 também, e pode ser considerado uma contraparte antiquada da função SOMASE.
Nas fórmulas SOMASE discutidas acima, você já usou argumentos de matriz, mas uma fórmula de matriz é algo diferente.
Exemplo 1. Soma com vários critérios AND no Excel 2003 e anteriores
Vamos voltar ao primeiro exemplo em que descobrimos uma soma de valores relacionados a uma determinada fruta e fornecedor:
Como você já sabe, essa tarefa é facilmente realizada usando um fórmula SUMIFS comum:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
E agora, vamos ver como a mesma tarefa pode ser realizada nas primeiras versões “livres de SUMIFS” do Excel . Primeiro, você anota todas as condições que devem ser atendidas na forma de intervalo = “condição”. Neste exemplo, temos dois pares de intervalo / condição:
Condição 1: A2: A9 = “maçãs”
Condição 2: B2: B9 = “Pete”
Em seguida, você escreve uma fórmula SUM que “multiplica” todos os seus pares de intervalo / condição, cada um colocado entre colchetes. O último multiplicador é o intervalo de soma, C2: C9 em nosso caso:
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
Conforme ilustrado na imagem abaixo, a fórmula funciona perfeitamente na versão mais recente do Excel 2013.
Exemplo 2. Fórmulas de matriz SUM em versões modernas do Excel
Uniforme nas versões modernas do Excel, o poder da função SUM não deve ser subestimado. A fórmula da matriz SUM não é simplesmente uma ginástica da mente, mas tem um valor prático, conforme demonstrado no exemplo a seguir.
Suponha que , você tem duas colunas, B e C, e você precisa contar quantas vezes a coluna C é maior do que a coluna B, quando um valor na coluna C é maior ou igual a 10. Uma solução imediata que vem à mente é usar o SUM fórmula de matriz:
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
Não vejo nada prático aplicação da fórmula acima? Pense nisso de outra forma:)
Suponha que você tenha a lista de pedidos conforme mostrado na imagem abaixo e deseja saber quantos produtos não foram entregues por completo em uma determinada data. Traduzido para a linguagem do Excel, temos as seguintes condições:
Condição 1: Um valor na coluna B (itens pedidos) é maior que 0
Condição 2: Um valor na coluna C (entregue) em menos do que na coluna B
Condição 3: uma data na coluna D (data de vencimento) é menor que 01/11/2014.
Colocando os três intervalos / pares de condições, você obtém a seguinte fórmula:
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
Bem, os exemplos de fórmulas discutidos neste tutorial apenas arranharam a superfície do que as funções SUMIFS e SUMIF do Excel podem realmente fazer. Mas, felizmente, eles ajudaram a apontar a direção certa e agora você pode somar valores em suas pastas de trabalho do Excel, não importa quantas condições intrincadas você deve considerar.
- Como usar SOMASE no Excel – exemplos de fórmula para somar células condicionalmente
- Como usar SOMASE e SOMASE do Excel com vários critérios – exemplos de fórmula
- Como usar PROCV & funções SOMA ou SOMASE no Excel
- SOMASE no Planilhas Google com exemplos de fórmula
- CONT.SE no Excel – conte se não em branco, maior que, duplicado ou único
- Como contar e somar células por cor no Excel