How Do SUMIFS Formulas Work? An Easy Guide

SUMIFS is one of the most useful and time-saving formulas in spreadsheets. Unlike SUMIF that can only be used with one criterion or range, SUMIFS gets the sum of the data with multiple criteria.

Check out this tutorial to know more about the SUMIFS formula.

SUMIFS Syntax

The syntax of SUMIFS is as follows:

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])

Let us break down the syntax. 

• sum_range is the range where you will get the sum.
• criteria_range1 is the range of data of the first criteria.
• criterion1 is the specific criteria from criteria_range1.
• criteria_range2, criterion2, etc., are additional ranges and criteria which are optional.

How To Use the SUMIFS Formula

Below is some sample sales data. We will use the SUMIFS function to get the total sales amount based on a sales representative, location, region, item, etc.

For this example, we will get the total television sales of John Williams.

1. Start by writing the following in any empty cell, then press Enter then = SUMIFS

You will see that a box will appear on your screen showing the syntax of SUMIFS. If ever you are worried about memorizing all the syntaxes, Google Sheets shows you the syntaxes of different functions upon typing it. So, you don’t need to memorize or constantly look up the syntax of functions on the internet.

2. Next, highlight the data for the sum_range, which is the data under “Total Sale Amount” cells I2 to I7. Then, type a comma.

3. Highlight the data for the criteria_range1, which is the data under “Sales Representative” cells A2 to A17. Then, type a comma.

4. Select criterion1 by clicking on any cell containing “John Williams” and type a comma.

5. Highlight the data for the criteria_range2, which are the cells under “Item” cells F2 to F17. Then, type a comma.

6. Select criterion2 by clicking on any cell containing “Television” and type a comma.

7. Finally, type a closing parenthesis to complete your formula.

Your overall formula will look like this:

= SUMIFS (I2:I17, A2:A17, A2,F2:F17, F2)

8. Press Enter to get the total.

Alternatively, you can also type the criteria required instead of selecting cells. In this case, you will need to type the following.

= SUMIFS (I2:I17, A2:A17, “John Williams”, F2:F17, “Television”)

Based on the formula above, you will see that we have typed “John Williams” instead of selecting criteria1, and “Television” instead of selecting criteria 2. Note that you can only do this for the criteria. For the ranges, you will need to highlight the required cells.

That’s it! You can also get the total sales from other sales representatives, regions, items, etc., by following the same syntax and selecting the different ranges and criteria.

SUMIFS With Comparison Operators

SUMIFS can also be used with the following comparison operators:

• > : greater than
• < : less than
• <> : not equal to
• = : is equal to

For example, we need to get the total sales of John Williams for items greater than the price of 100. To do this, type the following formula:

= SUMIFS (I2:I17, A2:A17, A2, I2:I17, “>100”)

Same as before,

• The sum_range is the data under “Total Sale Amount” cells I2 to I7
• The criteria_range1 is the data under “Sales Representative” cells A2 to A17
• Criterion1 is “John Williams”
• Now, cells I2 to I7 have become the criteria_range2 since we will find the prices greater than 100 in this range 
• Lastly, “>100” is our criterion2. In writing formulas with operators, do not forget to add quotation marks

More with SUMIFS formula

SUMIFS with Wildcards

Wildcards are special characters that you can use to replace any character. There are three wildcards in Google Sheets:

• * (asterisk) represents any character.
• ? (question mark) represents a single character.
• ~ (tilde) is used for matching any wildcard characters.

You can use these wildcards along with the SUMIFS formula to speed up your spreadsheet work.

Conclusion

In this article, you have learned to use the SUMIFS function to take your spreadsheet work to the next level. Now, you don’t have to manually search data in your spreadsheet to get the sum when you’re given multiple criteria.