SUMIFS allows you to add numeric types based on certain specified criteria. Some examples could be finding out the total salary of each department, the number of inventory in a particular warehouse, and your total expenses for the month. It is extremely useful when you have a dataset that can be classified into multiple categories.
This post will focus on introducing SUMIFS by breaking down its formula, followed by an example for better illustration. Let’s Begin!
SUMIFS Formula
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
The SUMIFS formula can be found above, with 3 COMPULSORY INPUTS, and ANY EVEN NUMBER OF NON-COMPULSORY INPUTS. Let’s dive deeper and understand what each input means.
sum_range
the sum_range will be the column you would like to perform your summation on. This column also needs to be a numeric type. Some examples of this column would be Salary, Total Inventory, and Expense. This is a compulsory input for SUMIFS to work.
criteria_range1 & criteria1
the critria_range1 will be the column where it will be tested based on the inputs that are stated in criteria1. The rows that pass the criteria stated in criteria1 will be included in the items to be added together (NOTE: this is only true if there is ONE criteria. If there is more than one criterion, only the rows that pass ALL criteria will be included into the items to be added together). Some examples of these columns can be Department Name, Warehouse name, or Expense type. This is a compulsory input for SUMIFS to work
–
criteria1 will be the condition you specify that will be checked on criteria_range1. The rows in criteria_range1 that pass this condition will be added together (NOTE: this is only true if there is ONE criterion. If there is more than one criterion, only the rows that pass ALL criteria will be included in the items to be added together). Some examples of criteria can be:
- Department Name = “Sales”
- Warehouse Name = “S3000”
- Expense Type = “Mandatory Expense”
criteria_rangeX & criteriaX (or any amount of criteria)
criteria_rangeX and criteriaX is the same as criteria_range1 and criteria1, but allow you to specify more conditions to be checked. An easier way to look at the formula would be
Add all items that meet criteria 1 AND criteria 2 AND criteria 3 AND …. criteria X
This means that line items will only be added if ALL criteria have been met.
SUMIFS Example
Imagine your boss gives you a list of employees that contains their age, department, and salary and wants you to find out how much salary is paid to the sales department for employees aged 40. The list of employees is in the table that you see above. You can manually add them up using the calculator app on your PC, or… you can just use SUMIFS and let Excel do the hard work.
–
The Criteria
There are 2 criteria stated in your boss’s requirements, and they are:
- Only employees who are under the Sales Department
- Only employees aged 40 should be included in the addition
Now that we understand the 2 criteria, we can begin to work on the Excel formula.
–
The Formula
Because there are 2 criteria to be met before adding the salary together, the SUMIFS formula will be as follows:
SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
As there are 2 criteria to be met, criteria_range1 and criteria1 will be used to check if the employee’s department is from sales, and criteria_range2 and criteria2 will be used to check if the employee is 40 years old. Using the Excel sheet shown above as an example, the Excel formula will be:
=SUMIFS(D2:D7,C2:C7,G2,B2:B7,H2)
sum_range [D2:D7]
as we are looking to sum the salary of the selected employees, the sum_range will be Salary, which is in cells D2 to D7.
–
criteria_range1 [C2:C7]
The first criterion is to check if the employee’s department is from sales. Thus, the range that we need to check would be the Department’s table, which is in cells C2 to C7.
–
criteria1 [G2]
The first criterion is to check if the employee’s department is from sales. Thus, the condition we are looking to check if the criteria are met is either a static text called “Sales”, or a cell that has the text Sales in it (in this case, it is G2).
–
criteria_range2 [B2:B7]
The second criterion is to check if the employee’s age is 40 years old. Thus, the range that we need to check would be the age column, which is in cells B2 to B7.
–
criteria2 [H2]
The second criterion is to check if the employee’s age is 40 years old. Thus, the condition we are looking to check if the criteria are met is either a static numeric figure of 40,, or a cell that has the numeric figure of 40 in it (in this case, it is H2).
This will result in a Salary of $5,000 for employees who are in the sales department and are 40 years old.
Personal Thoughts
As a data analyst, there will be times when you have to provide quick insights on the go. The example that I’ve given above does happen from time to time, where your boss might ask you to give the total sales generated in a specific country. Hence, knowing SUMIFS will ensure that you will not be caught off guard whenever your manager requests simple insights on the go.
I would like to hear your thoughts on SUMIFS, and how you think you will use it in your day-to-day task. Let me know in the comments section below!