Este tutorial fornece uma demonstração detalhada de como usar as funções SUMIF e SUMIFS no Planilhas Google com fórmulas e exemplos.
SUMIF é uma das funções matemáticas do Planilhas Google, usada para somar células condicionalmente. Basicamente, a função SOMASE procura uma condição específica em um intervalo de células e, em seguida, soma os valores que atendem à condição fornecida.
Por exemplo, você tem uma lista de despesas em planilhas do Google e deseja apenas somar as despesas que estão acima de um determinado valor máximo. Ou você tem uma lista de itens do pedido e seus valores correspondentes e deseja apenas saber o valor total do pedido de um item específico. É aí que a função SUMIF é útil.
O SUMIF pode ser usado para somar valores com base na condição de número, condição de texto, condição de data, curingas, bem como com base em células vazias e não vazias. O Planilhas Google tem duas funções para somar valores com base em critérios: SUMIF e SUMIFS. A função SUMIF soma números com base em uma condição, enquanto SUMIFS soma números com base em várias condições.
Neste tutorial, explicaremos como usar as funções SUMIF e SUMIFS no Planilhas Google para somar os números que atendem a determinadas condições.
Função SUMIF no Planilhas Google - Sintaxe e Argumentos
A função SOMASE é apenas uma combinação das funções SOMA e SE. A função IF examina o intervalo de células para uma determinada condição e, em seguida, a função SUM soma os números correspondentes às células que atendem à condição.
Sintaxe da função SUMIF:
A sintaxe da função SUMIF no Planilhas Google é a seguinte:
= SUMIF (intervalo, critérios, [intervalo_soma])Argumentos:
alcance - O intervalo de células onde procuramos as células que atendem aos critérios.
critério - Os critérios que determinam quais células precisam ser adicionadas. Você pode basear o critério no número, string de texto, data, referência de célula, expressão, operador lógico, caractere curinga, bem como outras funções.
intervalo_soma - Este argumento é opcional. É o intervalo de dados com valores a serem somados se a entrada do intervalo correspondente corresponder à condição. Se você não incluir este argumento, o ‘intervalo’ será somado.
Agora, vamos ver como usar a função SOMASE para somar valores com critérios diferentes.
Função SUMIF com critérios numéricos
Você pode somar números que atendem a certos critérios em um intervalo de células, usando um dos seguintes operadores de comparação para fazer os critérios.
- maior que (>)
- menos que (<)
- maior ou igual a (> =)
- menor ou igual a (<=)
- igual a (=)
- diferente de ()
Suponha que você tenha a planilha a seguir e esteja interessado nas vendas totais de 1.000 ou mais.
Veja como você pode entrar na função SUMIF:
Primeiro, selecione a célula onde deseja que o resultado da soma apareça (D3). Para somar os números em B2: B12 que são maiores ou iguais a 1000, digite esta fórmula e pressione ‘Enter’:
= SOMASE (B2: B12, "> = 1000", B2: B12)Nesta fórmula de exemplo, os argumentos intervalo e intervalo_soma (B2: B12) são iguais, porque os números de vendas e os critérios são aplicados no mesmo intervalo. E inserimos o número antes do operador de comparação e o colocamos entre aspas porque os critérios sempre devem ser colocados entre aspas duplas, exceto para uma referência de célula.
A fórmula procurava números maiores ou iguais a 1000 e então somava todos os valores correspondentes e mostrava o resultado na célula D3.
Como os argumentos intervalo e intervalo_soma são iguais, você pode obter o mesmo resultado sem os argumentos intervalo_soma na fórmula, como este:
= SOMASE (B2: B12, "> = 1000")
Ou você pode fornecer a referência de célula (D2) que contém o número em vez dos critérios de número e unir o operador de comparação com essa referência de célula no argumento de critérios:
= SOMASE (B2: B12, "> =" & D2)Como você pode ver, o operador de comparação ainda é inserido entre aspas duplas e o operador e a referência de célula são concatenados por um E comercial (&). E você não precisa colocar a referência de célula entre aspas.
Observação: Ao se referir à célula que contém os critérios, certifique-se de não deixar nenhum espaço à esquerda ou à direita no valor da célula. Se o seu valor tiver qualquer espaço desnecessário antes ou depois do valor na célula referida, a fórmula retornará '0' como resultado.
Você também pode usar outros operadores lógicos da mesma maneira para criar condições no argumento de critérios. Por exemplo, para somar valores inferiores a 500:
= SOMASE (B2: B12, "<500")
Soma se os números forem iguais a
Se quiser adicionar números iguais a um certo número, você pode inserir apenas o número ou inserir o número com o sinal de igual no argumento do critério.
Por exemplo, para somar os valores de vendas correspondentes (coluna B) para quantidades (coluna C) cujos valores são iguais a 20, tente qualquer uma destas fórmulas:
= SOMASE (C2: C12, "= 20", B2: B12)
= SOMASE (C2: C12, "20", B2: B12)
= SOMASE (C2: C12, E2, B2: B12)
Para somar números na coluna B com quantidade diferente de 20 na coluna C, tente esta fórmula:
= SOMASE (C2: C12, "20", B2: B12)
Função SUMIF com Critérios de Texto
Se você deseja somar números em um intervalo de células (coluna ou linha) correspondente às células que possuem um texto específico, você pode simplesmente incluir esse texto ou a célula que contém o texto no argumento de critérios de sua fórmula SOMASE. Observe que a sequência de texto deve sempre ser colocada entre aspas duplas (”“).
Por exemplo, se você deseja o valor total das vendas na região "Oeste", pode usar a fórmula abaixo:
= SOMASE (C2: C13, "Oeste", B2: B13)Nesta fórmula, a função SUMIF procura o valor ‘Oeste’ no intervalo de células C2: C13 e adiciona o valor de vendas correspondente na coluna B. Em seguida, exibe o resultado na célula E3.
Você também pode se referir à célula que contém o texto em vez de usar o texto no argumento de critérios:
= SOMASE (C2: C12, E2, B2: B12)
Agora, vamos obter a receita total de todas as regiões, exceto "Oeste". Para fazer isso, usaremos não igual ao operador () na fórmula:
= SOMASE (C2: C12, "" & E2, B2: B12)
SUMIF com WildCards
No método acima, a função SOMASE com critérios de texto verifica o intervalo em relação ao texto especificado exato. Em seguida, ele soma os números para o texto exato e ignora todos os outros números, incluindo a sequência de texto parcialmente correspondida. Para somar os números com strings de texto de correspondência parcial, você deve adaptar um dos seguintes caracteres curinga em seus critérios:
?(ponto de interrogação) é usado para corresponder a qualquer caractere único, em qualquer lugar na string de texto.*(asterisco) é usado para encontrar palavras correspondentes junto com qualquer sequência de caracteres.~(til) é usado para combinar textos com um ponto de interrogação (?) ou um asterisco (*).
Teremos esta planilha de exemplo para produtos e suas quantidades para somar números com curingas:
Asterisco (*) Wildcard
Por exemplo, se você deseja somar as quantidades de todos os produtos Apple, use esta fórmula:
= SOMASE (A2: A14, "Apple *", B2: B14)Esta fórmula SUMIF encontra todos os produtos com a palavra "Apple" no início e qualquer número de caracteres depois dela (denotados por ‘*’). Assim que a correspondência for encontrada, ele resume o Quantidade números correspondentes às sequências de texto correspondentes.
Também é possível usar vários curingas nos critérios. E você também pode inserir caracteres curinga com referências de células em vez de texto direto.
Para fazer isso, os curingas devem ser colocados entre aspas duplas (““) e concatenados com a (s) referência (s) da célula:
= SOMASE (A2: A14, "*" & D2 & "*", B2: B14)Esta fórmula soma as quantidades de todos os produtos que possuem a palavra ‘Redmi’, não importa onde a palavra esteja localizada na string.
Ponto de interrogação (?) Caractere curinga
Você pode usar o caractere curinga de ponto de interrogação (?) Para fazer a correspondência de strings de texto com qualquer caractere único.
Por exemplo, se você deseja encontrar quantidades de todas as variantes do Xiaomi Redmi 9, pode usar esta fórmula:
= SOMASE (A2: A14, "Xiaomi Redmi 9?", B2: B14)A fórmula acima procura sequências de texto com a palavra “Xiaomi Redmi 9” seguida por quaisquer caracteres únicos e soma os correspondentes Quantidade números.
Til (~) Wildcard
Se você deseja corresponder a um ponto de interrogação real (?) Ou caractere de asterisco (*), insira o caractere til (~) antes do curinga na parte da condição da fórmula.
Para adicionar as quantidades na coluna B com a string correspondente que tem um sinal de asterisco no final, insira a fórmula abaixo:
= SOMASE (A2: A14, "Samsung Galaxy V ~ *", B2: B14)
Para adicionar quantidades na coluna B que têm um ponto de interrogação (?) Na coluna A na mesma linha, tente a fórmula abaixo:
= SOMASE (A2: A14, "~?", B2: B14)
Função SUMIF com critérios de data
A função SUMIF também pode ajudá-lo a somar valores condicionalmente com base em critérios de data - por exemplo, números correspondentes a uma determinada data, ou antes de uma data, ou depois de uma data. Você também pode usar qualquer um dos operadores de comparação com um valor de data para criar critérios de data para somar números.
A data deve ser inserida no formato de data compatível com as planilhas do Google, ou como uma referência de célula que contém uma data, ou usando uma função de data como DATE () ou TODAY ().
Usaremos esta planilha de exemplo para mostrar como funciona a função SUMIF com critérios de data:
Suponha que você queira somar os valores de vendas que aconteceram em ou antes de (<=) 29 de novembro de 2019 no conjunto de dados acima, você pode adicionar esses números de vendas usando a função SUMIF de uma das seguintes maneiras:
= SOMASE (C2: C13, "<= 29 de novembro de 2019", B2: B13)A fórmula acima verifica cada célula de C2 a C13 e corresponde apenas às células que contêm datas em ou antes de 29 de novembro de 2019 (29/11/2019). Em seguida, soma o valor das vendas correspondente às células correspondentes do intervalo de células B2: B13 e exibe o resultado nas células E3.
A data pode ser fornecida para a fórmula em qualquer formato que seja reconhecido pelo Planilhas Google, como "29 de novembro de 2019 ′," 29 de novembro de 2019 "ou" 29/11/2019 "etc. Lembre-se do valor da data e o operador deve ser sempre colocado entre aspas duplas.
Você também pode usar a função DATE () nos critérios, em vez de usar o valor de data direto:
= SOMASE (C2: C13, "<=" & DATA (2019,11,29), B2: B13)
Ou você pode usar referência de célula em vez de data na parte dos critérios da fórmula:
= SOMASE (C2: C13, "<=" & E2, B2: B13)
Se você deseja adicionar os valores de vendas com base na data de hoje, você pode usar a função TODAY () no argumento de critérios.
Por exemplo, para somar todos e quaisquer valores de vendas para a data de hoje, use esta fórmula:
= SOMASE (C2: C13, HOJE (), B2: B13)
Função SUMIF com células em branco ou não em branco
Às vezes, você pode precisar somar os números em um intervalo de células com células em branco ou não em branco na mesma linha. Nesses casos, você pode usar a função SOMASE para somar valores com base em critérios em que as células estão vazias ou não.
Soma se estiver em branco
Existem dois critérios no Planilhas Google para encontrar células em branco: “” ou “=”.
Por exemplo, se você deseja somar todo o valor das vendas que contém strings de comprimento zero (visualmente parece vazio) na coluna C, use aspas duplas sem espaço entre eles na fórmula:
= SOMASE (C2: C13, "", B2: B13)
Para somar todo o valor das vendas na coluna B com células em branco completas na coluna C, inclua “=” como critério:
= SOMASE (C2: C13, "=", B2: B13)
Soma se não estiver em branco:
Se quiser somar células que contenham qualquer valor (não vazio), você pode usar “” como o critério na fórmula:
Por exemplo, para obter o valor total das vendas em quaisquer datas, use esta fórmula:
= SOMASE (C2: C13, "", B2: B13)
SUMIF com base em múltiplos critérios com lógica OR
Como vimos até agora, a função SOMASE é projetada para somar números com base em apenas um único critério, mas é possível somar valores com base em vários critérios com a função SOMASE no Planilhas Google. Isso pode ser feito unindo mais de uma função SOMASE em uma única fórmula com lógica OR.
Por exemplo, se você deseja somar o valor das vendas na região ‘Oeste’ ou ‘Sul’ (lógica OR) no intervalo especificado (B2: B13), use esta fórmula:
= SOMASE (C2: C13, "Oeste", B2: B13) + SOMASE (C2: C13, "Sul", B2: B13)Esta fórmula soma as células quando pelo menos uma das condições é VERDADEIRA. Por isso, é conhecido como 'lógica OR'. Ele também somará os valores quando todas as condições forem atendidas.
A primeira parte da fórmula verifica o intervalo C2: C13 para o texto ‘Oeste’ e soma os valores no intervalo B2: B13 quando a correspondência é encontrada. A parte dos segundos das verificações para o valor de texto ‘Sul’ no mesmo intervalo C2: C13 e, em seguida, soma os valores com o texto correspondente no mesmo intervalo_soma B2: B13. Em seguida, as duas somas são somadas e exibidas na célula E3.
Nos casos em que apenas um critério é atendido, ele retornará apenas esse valor de soma.
Você também pode usar vários critérios em vez de apenas um ou dois. E se você estiver usando vários critérios, é melhor usar uma referência de célula como um critério em vez de escrever o valor direto na fórmula.
= SOMASE (C2: C13, E2, B2: B13) + SOMASE (C2: C13, E3, B2: B13) + SOMASE (C2: C13, E4, B2: B13)
SOMASE com lógica OR adiciona valores quando pelo menos um dos critérios especificados é atendido, mas se você deseja somar valores apenas quando todas as condições especificadas são atendidas, você tem que usar sua nova função SOMASE () irmã.
Função SUMIFS no Planilhas Google (critérios múltiplos)
Quando você usa a função SOMASE para somar valores com base em vários critérios, a fórmula pode ficar muito longa e complicada e você está sujeito a cometer erros. Além disso, SUMIF permitirá que você some valores apenas em um único intervalo e quando qualquer uma das condições for TRUE. É aí que entra a função SUMIFS.
A função SUMIFS ajuda a somar valores com base em vários critérios de correspondência em um ou mais intervalos. E funciona na lógica AND, o que significa que só pode somar valores quando todas as condições fornecidas são atendidas. Mesmo se uma condição for falsa, ela retornará '0' como resultado.
Sintaxe e argumentos da função SUMIFS
A sintaxe da função SUMIFS é a seguinte:
= SUMIFS (intervalo_soma, intervalo_critérios1, critério1, [intervalo_critérios2, ...], [critério2, ...])Onde,
- intervalo_soma - O intervalo de células que contém os valores que você deseja somar quando todas as condições forem atendidas.
- intervalo_de_critérios1 - É o intervalo de células em que você verifica os critérios1.
- critérios1 - É a condição que você precisa verificar em intervalo_de_criterios1.
- crintervalo_de_iteria2, critério2,…- Os intervalos e critérios adicionais a serem avaliados. E você pode adicionar mais intervalos e condições à fórmula.
Usaremos o conjunto de dados na captura de tela a seguir para demonstrar como a função SUMIFS funciona com diferentes critérios.
SUMIFS com Condições de Texto
Você pode somar valores com base em dois critérios de texto diferentes em intervalos diferentes. Por exemplo, digamos que você queira saber o valor total das vendas do item Tenda entregue. Para isso, use esta fórmula:
= SUMIFS (D2: D13, A2: A13, "Tenda", C2: C13, "Entregue")Nessa fórmula, temos dois critérios: “Tenda” e “Entregue”. A função SUMIFS verifica o item ‘Tenda’ (critérios1) no intervalo A2: A13 (intervalo_critérios1) e verifica o status ‘Entregue’ (critérios2) no intervalo C2: C13 (intervalo_critérios2). Quando ambas as condições são atendidas, ele soma o valor correspondente no intervalo de células D2: D13 (intervalo_soma).
SUMIFS com critérios numéricos e operadores lógicos
Você pode usar operadores condicionais para criar condições com números para a função SUMIFS.
Para encontrar o total de vendas de mais de 5 quantidades de qualquer item no estado da Califórnia (CA), use esta fórmula:
= SUMIFS (E2: E13, D2: D13, "> 5", B2: B13, "CA")Esta fórmula possui duas condições: “> 5” e “CA”.
Esta fórmula verifica as quantidades (Qtd) maiores que 5 no intervalo D2: D13 e verifica o estado 'CA' no intervalo B2: B13. E quando ambas as condições são atendidas (ou seja, estão na mesma linha), ele soma o valor em E2: E13.
SUMIFS com critérios de data
A função SUMIFS também permite que você verifique várias condições no mesmo intervalo, bem como em intervalos diferentes.
Suponha que você queira verificar o valor total das vendas dos itens entregues após 31/5/2021 e antes da data de 06/10/2021 e, em seguida, use esta fórmula:
= SUMIFS (E2: E13, D2: D13, ">" & G1, D2: D13, "<" & G2, C2: C13, G3)A fórmula acima tem três condições: 31/5 / 2021,10 / 5/2021 e entregue. Em vez de usar data direta e valores de texto, nos referimos a células contendo esses critérios.
A fórmula verifica as datas após 31/5/2021 (G1) e antes de 06/10/2021 (G2) no mesmo intervalo D2: D13 e verifica o status ‘Entregue’ entre essas duas datas. Em seguida, soma o valor relacionado no intervalo E2: E13.
SUMIFS com células em branco e não em branco
Às vezes, você pode querer encontrar a soma dos valores quando uma célula correspondente está vazia ou não. Para fazer isso, você pode usar um dos três critérios que discutimos antes: “=”, “” e “”.
Por exemplo, se você deseja somar apenas a quantidade de itens 'Tenda' cuja data de entrega ainda não foi confirmada (células vazias), você pode usar o critério de “=”:
= SUMIFS (D2: D13, A2: A13, "Tenda", C2: C13, "=")A fórmula procura o item ‘Tenda’ (critério1) na coluna A com células em branco correspondentes (critério2) na coluna C e, em seguida, soma a quantidade correspondente na coluna D. O “=” representa uma célula completamente em branco.
Para encontrar a soma dos itens da 'Tenda' para os quais a data de entrega foi confirmada (não células vazias), use “” como critério:
= SUMIFS (D2: D13, A2: A13, "Tenda", C2: C13, "")Acabamos de trocar “=” por “” nesta fórmula. Ele encontra a soma dos itens da Tenda com células não vazias na coluna C.
SUMIFS com lógica OR
Como a função SUMIFS funciona na lógica AND, ela soma apenas quando todas as condições são atendidas. Mas e se você quiser somar o valor com base em vários critérios quando qualquer um dos critérios for atendido. O truque é usar várias funções SUMIFS.
Por exemplo, se você deseja somar o valor das vendas de ‘Suporte para bicicletas’ OU ‘Mochila’ quando seu status for ‘Encomendado’, tente esta fórmula:
= SUMIFS (D2: D13, A2: A13, "Suporte para bicicletas", C2: C13, "Ordenado") + SUMIFS (D2: D13, A2: A13, "Mochila", C2: C13, "Ordenado")A primeira função SUMIFS verifica dois critérios "Bike rack" e "Ordenado" e soma os valores de quantidade na coluna D. Em seguida, o segundo SUMIFS verifica dois critérios "Mochila" e "Ordenado" e soma os valores de quantidade na coluna D. E então , ambas as somas são somadas e exibidas em F3. Em palavras simples, esta fórmula soma quando o ‘Suporte para bicicletas’ ou ‘Mochila’ são encomendados.
Isso é tudo que você precisa saber sobre a função SUMIF e SUMIFS no Planilhas Google.