Google Sheets SUMIFS Function

The SUMIFS function in Google Sheets allows you to sum values in a range that meets multiple criteria. It’s a powerful tool for conditional summing, ideal for situations where you need to calculate totals based on more than one condition, such as summing sales only for a specific product and region.

SUMIFS Syntax:

Here is the syntax of the SUMIFS function in Google Sheets:

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

• sum_range: The range of cells containing the values to sum.
• criteria_range1: The first range to check for the condition.
• criterion1: The condition that must be met in criteria_range1.

How It Works: SUMIFS sums values in sum_range where all specified criteria across the criteria ranges are met (AND logic).

Sample Dataset

Use this dataset in cells A1:D7 for examples:

1. How to Use SUMIF in Google Sheets

Now we will understand the SUMIFS formula in Google Sheets with examples:

Step 1: Select the Cell for the Result

Choose the cell where you want the sum result to appear. For instance, select C2.

Step 2: Define the Sum Range

Identify the range containing the values you want to sum. For example, use B2:B10 if this range contains the sales data.

Step 3: Define the Criteria Ranges and Conditions

Specify the range and condition for the criterion:

• Criteria Range: A2:A10 (e.g., the column with product types).
• Condition: "Product A" (the specific product you want to filter by).

Step 4: Write the SUMIFS Formula

In the selected cell (e.g., C2), enter the formula:

=SUMIFS(B2:B10, A2:A10, "Product A")

This formula calculates the sum of values in B2:B10 where the corresponding cells in A2:A10 match "Product A".

Step 5: Press Enter

Press Enter to apply the formula. The calculated sum based on your criterion will appear in the selected cell.

2. SUMIFS in Google Sheets to Sum Cells with Multiple Criteria

To use SUMIFS in multiple criteria follow the steps given below:

Step 1: Select the Cell for the Result

Choose the cell where you want the sum result to appear (e.g., D2).

Step 2: Define the Sum Range

Identify the range of cells to sum. For example, use C2:C10 if this range contains the values you want to total (e.g., a "Sales Amount" column).

Step 3: Add Criteria Ranges and Conditions

Specify the criteria ranges and their respective conditions:

• First criteria range: A2:A10 (e.g., the "Product" column) with the condition "Product A".
• Second criteria range: B2:B10 (e.g., the "Region" column) with the condition "North".

Step 4: Write the SUMIFS Formula

In the selected cell (e.g., D2), type the formula:

=SUMIFS(C2:C10, A2:A10, "Product A", B2:B10, "North")

This formula sums the values in C2:C10 where the corresponding cells in A2:A10 match "Product A" and the cells in B2:B10 match "North".

Step 5: Press Enter

Press Enter to apply the formula. The calculated sum based on your criteria will appear in the selected cell.

Also Read:

• Google Sheets INDEX MATCH
• Google Sheets LEFT Function
• Google Sheets PROPER Function