This guide covers everything you need to know about the Google Sheets SUMIFS function, including its definition, syntax, use cases, and how to use it.
What is the SUMIFS Function? How Does It Work?
The SUMIFS function returns the sum of a range of cells that meet multiple specified criteria. This dynamic function in Google Sheets allows you to set multiple conditions and only sum the values that adhere to all these conditions.
In essence, the SUMIFS function works by examining a range of cells and adding up the values in the cells that meet all specified criteria. For instance, if you wanted to add up all the sales in a month that were over $20 and made by a certain salesperson, you can use the SUMIFS function to do this.
Similarly, if you wanted to sum all the values in a range where corresponding values in another range are greater than 20 and values in yet another range are less than 30, you could use SUMIFS.
The SUMIFS function will only include the cell in the sum if it meets all the criteria you’ve specified. If any of the conditions are not met for a certain cell, that cell’s value will not be included in the final sum.
SUMIFS Syntax
The syntax and arguments for the function are as follows:
SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])
Let’s break down each argument in greater detail:
- sum_range: This is the range of cells that you want to sum. It is a required argument. The sum_range argument must contain numeric values; if it includes non-numeric values, Google Sheets will ignore them.
- criteria_range1: This is the range of cells that you want to evaluate with a specific criterion. It is also a required argument.
- criterion1: This is the condition that cells in criteria_range1 need to meet to be included in the sum. It is a required argument. The criterion can be a number, expression, cell reference, or text that defines which cells will be added.
- criteria_range2, criterion2, …: These are additional ranges and their associated criteria. They are optional arguments. You can add as many criteria as you need, up to a maximum of 127 pairs.
Usage notes related to syntax and arguments:
- If the criteria_range and sum_range arguments do not contain the same number of rows and columns, Google Sheets will return a #VALUE! error.
- The criterion argument can include wildcard characters. The question mark (?) matches any single character, and the asterisk (*) matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
- The criterion argument is not case-sensitive. For example, the criterion “YES” will match “yes”, “Yes”, and “YES”.
- If you use a cell reference as a criterion, the function will use the value of the cell in the criterion.
- The SUMIFS function will return a #N/A error if no cells meet the criteria.
Examples of How to Use the SUMIFS Function
Here are some practical examples of how to use the SUMIFS function in Google Sheets.
Example #1: Summing Sales for a Specific Product
Let’s say you have a sales data sheet where column A lists product names, column B lists the quantity sold, and column C lists the sales amount. You want to find out the total sales amount for a specific product, say “Product X”. Here’s how you can do it with SUMIFS:
=SUMIFS(C2:C100, A2:A100, “Product X”)
This formula sums the values in column C (sales amount) only if the corresponding value in column A (product name) is “Product X”.
Example #2: Summing Sales for a Specific Product in a Specific Month
Now, let’s make it a bit more complex. Assume column D contains the sale date. You want to find out the total sales amount for “Product X” in the month of January. Here’s how you can do it:
=SUMIFS(C2:C100, A2:A100, “Product X”, D2:D100, “>=”&DATE(2022,1,1), D2:D100, “<=”&DATE(2022,1,31))
This formula sums the values in column C (sales amount) only if the corresponding value in column A (product name) is “Product X” and the sale date in column D is between January 1, 2022, and January 31, 2022.
Example #3: Summing Sales for a Specific Product in a Specific Month, Exceeding a Certain Quantity
Let’s add another condition. You want to find out the total sales amount for “Product X” in the month of January, but only for sales where the quantity sold exceeded 10 units. Here’s how you can do it:
=SUMIFS(C2:C100, A2:A100, “Product X”, D2:D100, “>=”&DATE(2022,1,1), D2:D100, “<=”&DATE(2022,1,31), B2:B100, “>10”)
This formula sums the values in column C (sales amount) only if the corresponding value in column A (product name) is “Product X”, the sale date in column D is between January 1, 2022, and January 31, 2022, and the quantity sold in column B is more than 10 units.
Why Is SUMIFS Not Working? Troubleshooting Common Errors
If you’re trying to use the SUMIFS function in Google Sheets and it’s not working, it can be a frustrating experience. However, understanding what types of errors can occur and how to troubleshoot them can help you find a solution quickly. Here are some of the most common errors with SUMIFS and how to fix them.
#VALUE! Error
Cause: This error typically occurs when one of the criteria in your SUMIFS function is not numeric or if the function is pointing to a range that doesn’t exist.
Solution: To resolve the #VALUE! error, ensure that all criteria within your SUMIFS function are numeric. Also, double-check your function to make sure it’s not looking for a range that doesn’t exist.
#REF! Error
Cause: The #REF! error will appear if your SUMIFS function is referencing a cell that has been deleted.
Solution: To fix the #REF! error, you’ll need to adjust your function to reference a valid cell. Check your spreadsheet to make sure the cell you’re referencing hasn’t been deleted. If it has, modify your function to reference a different cell.
#N/A Error
Cause: The #N/A error is displayed when your SUMIFS function is unable to find a match for your criteria.
Solution: If you’re seeing the #N/A error, double-check your criteria. Make sure that the criteria you’ve entered matches the data in your spreadsheet. If it doesn’t, modify your criteria so that it does.
#DIV/0! Error
Cause: This error is shown when you’re trying to divide by zero within your SUMIFS function.
Solution: To resolve the #DIV/0! error, modify your function so that it’s not attempting to divide by zero. Check the cells you’re referencing in your function to ensure they don’t contain a zero.
#NUM! Error
Cause: The #NUM! error is displayed when your SUMIFS function is trying to calculate a number that’s too large or too small for Google Sheets to handle.
Solution: If you’re seeing the #NUM! error, you’ll need to adjust your function so that it’s not trying to calculate an excessively large or small number. Check your data and your function to make sure you’re not attempting to sum a range that includes extremely large or small numbers.
Using SUMIFS With Other Google Sheets Functions
Combining SUMIFS with other Google Sheets functions can enhance your data analysis capabilities. By integrating SUMIFS with other functions, you can create more complex conditions and analyze your data in a more intricate way. Let’s see how you can use SUMIFS with other functions such as DATE.
With DATE
Usage: DATE function is used to return the number that represents the date in Google Sheets date-time code. When combined with SUMIFS, it can sum data that meets specific date conditions.
Example: Using the sales data sheet, suppose Column D represents the date of sales and you want to find out the total sales made by a specific salesperson in a specific year. You can use SUMIFS and DATE together for this.
=SUMIFS(C2:C10, A2:A10, “John”, D2:D10, “>=”&DATE(2020,1,1), D2:D10, “<=”&DATE(2020,12,31)) will sum the sales made by John in the year 2020.
For more details on the SUMIFS function, check out the official documentation at the Google Docs Editors Help Center.
