SUMIFS

• How to use SUMIFS formula?
• Examples of using SUMIFS formula
• SUMIFS formula not working?
• Similar formulas to SUMIFS

Usage

Use the SUMIFS formula with the syntax shown below, it has 3 required parameters and 1 optional parameter:

=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

1. sum_range (required):
   The range of cells to be added if they meet the criteria.
2. criteria_range1 (required):
   The range of cells to be evaluated by criterion 1.
3. criterion1 (required):
   The criterion to be used for evaluating criteria_range1.
4. criteria_range2, criterion2, ... (optional):
   Additional ranges and criteria to be evaluated. Up to 127 range/criteria pairs can be entered.

Examples

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.

Common Mistakes

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.

Related Formulas

The following functions are similar to SUMIFS or are often used with it in a formula:

• SUMIF

  The SUMIF formula 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.

• AVERAGEIFS

  The AVERAGEIFS formula 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).

• COUNTIFS

  The COUNTIFS function 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.

Learn More

You can learn more about the SUMIFS Google Sheets function on Google Support.