Este tutorial demonstra como usar o Excel SUMIF e SUMIFS Funcções no Excel e no Planilhas Google para somar os dados que atendem a determinados critérios.
Visão geral da função SUMIF
Você pode usar a função SOMASE no Excel para somar células que contêm um valor específico, células que são maiores ou iguais a um valor, etc.
(Observe como as entradas da fórmula aparecem)
Sintaxe e argumentos da função SUMIF:
| 1 | = SUMIF (intervalo, critérios, [intervalo_soma]) |
faixa - O intervalo de células ao qual você deseja aplicar os critérios.
critério - Os critérios usados para determinar quais células adicionar.
intervalo_soma - [opcional] As células a serem somadas. Se intervalo_soma for omitido, as células no intervalo serão adicionadas juntas.
Qual é a função SUMIF?
A função SUMIF é uma das funções mais antigas usadas em planilhas. É usado para percorrer um intervalo de células, verificando um critério específico e, em seguida, somando valores em um intervalo que corresponda a esses valores. A função SUMIF original foi limitada a apenas um critério. Após 2007, foi criada a função SUMIFS que permite uma infinidade de critérios. A maior parte do uso geral permanece o mesmo entre os dois, mas existem algumas diferenças críticas na sintaxe que discutiremos ao longo deste artigo.
Se ainda não o fez, você pode revisar grande parte da estrutura e exemplos semelhantes no artigo COUNTIFS.
Exemplo básico
Vamos considerar esta lista de vendas registradas e queremos saber a receita total.
Porque tínhamos uma despesa, o valor negativo, não podemos fazer apenas uma soma básica. Em vez disso, queremos somar apenas os valores maiores que 0. O “maior que 0” é o que será nosso critério em uma função SOMASE. Nossa fórmula para afirmar isso é
| 1 | = SOMASE (A2: A7, "> 0") |
Exemplo de duas colunas
Embora a função SOMASE original tenha sido projetada para permitir que você aplique um critério ao intervalo de números que deseja somar, na maior parte do tempo você precisará aplicar um ou mais critérios a outras colunas. Vamos considerar esta tabela:
Agora, se usarmos a função SUMIF original para descobrir quantas Bananas temos (listadas na célula D1), precisaremos fornecer o intervalo que queremos soma como o último argumento, e assim nossa fórmula seria
| 1 | = SOMASE (A2: A7, D1, B2: B7) |
No entanto, quando os programadores finalmente perceberam que os usuários queriam fornecer mais de um critério, a função SUMIFS foi criada. Para criar uma estrutura que funcione para qualquer número de critérios, o SUMIFS requer que o intervalo de soma seja listado primeiro. Em nosso exemplo, isso significa que a fórmula precisa ser
| 1 | = SUMIFS (B2: B7, A2: A7, D1) |
NOTA: Essas duas fórmulas obtêm o mesmo resultado e podem parecer semelhantes, portanto, preste muita atenção em qual função está sendo usada para certificar-se de listar todos os argumentos na ordem correta.
Trabalho com datas, vários critérios
Ao trabalhar com datas em uma planilha, embora seja possível inserir a data diretamente na fórmula, é uma prática recomendada ter a data em uma célula para que você possa apenas fazer referência à célula em uma fórmula. Por exemplo, isso ajuda o computador a saber que você deseja usar a data 27/05/2020, e não o número 5 dividido por 27 dividido por 2022.
Vejamos nossa próxima tabela que registra o número de visitantes de um site a cada duas semanas.
Podemos especificar os pontos inicial e final do intervalo que queremos observar em D2 e E2. Nossa fórmula para somar o número de visitantes neste intervalo poderia ser:
| 1 | = SUMIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2) |
Observe como fomos capazes de concatenar as comparações de “=” com as referências de célula para criar os critérios. Além disso, embora ambos os critérios estivessem sendo aplicados ao mesmo intervalo de células (A2: A7), você precisa escrever o intervalo duas vezes, uma para cada critério.
Múltiplas colunas
Ao usar vários critérios, você pode aplicá-los ao mesmo intervalo do exemplo anterior ou pode aplicá-los a intervalos diferentes. Vamos combinar nossos dados de amostra nesta tabela:
Configuramos algumas células para que o usuário insira o que deseja pesquisar nas células E2 a G2. Portanto, precisamos de uma fórmula que some o número total de maçãs colhidas em fevereiro. Nossa fórmula se parece com esta:
| 1 | = SUMIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2) |
SUMIFS com lógica de tipo OR
Até este ponto, os exemplos que usamos foram todos baseados em comparação E, em que procuramos linhas que atendam a todos os nossos critérios. Agora, vamos considerar o caso quando você deseja pesquisar a possibilidade de uma linha atender a um ou outro critério.
Vejamos esta lista de vendas:
Gostaríamos de somar as vendas totais de Adam e Bob. Para fazer isso, você tem algumas opções. O mais simples é adicionar dois SOMASE, assim:
| 1 | = SUMIFS (B2: B7, A2: A7, "Adam") + SUMIFS (B2: B7, A2: A7, "Bob") |
Aqui, fizemos com que o computador calculasse nossas pontuações individuais e depois as adicionamos.
Nossa próxima opção é boa para quando você tiver mais intervalos de critérios, de forma que não queira ter que reescrever toda a fórmula repetidamente. Na fórmula anterior, dissemos manualmente ao computador para adicionar dois SOMASE diferentes. No entanto, você também pode fazer isso escrevendo seus critérios dentro de uma matriz, como este:
| 1 | = SOMA (SOMASE (B2: B7, A2: A7, {"Adam", "Bob"})) |
Observe como a matriz é construída dentro das chaves. Quando o computador avaliar esta fórmula, ele saberá que queremos calcular uma função SOMASE para cada item em nosso array, criando assim um array de números. A função SUM externa então pegará aquele array de números e o transformará em um único número. Passando pela avaliação da fórmula, ficaria assim:
| 123 | = SOMA (SOMASE (B2: B7, A2: A7, {"Adam", "Bob"}))= SUM (27401, 43470)= 70871 |
Obtemos o mesmo resultado, mas fomos capazes de escrever a fórmula de forma um pouco mais sucinta.
Lidando com espaços em branco
Às vezes, seu conjunto de dados terá células em branco que você precisa localizar ou evitar. Definir os critérios para isso pode ser um pouco complicado, então vamos ver outro exemplo.
Observe que a célula A3 está realmente em branco, enquanto a célula A5 tem uma fórmula que retorna uma string de comprimento zero de “”. Se quisermos encontrar a soma total de verdadeiramente células em branco, usaríamos um critério de “=” e nossa fórmula seria assim:
| 1 | = SUMIFS (B2: B7, A2: A7, "=") |
Por outro lado, se quisermos obter a soma de todas as células que parecem em branco, alteraremos os critérios para “” e a fórmula será semelhante a
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Vamos inverter: e se você quiser encontrar a soma das células não vazias? Infelizmente, o design atual não permite que você evite a string de comprimento zero. Você pode usar um critério de “”, mas como você pode ver no exemplo, ele ainda inclui o valor da linha 5.
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Se você precisar não contar células contendo strings de comprimento zero, considere o uso da função LEN dentro de um SUMPRODUCT
SUMIF no Planilhas Google
A função SUMIF funciona exatamente da mesma forma no Planilhas Google e no Excel:
