Como usar COUNTIFS, SUMIFS, AVERAGEIFS no Excel

As três fórmulas mais usadas no Excel que executam cálculos matemáticos simples são COUNT , SUM e AVERAGE . Esteja você gerenciando um orçamento financeiro no Excel ou simplesmente acompanhando suas próximas férias, provavelmente já usou uma dessas funções antes.

Neste artigo, veremos o básico dessas três funções e suas contrapartes relevantes e úteis: COUNTIFS, SUMIFS e AVERAGEIFS.

Digamos que estamos iniciando um novo negócio online de venda de telefones celulares e temos uma planilha que lista as vendas que fizemos nos primeiros dois meses. Faça o download da planilha Excel de exemplo aqui .

Excel CONTAGEM, SOMA e MÉDIA

Para saber quantos telemóveis já vendemos, podemos utilizar rapidamente a fórmula CONTAGEM conforme abaixo:

=CONTAGEM(E2:E16)

Por outro lado, para obter o valor total das vendas que realizamos, podemos usar a fórmula SOMA conforme mostrado abaixo:

=SOMA(E2:E16)

Por fim, para saber a média de vendas que fizemos para todos os telefones, podemos usar a fórmula MÉDIA conforme abaixo:

=MÉDIA(E2:E16)

O resultado deve ser como abaixo:

As fórmulas COUNT, SUM e AVERAGE funcionarão apenas para registros em que o valor da célula está no formato numérico. Qualquer registro dentro do intervalo da fórmula (isto é, E2:E16 neste exemplo) que não esteja no formato numérico será ignorado.

Portanto, certifique-se de que todas as células nas fórmulas COUNT, SUM e AVERAGE estejam todas formatadas como Number , não Text . Tente usar a mesma fórmula, mas com E:E como intervalo em vez de E2:E16 . Ele retornará o mesmo resultado de antes porque ignora o cabeçalho (ou seja, preço de venda ), que está no formato de texto.

Agora, e se quisermos saber o número de vendas, o valor total das vendas e o valor médio das vendas por telefone, apenas para os vendidos nos EUA? É aqui que COUNTIFS, SUMIFS e AVERAGEIFS desempenham um papel importante. Observe a fórmula abaixo:

COUNTIFS

Divisão da fórmula:

1. =CONT.IFS( – O “=” indica o início de uma fórmula na célula e CONT.IFS é a primeira parte da função do Excel que estamos utilizando.
2. D2:D16 – Refere-se ao intervalo de dados para verificar se satisfaz os critérios a serem incluídos na fórmula de contagem.
3. “USA” – Critérios a procurar no intervalo de dados especificado ( D2:D16 )
4. ) – Colchete de fechamento indicando o final da fórmula.

A fórmula retorna 6, que é o número de vendas de produtos enviados do armazém dos EUA.

SOMAS

Divisão da fórmula:

1. =SUMIFS( – O “=” indica novamente o início da fórmula.
2. E2:E16 – Refere-se ao intervalo de dados que gostaríamos de totalizar, ou seja, preço de venda em nosso exemplo.
3. D2:D16 – Refere-se a intervalo de dados para verificar se satisfaz os critérios para ser incluído no valor total.
4. “USA” – Critérios a procurar no intervalo de dados especificado ( D2:D16 )
5. ) – Colchete de fechamento indicando o final da fórmula.

A fórmula mostra vendas totais de $ 6.050 feitas para produtos enviados do depósito nos EUA.

AVERAGEIFS

Divisão da fórmula:

1. =MÉDIAIFS( – O “=” indica o início da fórmula.
2. E2:E16 – Refere-se ao intervalo de dados que gostaríamos de calcular a média. Neste exemplo, queremos obter a quantidade média de vendas de todos os telefones vendidos nos EUA.
3. D2:D16 – Refere-se ao intervalo de dados para verificar se satisfaz os critérios a serem incluídos na fórmula da média.
4. “EUA” – Critérios a procurar no intervalo de dados especificado
5. ) – Colchete de fechamento que indica o final da fórmula.

A fórmula mostra que vendemos o produto por cerca de US$ 1.008 por telefone nos EUA.

Todas as três fórmulas podem ter mais de um critério. Por exemplo, se quisermos saber os mesmos números (ou seja, COUNT , SUM e AVERAGE ) para produtos vendidos nos EUA , mas especificamente apenas para a  marca Samsung , basta adicionar o intervalo de dados a ser verificado seguido de seus critérios.

Veja o exemplo abaixo onde um segundo critério é adicionado às verificações de critérios iniciais. (O texto azul indica o primeiro critério e o vermelho indica o segundo critério)

=CONT.IFS(D2:D16,"EUA", B2:B16,"Samsung") =SOMAS(E2:E16,D2:D16,"EUA", B2:B16,"Samsung") =MÉDIAIFS(E2:E16,D2 :D16,"EUA", B2:B16,"Samsung")

Você notará que o Excel também possui  fórmulas COUNTIF , SUMIF e AVERAGEIF sem o sufixo “S” . Esses são usados ​​de forma semelhante a COUNTIFS , SUMIFS e AVERAGEIFS . No entanto, aqueles sem o sufixo “S” na fórmula têm a limitação de permitir apenas um critério por fórmula.

Como a sintaxe é um pouco diferente, eu recomendaria usar  apenas COUNTIFS , SUMIFS e AVERAGEIFS , pois pode ser usado para um critério ou mais, se necessário. Apreciar!