An Ultimate Guide To Learn And Implement SUMIFS In Excel With Practical Examples

The title SUMIFS in Excel signifies that the function is an aggregation function in Excel. But not just any other, this one comes with some advanced capabilities that could redefine the traditional summation methods in Excel. 

This tutorial on SUMIFS in Excel will help you learn the latest and logically powerful aggregation methods in Excel.

Transform Data into Decisions Like a Pro

By Enrolling in Our Tableau Training ProgramStart Learning

What Is SUMIFS in Excel?

SUMIFS in Excel is an aggregation method in Excel that comes with advanced logical and conditional parameters that allow users to apply custom logic to the business data and summarise the selected data based on conditions in real-time.

With the basic definition understood, you shall move ahead and learn the technical implementation of SUMIFS in Excel.

How to Implement SUMIFS in Excel?

To implement the SUMIFS in Excel, you must consider the following superstore dataset that includes sales data from various regions like East, West, North, South, and Central. A glimpse of the dataset appears as shown below.

The goal is to calculate the overall sales in a specific region, say west. To achieve the desired result using SUMIFS in Excel, you must follow a few simple steps.

Step 1 - Convert the Original Data into Tabular Format.

Select all the cells in the spreadsheet and press the shortcut key combination - “CTRL + T.” This will enable a pop on your screen that asks you to grant access for converting data to table data, as shown below.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

Step 2 - Setup Formula for Calculating West Region Sales.

Navigate to the formula bar and type “SUMIFS,” as shown below.

Make sure you select “SUMIFS” as multiple options are available, starting with “SUM.”

Step 3 - Setting the Parameters for the SUMIFS function.

The SUMIFS function has a few parameters. The first one will be the range/column to fetch the total you want to filter out. That is the sales column. 

The second will be the range/column to fetch the region you want to filter out. That is the west region. This data is available in the region column of the dataset.

Finally, the last parameter. Here, you have to fetch and select the cell that reads west. This cell will act as a reference to the SUMIFS function and enables it to select the rows with only “west” in their “region” column. Cell “F7” (Column F and Row number 7) represents the “west” region as shown below.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

Step 4 - Press Enter, and the Aggregation Will Be Done. 

Once the formula is set, you can press enter, and Excel will finish the aggregation operations and displays the result as follows. The final result also includes the remaining regions as well. This can be achieved by using the same formula and also making minor adjustments to the last parameter which is the region name.

You have come to the end of this tutorial on SUMIFS in Excel. 

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

Wrapping Up

DAX in Excel can be your next step in learning data analytics. DAX or the Data Analysis Expressions in Excel will help you devise powerful analytical expressions based on time, mathematics, aggregation, table values, logic, and many more parameters that could play a pivotal role in business and marketing data analysis.

Interested to learn more about Business Analytics? Or wish to get trained and certified to become a successful Business Analyst? Then feel free to look into the Business Analytics certification course from Simplilearn. Ranked among the five topmost business analytics courses, this Simplilearn program is offered in partnership with Purdue University. It is an outcome-driven training and certification program that helps you master the fundamental concepts of statistics and data analytics. 

Have any questions for us on this tutorial on "SUMIFS  in Excel"? If you do, or maybe you have doubts about our certification course, do reach out to us by sharing them as comments below. Our team of experts will address them and will be happy to answer them at the earliest.