Excel - COUNTIFS and SUMIFS Tutorial

In this video, you will learn about COUNTIFS and SUMIFS functions in Microsoft 365. The video covers how to determine the number of salespeople by region who have 50 or more orders using the COUNTIFS function.
It also demonstrates how to calculate the total sales by region where the salesperson has 50 or more orders using the SUMIFS function.
This tutorial will help you understand and apply these functions effectively in Microsoft 365.

5:30
4709 views

Available in Microsoft365

Objectifs :

This video aims to teach viewers how to use the COUNTIFS and SUMIFS functions in Excel to analyze sales data by region, specifically focusing on salespeople with 50 or more orders.

Chapitres :

Introduction to COUNTIFS Function
The COUNTIFS function is used to count the number of salespeople in a specific region who have 50 or more orders. To begin, select the cell range for the region and press F4 to create an absolute cell reference. This allows the reference to remain constant when copying the formula. The criteria for this example is set to 'East', and the threshold for orders is specified in cell G2.
Evaluating Salespeople in the East Region
In the East region, we find that there is one salesperson with 50 or more orders. The function first checks how many cells in the region cell range (B7) are equal to 'East'. In this case, there are two salespeople: Bob and Sue. Next, it evaluates how many of these have orders greater than or equal to 50, which in this case is only Bob.
Understanding COUNTIFS Syntax
The COUNTIFS function requires at least one criteria range and one corresponding criterion. The syntax is as follows: - Criteria range 1: Required, the first range evaluated. - Criteria 1: Required, the criterion for criteria range 1. - Criteria range 2 and criteria 2: Optional, additional ranges and criteria. Each additional range must match the dimensions of criteria range 1. Wildcard characters like '?' and '*' can be used in criteria.
Using SUMIFS Function for Sales Calculation
To calculate total sales by region for salespeople with 50 or more orders, we use the SUMIFS function. First, select the range of cells in the sales column as an absolute reference. Then, select the region column to evaluate the four regions, followed by the criteria for the East region (cell F2) and the criteria for orders (greater than or equal to 50 in cell G2).
Calculating Total Sales for the East Region
In the East region, the total sales from sales reps with orders greater than or equal to 50 amount to 49,017. The function evaluates how many cells in the region column are equal to 'East', identifying Bob and Sue as the salespeople. It then checks who has orders greater than or equal to 50, confirming that only Bob meets this criterion. Finally, the function sums the sales figures for Bob, yielding the total sales.
Understanding SUMIFS Syntax
The SUMIFS function adds cells in a range that meet multiple criteria. The syntax is as follows: - Sum range: Required, the range to sum (blank and text values are ignored). - Criteria range 1: Required, the first range evaluated. - Criteria 1: Required, the criterion for criteria range 1. - Additional criteria ranges and criteria are optional. Each cell in the sum range is summed only if all corresponding criteria are met. Wildcard characters can also be used.
Next Steps: AVERAGEIFS Function
The next topic will cover the AVERAGEIFS function, which allows users to calculate the average of a range based on multiple criteria, similar to the COUNTIFS and SUMIFS functions.

FAQ :

What is the COUNTIFS function used for?

The COUNTIFS function is used to count the number of cells that meet multiple criteria across different ranges in Excel.

How do I use the SUMIFS function?

To use the SUMIFS function, specify the range of cells to sum, followed by the criteria ranges and their corresponding criteria. The function will add the values that meet all specified criteria.

What is the difference between COUNTIFS and SUMIFS?

COUNTIFS counts the number of cells that meet specified criteria, while SUMIFS adds the values of cells that meet those criteria.

Can I use wildcards in COUNTIFS and SUMIFS?

Yes, you can use wildcard characters like '?' for a single character and '*' for multiple characters in the criteria for both COUNTIFS and SUMIFS.

What does an absolute cell reference do?

An absolute cell reference keeps the cell reference constant when the formula is copied to other cells, ensuring that it always refers to the same cell.

How can I find the average of values based on multiple criteria?

You can use the AVERAGEIFS function, which calculates the average of cells that meet multiple specified criteria.

Quelques cas d'usages :

Sales Performance Analysis

Using the COUNTIFS function, a sales manager can analyze the number of salespeople in different regions who have achieved 50 or more orders, helping to identify top performers.

Regional Sales Summation

A business analyst can apply the SUMIFS function to calculate total sales for sales representatives in a specific region with orders greater than or equal to 50, aiding in regional performance assessments.

Sales Forecasting

By utilizing the AVERAGEIFS function, a company can forecast future sales by averaging the sales figures of representatives who meet certain criteria, such as having a minimum number of orders.

Performance Reporting

A sales team leader can create performance reports by using COUNTIFS and SUMIFS to evaluate the effectiveness of sales strategies across different regions and order volumes.

Data Validation

Using COUNTIFS, a data analyst can validate the integrity of sales data by checking how many entries meet specific criteria, ensuring accurate reporting and analysis.

Glossaire :

COUNTIFS

A function in Excel that counts the number of cells that meet multiple criteria across different ranges.

SUMIFS

A function in Excel that adds the values in a range that meet multiple criteria.

AVERAGEIFS

A function in Excel that calculates the average of cells that meet multiple criteria.

Absolute Cell Reference

A cell reference that remains constant when copied to other cells, indicated by the dollar sign (e.g., $A$1).

Criteria Range

The range of cells that is evaluated against a specified criterion in functions like COUNTIFS and SUMIFS.

Wildcard Characters

Special characters used in criteria to represent one or more characters; '?' represents a single character, and '*' represents multiple characters.

Cell Reference

A way to refer to a cell in Excel, which can be relative (changes when copied) or absolute (remains constant).

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.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2495 Views

Video Training Microsoft Excel - Intermediate level

Increase your scope of action with our intermediate tutorials dedicated to formulas, advanced visual enhancement and data analysis with pivo...

ALL COURSES