Excel - COUNTIFS and SUMIFS

Tutorial

Excel

Advanced

Produce

Intermediate

00:00:07

who have 50 or more orders, we use the COUNTIFS function.

00:00:13

First we select the region cell range

00:00:17

and I press F4 to make this an absolute cell reference,

00:00:22

Then we select the criteria it needs to meet,

00:00:25

in this case the region is east,

00:00:29

then we select the cell range for the number of orders

00:00:34

and the criteria it needs to meet,

00:00:37

greater than or equal to 50 in cell G2,

00:00:42

I'm using a cell reference for the criteria

00:00:44

instead of hard coding it into the formula,

00:00:47

so that I can easily change it if I want to.

00:00:54

In the East region there is one person with 50 or more orders.

00:00:59

Let's walk through this.

00:01:02

First the function evaluates how many cells in the region cell range

00:01:07

B2 through B7 are equal to East,

00:01:12

in this example there are two: Bob and Sue.

00:01:17

Of these two it then evaluates how many have orders

00:01:21

greater than or equal to 50, cell G2.

00:01:26

In this case it's just Bob.

00:01:33

COUNTIFS supplies criteria to cells across multiple ranges,

00:01:37

and counts the number of times all criteria are met.

00:01:43

This is the syntax of the COUNTIFS function.

00:01:46

Criteria range 1 is required, it's the first range that's evaluated.

00:01:52

Criteria 1 is required,

00:01:55

It's the criteria by which criteria range 1 is evaluated.

00:02:00

Criteria range 2, criteria 2, and so on are optional.

00:02:07

Each additional range must have the same

00:02:10

number of rows and columns as criteria range 1

00:02:13

but they don't have to be adjacent to each other.

00:02:18

You can use the question mark and asterisk wildcard characters in criteria.

00:02:25

Now we want to determine the sales by region,

00:02:29

where the salesperson has 50 or more orders.

00:02:33

We can do this with the SUMIFS function.

00:02:37

First we select the range of cells we want to add,

00:02:41

In this case the range of cells in the sales column,

00:02:45

I press F4 to make this an absolute cell reference,

00:02:50

Next we select the first cell range we want to evaluate,

00:02:54

the 4 regions in the region's column.

00:02:58

Then, we select the criteria that the range will be evaluated by,

00:03:03

In this case East cell F-2,

00:03:07

then we select the range of the cells for the number of orders

00:03:11

and the criteria, greater than or equal to 50 in cell G2.

00:03:19

In the East region, the total sales by sales reps with

00:03:23

orders greater than or equal to 50 is $49,017.

00:03:30

Let's walk through this,

00:03:32

it's similar to the COUNTIFS function.

00:03:36

First the function evaluates how many cells in the region

00:03:39

columns range of cells are equal to East,

00:03:44

there are two, Bob and Sue are the salespeople for the East Region.

00:03:50

Of these two, it then evaluates how many have orders

00:03:54

greater than or equal to 50.

00:03:58

For East it's just Bob.

00:04:02

Lastly the function adds the cells from the range of

00:04:06

cells in the cells column where all the correspondent criteria are met:

00:04:11

Bob is the only salesperson from the East region

00:04:13

to meet all of the criteria.

00:04:16

So the results of the function is Bob sales figure.

00:04:22

SUMIFS adds the cells in a range that meet multiple criteria.

00:04:27

This is the syntax of the SUMIFS function,

00:04:31

some range is required, it's one or more cells to sum,

00:04:36

blank and text values are ignored,

00:04:40

criteria range 1 is required, it's the first range that's evaluated,

00:04:47

criteria 1 is required, it's the criteria by which

00:04:50

criteria range 1 is evaluated,

00:04:54

criteria range 2, criteria 2 and so on are optional.

00:05:00

Each cell in the sum range argument is summed only if all

00:05:04

the corresponding criteria specified are true for that cell,

00:05:09

each criteria range argument must contain the same number

00:05:12

of rows and columns as the sum range argument.

00:05:18

You can use the question mark and asterisk wildcard characters in criteria.

00:05:25

Up next: the AVERAGEIFS and IFERROR functions.

00:00:03

Para determinar o número de vendedores, por região,

00:00:07

com 50 ou mais pedidos, usamos a função CONT.SE.

00:00:13

Primeiro, selecionamos a região da faixa de células

00:00:17

e pressiono a tecla F4 para torná-la uma célula de referência absoluta.

00:00:22

Selecionamos, então, o critério que precisamos encontrar,

00:00:25

neste caso, a região é Leste,

00:00:29

então selecionamos a faixa de células para o número de pedidos

00:00:34

e o critério que precisamos encontrar,

00:00:37

maior ou igual a 50 na célula G2;

00:00:42

estou usando uma célula de referência para o critério

00:00:44

ao invés de fazer o hard coding dentro da fórmula,

00:00:47

de modo que eu possa alterá-la facilmente se eu quiser.

00:00:54

Na região Leste há uma pessoa com 50 ou mais pedidos.

00:00:59

Vamos esmiuçar isso.

00:01:02

Primeiro a função avalia quantas células na região da faixa de células

00:01:07

B2 a B7 são iguais a Leste;

00:01:12

neste exemplo são duas: Bob e Sue.

00:01:17

Destes dois, ela avalia quantos têm pedidos

00:01:21

maiores ou iguais a 50 na célula G2.

00:01:26

Neste caso, apenas Bob.

00:01:33

CONT.SE fornece critérios para células através de múltiplas faixas,

00:01:37

e conta o número de vezes que todos os critérios foram atendidos.

00:01:43

Esta é a sintaxe da função CONT.SE.

00:01:46

A faixa de critério 1 é obrigatória. É a primeira faixa avaliada.

00:01:52

Critério 1 é obrigatório,

00:01:55

é o critério pelo qual a faixa de critério 1 é avaliada.

00:02:00

A faixa de critério 2, critério 2 e daí por diante são opcionais.

00:02:07

Cada faixa adicional deve ter o mesmo

00:02:10

número de linhas e colunas que a faixa de critério 1,

00:02:13

mas não precisa ser adjacente uma a outra.

00:02:18

Você pode usar o ponto de interrogação e o caráter de curinga do asterisco no critério.

00:02:25

Agora queremos determinar as vendas por região,

00:02:29

onde o vendedor tem 50 ou mais pedidos.

00:02:33

Podemos fazer isso com a função SOMASES.

00:02:37

Primeiro selecionamos a faixa de células que queremos adicionar,

00:02:41

neste caso, a faixa de células na coluna vendas.

00:02:45

Pressiono a tecla F4 para torná-la uma célula de referência absoluta.

00:02:50

A seguir, selecionamos a primeira faixa de células que queremos avaliar,

00:02:54

as 4 regiões na coluna de regiões.

00:02:58

Selecionamos, então, o critério pelo qual a faixa será avaliada.

00:03:03

Neste caso, célula Leste, F2,

00:03:07

então selecionamos a faixa de células para o número de pedidos

00:03:11

e o critério, maior ou igual a 50 na célula G2.

00:03:19

Na região Leste, o total de vendas por representando com

00:03:23

pedidos maior ou igual a 50 é $49.017.

00:03:30

Vamos esmiuçar isso,

00:03:32

é bem similar à função CONT.SE.

00:03:36

Primeiro a função avalia quantas células na região

00:03:39

das colunas faixa de células são iguais a Leste.

00:03:44

Existem duas, Bob e Sue são vendedores para a Região Leste.

00:03:50

Destes dois, ela avalia quantos têm pedidos

00:03:54

maiores ou iguais a 50.

00:03:58

Para a região Leste, apenas Bob.

00:04:02

Por fim, a função adiciona as células das faixa de

00:04:06

células nas colunas de células onde todos os critérios correspondentes são atendidos:

00:04:11

Bob é o único vendedor da região Leste

00:04:13

a atender todos os critérios.

00:04:16

Assim, o resultado da função é Bob.

00:04:22

SOMASES adiciona as células em uma faixa que atende múltiplos critérios.

00:04:27

Esta é a sintaxe da função SOMASES,

00:04:31

alguma faixa é obrigatória, uma ou mais células para somar,

00:04:36

valores vazios e de texto são ignorados,

00:04:40

a faixa de critério 1 é obrigatória. É a primeira faixa avaliada,

00:04:47

critério 1 é obrigatório. É o critério pelo qual

00:04:50

a faixa de critério 1 é avaliada,

00:04:54

a faixa de critério 2, critério 2 e daí por diante são opcionais.

00:05:00

Cada célula na faixa de argumento soma é somado apenas se todos

00:05:04

os critérios correspondentes especificados forem verdadeiros para esta célula,

00:05:09

cada faixa de argumento critério deve conter o mesmo número

00:05:12

de linhas e colunas da faixa de argumento soma.

00:05:18

Você pode usar o ponto de interrogação e o caráter de curinga do asterisco no critério.

00:05:25

A seguir: as funções MÉDIASE e SEERRO.

00:00:03

Wenn ich feststellen möchte, wie viele Vertriebsmitarbeiter pro Region

00:00:07

50 oder mehr Aufträge abgewickelt haben, verwende ich die Funktion ZÄHLENWENNS.

00:00:13

Im ersten Schritt wähle ich den Zellbereich „Region” aus.

00:00:17

Ich drücke F4, um den Zellbezug zu einem absoluten Zellbezug zu machen.

00:00:22

Anschließend wähle ich die Kriterien aus, die zutreffen müssen.

00:00:25

In diesem Fall ist dies die Region „Osten”.

00:00:29

Dann wähle ich den Zellbereich mit der Auftragsanzahl und das

00:00:34

Kriterium aus, das zutreffen muss:

00:00:37

„ist größer als oder gleich 50” in Zelle G2.

00:00:42

Ich verwende in der Formel einen Zellbezug anstelle eines

00:00:44

definierten Werts als Kriterium,

00:00:47

damit ich das Kriterium im Bedarfsfall einfach ändern kann.

00:00:54

In der Region „Osten” gibt es eine Person mit 50 oder mehr Aufträgen.

00:00:59

Diese Funktion wollen wir uns nun genauer anschauen.

00:01:02

Die Funktion prüft im ersten Schritt, wie viele Zellen im Zellbereich „Region”,

00:01:07

B2 bis B7, gleich „Osten” sind.

00:01:12

In diesem Beispiel gibt es zwei Mitarbeiter: Bob und Sue.

00:01:17

Anhand dieser beiden Zellen wird nun in Zelle G2 ausgewertet,

00:01:21

wer über 50 oder mehr Aufträge verfügt.

00:01:26

Dies gilt in diesem Fall nur für Bob.

00:01:33

Mit ZÄHLENWENNS werden Kriterien auf mehrere Zellbereiche angewendet

00:01:37

und es wird ermittelt, wie häufig die Kriterien zutreffen.

00:01:43

Hier die Syntax der Funktion ZÄHLENWENNS:

00:01:46

„Kriterienbereich1” ist erforderlich. Dies ist der erste Bereich, der ausgewertet wird.

00:01:52

„Kriterien1” ist erforderlich.

00:01:55

Dies ist das Kriterium, anhand dessen der Kriterienbereich 1 ausgewertet wird.

00:02:00

„Kriterienbereich2”, „Kriterien2” und so weiter sind optional.

00:02:07

Jeder weitere Bereich muss über die gleiche Anzahl

00:02:10

an Zeilen und Spalten wie „Kriterienbereich1” verfügen,

00:02:13

die Bereiche müssen jedoch nicht nebeneinander liegen.

00:02:18

Sie können Fragezeichen und Stern als Platzhalterzeichen in Kriterien verwenden.

00:02:25

Nun möchte ich die Umsätze nach der Region ermitteln, in der der

00:02:29

Vertriebsmitarbeiter 50 oder mehr Aufträge abgewickelt hat.

00:02:33

Dafür kann ich die Funktion SUMMEWENNS verwenden.

00:02:37

Als Erstes markiere ich den Zellbereich, den ich addieren möchte, in

00:02:41

diesem Fall die Zellen in der Spalte „Umsätze”.

00:02:45

Ich drücke wieder F4, um den Zellbezug zu einem absoluten Zellbezug zu machen.

00:02:50

Als Nächstes wähle ich den ersten Zellbereich aus, der ausgewertet werden soll,

00:02:54

die vier Regionen in der Spalte „Region”.

00:02:58

Dann wähle ich das Kriterium aus, nach dem der Bereich ausgewertet werden soll,

00:03:03

in diesem Fall „Osten”, Zelle F2.

00:03:07

Zum Schluss wähle ich den Zellbereich mit der Anzahl der Auträge

00:03:11

und dann das Kriterium, „Größer oder gleich 50”, in Zelle G2 aus.

00:03:19

In der Region „Osten” beträgt der Gesamtumsatz nach Vertriebsmitarbeitern

00:03:23

mit einer Auftragsanzahl von 50 oder mehr $ 49.017.

00:03:30

Wir wollen uns auch diese Funktion genauer anschauen.

00:03:32

Sie ist mit der Funktion ZÄHLENWENNS vergleich