In this quick guide, we’ll unravel the power of SUMIFS and how it can revolutionize your data analysis in Excel.

Formula

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

In the formula above:

• sum_range: This is the range of cells that you want to sum.
• criteria_range1, criteria_range2, etc.: These are the ranges that contain the criteria you want to apply to sum_range.
• criteria1, criteria2, etc.: These are the criteria you want to apply to the corresponding criteria_range.

Usage example

To give a quick example let’s concider a portion of the Kilometers produced per day per line dataset (modified: 10 January 2024 09:04) form the opendata platform of tpg. Download the dataset with usage example here. Below is the screenshot of the dataset.

Use SUMIFS function to find the total kilometers produced by the line 32 during the normal schedule from the table below.

Answer: Here is the formula to solve the problem.

=SUMIFS(E2:E11,B2:B11,32,D2:D11,"NORMAL")

In the formula above:

• E2:E11 represents the sum_range. Here we are summing all the values of the column Kilometers Produced to get the total kilometers produced.
• B2:B11 and 32 designate respectively the criteria_range1 and the criteria1. We add this criteria range to filter only the line 32. At this stage the summation of Kilometers will be done for the line 32 only.
• D2:D11 and NORMAL designate respectively the criteria_range2 and the criteria2. We add this column to filter only the the NORMAL schedule type. At this stage the summation is done for the line 32 and for the NORMAL schedule type only.

The answer is 2508.661.

We can achieve this by filtering 32 from the colum Line and NORMAL from the colum Schedule Type. Of course you need to activate the filter under the Data tab to enable filtering. After that we can select the displayed values under the column Kilometers Produced and see the same result at the right bottom of the workbook. Check the picture below.

Overall, the SUMIFS function is a powerful tool for performing conditional sums in Excel, allowing you to analyze and summarize data based on specific criteria.

SOME BOOKS TO LEARN EXCEL

• Excel All-in-One For Dummies
• Excel Made Easy: The Ultimate Crash Course to Master Excel Without Getting Overwhelmed
• Excel Basics In 30 Minutes: The beginner’s guide to Microsoft Excel, Excel Online, and Google Sheets

As an Amazon Associate, I earn from qualifying purchases.