Home » Function » Condition » Function SUMIFS in Excel – Example

Function SUMIFS in Excel – Example

Reading time: 2 minutes
Last Updated on 15/05/2023 by Frédéric LE GUEN

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

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.

Differences between SUMIF and SUMIFS

For a lot of people, the functions SUMIF and SUMIFS are similar, but it's not true.

  • 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 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

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.

Data Source for SUMIFS

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

  1. Write the function and open the parenthesis
  2. Select the column with the value to add (here the column Total)
  3. Then, select the Category column (the first criterion)
  4. 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")

SUMIFS function of excel with one criterion

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

  1. Select the Country column
  2. And finally, write the value of the country (here Germany)

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

SUMIFS function of excel two criteria

New Country value

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

Sales of the drink 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 name of the country, without duplicate, with the UNIQUE function. And also use the SORT function to order alphabetically the country's name.

UNIQUE extracts all unique values from a range of cells

And then, the SUMIFS function is

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

Use cells reference for the criteria for your SUMIFS function

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

1 Comment

  1. Tabinda
    15/06/2022 @ 05:43

    whatever data use, request to provide for practice

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Function SUMIFS in Excel – Example

Reading time: 2 minutes
Last Updated on 15/05/2023 by Frédéric LE GUEN

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

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.

Differences between SUMIF and SUMIFS

For a lot of people, the functions SUMIF and SUMIFS are similar, but it's not true.

  • 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 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

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.

Data Source for SUMIFS

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

  1. Write the function and open the parenthesis
  2. Select the column with the value to add (here the column Total)
  3. Then, select the Category column (the first criterion)
  4. 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")

SUMIFS function of excel with one criterion

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

  1. Select the Country column
  2. And finally, write the value of the country (here Germany)

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

SUMIFS function of excel two criteria

New Country value

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

Sales of the drink 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 name of the country, without duplicate, with the UNIQUE function. And also use the SORT function to order alphabetically the country's name.

UNIQUE extracts all unique values from a range of cells

And then, the SUMIFS function is

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

Use cells reference for the criteria for your SUMIFS function

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

1 Comment

  1. Tabinda
    15/06/2022 @ 05:43

    whatever data use, request to provide for practice

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *