Excel - The SUMIF function Tutorial

In this video, you will learn about the SUMIF function in Excel.
The video covers how to use the SUMIF function to add cells that meet specific criteria.
It demonstrates the syntax of the function and provides examples of how to use it to add cells based on conditions.
This will help you efficiently add numbers in Excel and improve your data analysis skills.

5:27
4410 views

Available in Microsoft365

Objectifs :

Understand and apply the SUMIF function in Excel to add cells based on specific criteria, including numerical and text evaluations.

Chapitres :

Introduction to the SUMIF Function
The SUMIF function in Excel is a powerful tool that allows users to sum cells that meet certain criteria. This function is particularly useful for analyzing data sets where only specific values are relevant.
Syntax of the SUMIF Function
The syntax for the SUMIF function is as follows: SUMIF(range, criteria, [sum_range]) - **Range**: This is the range of cells that you want to evaluate (e.g., B2 through B6). - **Criteria**: This defines the condition that must be met (e.g., greater than 100). - **Sum_range**: These are the actual cells to sum if the corresponding cells in the range match the criteria (e.g., C2 through C6). Note that the sum_range is optional; if omitted, the function will sum the cells in the range that meet the criteria.
Example of Using SUMIF
To illustrate the use of the SUMIF function, let's create a formula that adds property values over 160,000. 1. Start with an equal sign: `=`. 2. Type `SUMIF(`. 3. Select the range of property values. 4. Enter the criteria in quotes: ">160000". 5. Press Enter. This will return the sum of property values that exceed 160,000. In this case, if A2 is greater than 160,000, it will add A2, and so on for the other cells.
Defining Sum_range in SUMIF
Next, we can create a formula to add commissions for property values over 160,000. The steps are similar: 1. Start with an equal sign: `=`. 2. Type `SUMIF(`. 3. Select the range of property values. 4. Enter the criteria in quotes: ">160000". 5. Select the sum_range (the range of commissions). 6. Press Enter. This will yield the total commissions for property values exceeding 160,000, which in this example is 63,000.
Using Text Criteria in SUMIF
The SUMIF function can also evaluate text. For instance, to total sales for fruits: 1. Start with an equal sign: `=`. 2. Type `SUMIF(`. 3. Select the range of items. 4. Enter the criteria in quotes: "fruits". 5. Select the sum_range (the range of sales). 6. Press Enter. This will sum the sales for items labeled as fruits.
Using Wildcards in SUMIF
Wildcards can enhance the functionality of the SUMIF function. A question mark (?) matches any single character, while an asterisk (*) matches any sequence of characters. For example, to sum sales for foods ending in 'es': 1. Start with an equal sign: `=`. 2. Type `SUMIF(`. 3. Select the range. 4. Enter the criteria in quotes: "*es". 5. Select the sum_range. 6. Press Enter. This will total the sales for items where the last word ends in 'es'.
Conclusion
In summary, the SUMIF function is a versatile tool in Excel that allows users to sum values based on specific criteria, whether numerical or textual. By understanding its syntax and application, users can effectively analyze and manipulate data to derive meaningful insights.

FAQ :

What is the SUMIF function used for in Excel?

The SUMIF function is used to add up cells that meet a specific condition or criteria. It allows users to sum values based on certain criteria, making it useful for data analysis.

How do I use the SUMIF function?

To use the SUMIF function, you need to specify the range of cells to evaluate, the criteria for summing, and optionally, the range of cells to sum. The syntax is SUMIF(range, criteria, [sum_range]).

Can I use text criteria in the SUMIF function?

Yes, you can use text criteria in the SUMIF function. For example, you can sum values based on whether another cell contains specific text, such as 'fruits'.

What are wildcards in the context of the SUMIF function?

Wildcards are special characters that allow you to match patterns in text criteria. The question mark (?) matches any single character, while the asterisk (*) matches any sequence of characters.

What happens if I omit the sum_range in the SUMIF function?

If you omit the sum_range, the SUMIF function will sum the cells in the range that meet the criteria instead of summing a different set of cells.

Quelques cas d'usages :

Calculating Total Property Values

Use the SUMIF function to calculate the total value of properties that exceed a certain price threshold, such as $160,000. This can help real estate professionals assess market trends.

Summing Commissions Based on Property Values

Real estate agents can use the SUMIF function to sum their commissions for properties sold above a specific value, allowing them to track earnings effectively.

Analyzing Sales Data for Fruits

Retail managers can apply the SUMIF function to total sales of specific product categories, such as fruits, to analyze performance and make inventory decisions.

Using Wildcards for Flexible Criteria

In a grocery store, managers can use the SUMIF function with wildcards to sum sales of products that end with specific letters, such as 'es', to identify trends in product sales.

Budgeting and Expense Tracking

Finance professionals can utilize the SUMIF function to sum expenses that fall under certain categories, helping in budget management and financial analysis.

Glossaire :

SUMIF

A function in Excel that adds the cells specified by a given condition or criteria. The syntax is SUMIF(range, criteria, [sum_range]).

Range

The group of cells that you want to evaluate against the criteria. For example, B2 through B6.

Criteria

The condition that must be met for the cells in the range to be included in the sum. For example, 'greater than 100'.

Sum_range

The actual cells to add if the corresponding cells in the range match the criteria. This is optional; if omitted, the cells in the range are summed.

Wildcards

Special characters used in criteria to match patterns. The question mark (?) matches any single character, and the asterisk (*) matches any sequence of characters.

Equal sign

The symbol (=) used to start a formula in Excel.

00:00:04

a criteria that you provide.

00:00:08

For example, in this formula, if B2 is greater than 100, add C2,

00:00:14

if B3 is greater than 100, add C3 and so on.

00:00:21

The syntax of the SUMIF function is SUMIF,

00:00:25

open parenthesis, range, comma,

00:00:31

criteria, comma, sum_range, close parenthesis.

00:00:38

Range is the range of cells that you want to evaluate

00:00:41

using the criteria such as B2 through B6.

00:00:47

Criteria define what the cells in the range are evaluated against,

00:00:51

such as greater than 100.

00:00:55

Sum_range are the actual cells to add if

00:00:58

the corresponding cells in the range match the criteria,

00:01:02

such as C2 through C6.

00:01:05

Brackets around sum _range indicate it's optional.

00:01:09

If sum_range isn't used, the cells in the range are both evaluated

00:01:13

by the criteria and added if they match the criteria.

00:01:18

Let's try it out.

00:01:20

We're going to create a formula that adds property values

00:01:24

that are over $160,000.

00:01:27

We start with an equal sign, SUMIF,

00:01:33

open parentheses, select the range,

00:01:37

comma, put the criteria in quotes,

00:01:41

in this case greater than 160,000 and press Enter.

00:01:48

And the sum of the property values over $160,000 is $900,000.

00:01:55

This is an example where sum_range is omitted.

00:01:59

The cells in the range, A2 through A5, are both evaluated

00:02:04

by the criteria and added if they match the criteria.

00:02:09

In this example, if A2 is greater than 160,000, add A2,

00:02:15

if A3 is greater than 160,000 add, A3 and so on.

00:02:23

Now we're going to create a formula that adds

00:02:25

the commissions for property values that are over $160,000.

00:02:31

We start with an equal sign, SUMIF,

00:02:35

open parentheses, select the range,

00:02:40

comma, put the criteria in quotes,

00:02:47

comma, select the sum_range

00:02:53

and then press enter.

00:02:56

The commissions for property values over $160,000

00:02:59

is $63,000.

00:03:04

In this example, sum_range is defined.

00:03:08

If A2 is greater than 160,000, add B2,

00:03:13

if A3 is greater than 160,000, add B3 and so on.

00:03:20

We can also evaluate text in a SUMIF function.

00:03:24

Let's create a formula that totals sales for fruits.

00:03:29

As always, we start the formula with an equal sign,

00:03:33

SUMIF, open parentheses, select the range,

00:03:41

comma, put the criteria in quotes, in this case fruits,

00:03:47

comma, select the sum_range

00:03:54

and then press Enter

00:03:56

and we see that the sales total for fruits is $2000.

00:04:01

In this example, if A2 is equal to fruits, add C2,

00:04:06

if A3 is equal to fruits, add C3 and so on.

00:04:13

We can use the question mark and asterisk wildcards as

00:04:16

criteria arguments in a SUMIF function.

00:04:20

A question mark matches any single character

00:04:22

and an asterisk matches any sequence of characters.

00:04:26

Let's add the sales for foods ending in es.

00:04:32

Equal sign, SUMIF,

00:04:36

open parentheses, select the range,

00:04:44

comma, put the criteria in quotes, in this case asterisk, es,

00:04:53

comma, select the sum_range

00:05:00

and then press Enter,

00:05:03

and the total sales for foods ending in es is $4300.

00:05:08

In this example,

00:05:10

if the last word in B2 ends in es, add C2,

00:05:15

if the last word in B3 ends in es, add C3

00:05:20

and so on.

00:05:23

Now you've got a pretty good idea of what's involved in

00:05:25

adding numbers in Excel.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2494 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