SUMIFSformula is a function in Google Sheets that adds the values in a specified range based on multiple criteria. It is most commonly used to sum data that meets specific criteria, such as summing sales for a particular month by region or summing expenses for a specific category and date range.
- How to use
- Examples of using
SUMIFSformula not working?
- Similar formulas to
SUMIFS formula with the syntax shown below, it has 3 required parameters and 1 optional parameter:
- sum_range (required):
The range of cells to be added if they meet the criteria.
- criteria_range1 (required):
The range of cells to be evaluated by criterion 1.
- criterion1 (required):
The criterion to be used for evaluating criteria_range1.
- criteria_range2, criterion2, ... (optional):
Additional ranges and criteria to be evaluated. Up to 127 range/criteria pairs can be entered.
ExamplesHere are a few example use cases that explain how to use the
SUMIFSformula in Google Sheets.
Summing sales for a specific region and date range
One common use for
SUMIFS is to sum sales data for a specific region and date range. This can be accomplished by setting the first criterion_range to the region column, the second criterion_range to the date column, and using the appropriate criteria for each range.
Calculating expenses for a specific category and month
Another common use for
SUMIFS is to sum expenses data for a specific category and month. This can be accomplished by setting the first criterion_range to the category column, the second criterion_range to the date column, and using the appropriate criteria for each range.
Summing data based on multiple criteria
The primary use for
SUMIFS is to sum data based on multiple criteria. This can be accomplished by entering the appropriate ranges and criteria in the formula.
SUMIFSnot working? Here are some common mistakes people make when using the
SUMIFSGoogle Sheets Formula:
Incorrect range for sum_range
Make sure the sum_range includes only the cells you want to sum. If the range is too large, you will get an incorrect result.
Mismatched number of criteria_range and criterion arguments
Double-check that you have provided the same number of criteria_range and criterion pairs. If you have left out a criterion or range, you will get an error.
Incorrect criteria_range for criterion
Make sure that the criteria_range and criterion are referring to the same type of data. For example, if the criterion is a date, the criteria_range should be a range of dates.
Using text criteria in a numeric criteria_range
If the criteria_range is numeric, the criterion should also be a number. Using text criteria in a numeric criteria_range will result in an error.
Criteria not met
If none of the cells in the criteria_range meet the specified criterion, the formula will return a value of 0. Double-check that your criteria are correct.
The following functions are similar to
SUMIFS or are often used with it in a formula:
SUMIFformula is used to add up values in a range that meet a specific criterion. It can be used to sum values based on text, numbers, or dates. The formula is most commonly used in financial analysis, budgeting, and data analysis.
AVERAGEIFSformula calculates the average of a range of cells that meet multiple criteria. It is most commonly used to find the average of a range of values that meet specific conditions. The formula takes one required argument (average_range) and any number of optional argument pairs (criteria_range, criterion).
COUNTIFSfunction counts the number of cells in a range that meet multiple criteria. It is commonly used to analyze data sets and filter out specific information based on selected criteria.
You can learn more about the
SUMIFS Google Sheets function on Google Support.