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.
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(sum_range, criteria_range1, criteria1, [criteria_range2],[criteria2],...)
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
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~?" |
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 |
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)
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.
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")
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.
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")
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 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.