Excel SUMIFS Function

The Excel SUMIFS function is similar to the Excel SUM function that adds values from a range together, but with the added benefit of being able to add multiple criteria or conditions you specify. Think of it as a conditional SUM function. The close relative of the SUMIFS function is the Excel SUMIF function, the only major difference between the two is that you can specify multiple conditions in the SUMIFS, while only one condition with the SUMIF function.

Syntax of the SUMIFS Function

The SUMIFS function has 3 required arguments (input data separated by commas), then up to an optional 127 pairs of criteria\_range and criteria arguments. The syntax is as follows:

=SUMIFS(sumrange, criteriarange1, criteria1, [criteria_range2],[criteria2],...)

SUM\Range (required) \- These are the cells that will be added together. The sum\range values that correspond to the criteria\_range values that matched the criteria will be added.
Criteria\Range (required) \- The Criteria\Range is the cells that must adhere to the criteria or condition you are looking for. The cells in the Criteria\_Range argument can contain numbers, text, or dates. You can use a defined named range if desired.
Criteria (required) \- The criteria or condition that is used by Excel to determine which cells to add. The values within the Criteria\_Range argument will be compared against the Criteria argument to find values that match the given Criteria. The criteria can be a number, an expression, a cell reference, text, or another function. Criteria can contain wildcard characters (see section below).

SUMIFS Criteria Examples

	Criteria Type	Example	Result
1	Number	54	Looks for any number equal to 54
2	Expression	">100"	Looks for any number greater than 100
3	Cell Reference	A15	Looks for any value equal to the value in cell A15
4	Wildcard	"W\*"	Looks for any value that starts with a W
5	Function	=TODAY()	Looks for any value that matches the current date

Important Note: Text criteria and criteria that include logical operator symbols ( >, <, <=, >=, =, <>) must be surrounded by double quotation marks (" ") in Excel

Excel Wildcard Examples

	Wildcard	Meaning
1	?	Single character wildcard
2	\*	Wildcard string of undefined length
3	~	Used when you want to use one of the wildcard characters as an actual criteria (e.g. a question mark is actually part of the text) Example: "What~?"

Examples using the SUMIFS Function in Excel

Use this data for the following function examples.

	A	B	C	D	E	F	G
1	Name	Employee ID	Sales Region	Region Code	Sales	New Customers	Commission
2	Hugh Jackman	1002	East	124	$10,000	20	$1,000
3	Brad Pitt	1003	West	542	$5,000	4	$500
4	Scarlett Johansson	1005	West	542	$2,000	2	$200
5	Raymond Reddington	1006	East	124	$9,000	15	$900
6	Elizabeth Keen	1007	West	542	$15,000	25	$1,500
7	Scarlett Johansson	1005	South	140	$10,000	15	$1,100
8	Idris Elba	1008	East	124	$12,000	22	$1,350

Exercises Using Number Criteria

If we wanted to get the total amount of sales from region code 542 only for employee 1005 in the Excel table above, we could write:

=SUMIFS(E2:E8,D2:D8,542,B2:B8,1005)

SUM\_Range: E2:E8 \- This is the Region code column. These are the values the SUMIFS function will conditionally add together.
Criteria\_Range1: D2:D8 \- This is the Region Code column, which is the range that will be checked for matches to our criteria1 condition.
Criteria1: 542 \- Excel will look for any value in D2:D8 (Criteria\_Range1) that matches our criteria of 542.
Criteria\_Range2: B2:B8 \- This is the Employee ID column, which will be checked for matches to our criteria2 condition.
Criteria2: 1005 \- This is the condition that will be used to find matches within our Criteria\_Range2 argument.

The function would return the value of $2000. In D2:D8 it would have found a match in cells D3, D4, and D6. Then it would perform the matching for Criteria2 in cells B2:B8 and only find a match in cells B4 and B7. It would then take the corresponding values from the sum range where both conditions are met. The only record in the data that matches both conditions would be in row 4 since D4 and B4 would both evaluate to TRUE and pass the criteria we were looking for.

Example Using Logical Expression and Text Condition

In the above Excel table, if we wanted to add together to Total amount of sales from all employees in the West region that sold more than $4000 worth of products, we would construct a SUMIFS function as follows:

=SUMIFS(E2:E8,E2:E8,">4000",C2:C8,"West")

SUM\_Range: E2:E8 \- This is the Sales column. This is where the SUMIFS function will add together for the records that match all criteria.
Criteria\_Range1: E2:E8 \- This is the Sales column again, which is the range that will be checked for matches to our criteria1 condition.
Criteria1: ">4000" \- Excel will look for any value in E2:E8 (Criteria\_Range1) that matches our criteria of ">4000".
Criteria\_Range2: C2:C8 \- This is the Sales Region column, which will be checked for matches to our criteria2 condition.
Criteria2: "West" \- Excel will look for any value in C2:C8 (Criteria\_Range2) that matches our criteria of "West".

The function would return the value of $20,000. In E2:E8 it would have found a match in all rows except row 4. It then checks the Criteria2 and finds a match on cells C3, C4, C6. The only records (rows) that have a match to both Criteria1 and Criteria2 would be rows 3 and 6, which add up to $20,000. It would disregard the other rows that match only 1 of the criteria.

Example Using Wildcard and Text Criteria

In the above Excel table, if we wanted to add together to Total Commission amounts from all employees whose names ended with the letter N in the East region, we would do the following:

=SUMIFS(G2:G8,A2:A8,"*n",C2:C8,"East")

SUM\_Range: G2:G8 \- This is the Commissions column. This is where the SUMIFS function will add together for the records that match all criteria.
Criteria\_Range1: A2:A8 \- This is the Employee Name Column. This is where the SUMIFS function will look for matches to Criteria1.
Criteria1: "\n" \- Excel will look for any value in A2:A8 (Criteria\_Range1) that matches our criteria of "\n". "\*n" means it will find any size text string that has the last letter of "n".
Criteria\_Range2: C2:C8 \- This is the Sales Region column, which will be checked for matches to our criteria2 condition.
Criteria2: "East" \- Excel will look for any value in C2:C8 (Criteria\_Range2) that matches our criteria of "East".

The function would return the value of $19,000. In A2:A8 it would have found a match in cells A2 (Hugh Jackman), A4 (Scarlett Johansson), A5 (Raymond Reddington), and A6 (Elizabeth Keen), and A7(Scarlett Johansson). In C2:C8 it would have found matches in cells C2, C5, and C8. It would then SUM the cells in G2:G8 (Commission) that are on rows that match all conditions. Both conditions are met on rows 2 and 5.

The Excel Family of SUM Functions

The SUMIFS function in the tutorial is only one of many belonging to the group of SUM functions in Excel. There are many summing functions in Excel used for a variety of other tasks shown below:

If adding cells together without any rules, use the SUM function.

If adding cells that must meet one specific criteria, use the SUMIF function.

If adding cells that must meet multiple criteria, use the SUMIFS function.

Continue to SUMIFS practice exercises! →

Images Referenced

https://excelexercises.com/logo2.png
https://excelexercises.com/excel-functions/excelImages/logoGreenWhite.png