How to Use the SUMIFS Function in Excel
Excel’s list of functions is one of the most dynamic elements in data analysis. From fancy arithmetic operations to advanced conditional logic, there is a little bit of something for every kind of user out there.
The SUMIFS function is one of the many functions available to users wishing to drive detailed analysis based on conditional logic. If you are familiar with the SUMIF function in Excel, you will find the SUMIFS function is quite similar to its counterpart. Here’s what you need to know about using the SUMIFS function in Excel.
Syntax of a SUMIFS Function
The SUMIFS function in Excel is the culmination of the SUM and IF functions. It sums the values in a cell based on specific criteria. You can create conditions related to dates, numbers, and text. Additionally, feel free to use logical operators to manipulate your conditions and make them more expansive.
To master these logical formulas, you need to knowhow to use the IFS function, before combining it with any aggregator functions.
Excel has other logical functions, such as COUNTIF, COUNTIFS, SUMIF, MAXIFS, MINIFS, and many others. When working with any of these functions, you must ensure you pass the right syntax within the formulas.
Here’s the syntax of the SUMIFS Function:
Now that the syntax is out of the way, it is essential to understand the various components of the function and how you can best use it. TheSUMIF function has a lot of uses in the data world, but that does not mean the SUMIFS function is far away. You can pass multiple conditions within the SUMIFS formula and take the SUMIF function up a notch.
Data Preparation
Suppose you have a dataset containing information on products sold in an office factory. Some relevant data columns include the order date, category, color, sale price, quantity sold, and total sales. All formulas will be based on the data references included in this sample data.
Order Date
Sale Price
Total Sales
08-11-2016
12-06-2016
11-10-2015
Appliances
09-06-2014
15-04-2017
05-12-2016
22-11-2015
Dark Brown
11-11-2014
Technology
Not Applicable
13-05-2014
1. Working With Text Conditions
If you want to calculate the total cost ofGreen Bindersfrom this list, you can use theSUMIFSformula as follows:
First, you must define the column you want to get the totals from; in this case, it is theTotal Salescolumn. Define the column range that has the Binders value (column A). The subsequent part of the formula needs the criteria, Binders.
The first criterion is set; to define the next segment, which is color, you must follow a similar rule. You need to include the column that holds the color details, followed by the color (Green) you want to look for.
Excel calculates the values based on the specified conditions and gives the result154.024.
2. Working With Numeric Conditions
In the above example, you defined Text conditions (Color = Green and Category = Binders). Now, the next step is to work with numerical conditions. Let’s illustrate this with an example:
If you want to calculate the total sales for products with a Sale Price between 500 and 1000. Here’s how you can construct the formula:
The above formula’s first range is the Total Sales column reference. In the next segment, you must define the column for the first criterion, which captures values above 500. In the final part, you must define the final criterion, which captures values below 1000.
The final result is13083.888.
3. Working With Date Conditions
You can use the SUMIFS function to sum values basis date conditions. For example, if you have a set of columns with dates and want to find the total sales during a date range, you can use the formula to derive the result quickly.
Like the other formulas above, you could use dates to manipulate your data and sum the total sales values. The first part of the formula refers to the Total Sales column. In the next segment, you need to define the date ranges. You need to use logical operators to manipulate dates within the SUMIFS formula.
Since the reference includes the dates between 01-01-2015 and 01-01-2016, the result is16495.28.
4. Using Wildcard Searches
Finally, there is one last method to use with the SUMIFS function. it’s possible to aptly combine the wildcard search function with the multi-functional SUMIFS function to find matches based on certain elements. For example, suppose you want to find the sum of values that contain the lettersindwithin the Category column, and the letterslain the Color column.
Excel’s SUMIFS function filters through each specified column and looks for the specified letters. Any matching values are summed up, and the result is displayed. To use the wildcard search, you can either use the* (asterix)sign or the? (question mark)symbol within your formula.
Here’s how you can write the formula:
The result is1223.928.
Using the SUMIFS Function in Excel Efficiently
Despite the widespread use of the SUMIFS function within Excel, many analysts continue to rely on the true potential of this powerful function. There are plenty of ways you can use this multi-faceted function in your regular reporting and analysis.
No matter if you are a freelancer, a shopkeeper, or a data analyst, chances are there will always be a way for you to make the most of this function.
SUMIF is a built-in function in Excel that is categorized as a Math/Trig function. Here’s how you’re able to use it!
I found my TV was always listening—so I shut it down.
The best features aren’t the ones being advertised.
Taming data is easier than it looks.
Your phone is a better editor than you give it credit for.
You’re not getting the most out of what you pay for iCloud+.
