**How to use the SUMIFS function in Excel and what is the purpose of the function?**

**Select the column with the value to add**Only one column must be selected

**The column containing the first criterion**Select the column where is the first criterion

**First criterion**Write the value of the first criterion

**[optional] The column containing the second criteria**Select the column where is the second criterion

**[optional] Second criterion**Write the value of the second criterion

=SUMIFS(Range to add, Column with the first criteria, First criteria, Column with the second criteria, Second criteria, ....)

## Purpose of the SUMIFS function in Excel

The **SUMIFS function** in Excel helps **to sum values based on multiple criteria or conditions**. It allows you to filter and add up only the values that meet the specified criteria, making it a powerful tool for data analysis and reporting.** **

## Example: What are the sales of the Category Drink in Germany

In this example, we want to calculate the **sales for the Category Drink in Germany**. Note, the data are in a Table named **tbl_Sales**. This is easier to visualize the columns selected.

In other words, we want to do a sum with 2 criteria

- The first criterion, the
**Category** - The second criterion, the
**Country**

In this situation, how to write the SUMIFS function? It's easy

- Write the function and open the parenthesis
- Select the column with the value to add (here the column
**Total**) - Then, select the Category column (the first criterion)
- Just after, indicate the value of the criterion (here
**Drink**)

At this step, the writing is the following

=SUMIFS(tbl_Sales[Total],tbl_Sales[Category],"Drink")

**But, the criterion of the country is missing**. So, we must add another criterion to the SUMIFS function.

- Select the Country column
- And finally, write the value of the country (here
**Germany**)

=SUMIFS(tbl_Sales[Total],tbl_Sales[Category],"Drink",tbl_Sales[Country],"Germany")

## What happens when the value of the country is changed?

Now, if we replace **Germany **with **Australia**, we will return the number** of sales for the Drinks in Australia** 😀👍

## Use references instead of the name of the criteria

You can also use references in your SUMIFS function to use the content of cells. Here, we have extracted the country's name without duplicates using the UNIQUE function. Also, use the SORT function to alphabetically order the country's name.

And then, the SUMIFS function is

=SUMIFS(tbl_Sales[Total],tbl_Sales[Category],"Drink",tbl_Sales[Country],I3)

**AND JUST LIKE THAT, you have to build a report to visualize all the results for all the countries** 👍👏

## Related articles

- COUNTIF, counts on a single criterion
- COUNTIFS, counts rows for many criteria
- How to use wildcards with COUNTIFS and SUMIFS
- Training with the SUMIF and SUMIFS functions

## Differences between SUMIF and SUMIFS functions

For many people, SUMIF and SUMIFS functions **are similar, but this is untrue**.

**Both functions don't require the same number of criteria**- The function
**SUMIF**will require only 1 criterion - The function
**SUMIFS**requires 1 to 256 criteria

- The function

- The writing of the functions is different
- With
**SUMIF**, the column to sum is necessarily**the last argument** - With
**SUMIFS**, the column to sum is necessarily**the first argument**

- With

Tabinda

15/06/2022 @ 05:43

whatever data use, request to provide for practice