Google Sheets SUMIFS to sum cells with multiple AND / OR criteria

This tutorial demonstrates how to use the SUMIFS function in Google Sheets to sum cells based on multiple criteria. Several real-world examples are provided, showcasing both AND and OR logic.

Google Sheets offers two functions for conditional summation: SUMIF and SUMIFS. While SUMIF handles single criteria, SUMIFS excels with multiple conditions.

The key difference:

• SUMIF: Sums cells based on a single condition.
• SUMIFS: Sums cells based on two or more conditions.

This tutorial explains SUMIFS syntax and provides formula examples for various data types.

Table of Contents:

• Google Sheets SUMIFS Function: Syntax and Basic Usage
• Summing Cells with Multiple AND Criteria
  • SUMIFS with Logical Operators
  • SUMIFS with Other Functions
  • SUMIFS with Blank and Non-Blank Cells
• Summing Cells with Multiple OR Criteria
  • SUMIF with OR Logic (Criteria in One Column)
  • SUMIF with OR Criteria and Results in Separate Cells
  • SUMIFS with OR Logic (Criteria in Different Columns)

See the colors, sum the numbers! Effortlessly sum cells based on their fill color using Ablebits Data.

Try Ablebits Data now! Duplicates? Merge, Sum, Done!

Identify duplicates, combine data, and calculate totals with ease.

Try Ablebits Data now! Unlock the full potential of your Google Sheets.

Discover the Ablebits Data collection.

Google Sheets SUMIFS Function: Syntax and Basic Usage

The SUMIFS function in Google Sheets sums numbers based on multiple conditions. Its arguments are:

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

Where:

• sum_range (required): The range of cells to sum.
• criteria_range1 (required): The range evaluated against criterion1.
• criterion1 (required): The condition for cells in criteria_range1.
• criteria_range2, criterion2, ... (optional): Additional ranges and criteria.

Three Key Points for Using SUMIFS:

1. SUMIFS sums only cells meeting all conditions (AND logic).
2. The sum_range and all criteria_ranges must be the same size. Otherwise, a #VALUE error occurs.
3. Criteria construction depends on data type:
   • Text, wildcards, and dates require quotes (e.g., "apples", "*", "4/19/2024").
   • Numbers and cell references don't need quotes (unless part of a logical expression).
   • Logical operators with numbers, dates, or text need quotes (e.g., ">200", ">4/19/2024", "apples").
   • Logical operators with cell references and functions require concatenation (e.g., ">="&B1 or ">="&TODAY()).

Summing with Multiple Criteria (AND Logic)

Let's say you have items in column A, amounts in column B, and delivery status in column C. To find the total for "apples" that are "delivered":

Avoid complex ARRAYFORMULA and SUMPRODUCT solutions. Instead, use SUMIFS:

• criteria_range1: A2:A13, criterion1: "apples"
• criteria_range2: C2:C13, criterion2: "delivered"
• sum_range: B2:B13

The simple formula is:

=SUMIFS(B2:B13, A2:A13, "apples", C2:C13, "delivered")

For greater flexibility, use cell references for criteria:

=SUMIFS(B2:B13, A2:A13, F1, C2:C13, F2)

(Image showing SUMIFS example with AND logic)

SUMIFS with Logical Operators

For numerical or date criteria, use comparison operators: =, , >, =,

Example: Sum amounts in B2:B13 greater than or equal to 200 and delivered before April 1st, 2024:

=SUMIFS(B2:B13, B2:B13, ">=200", C2:C13, "

Using cell references:

=SUMIFS(B2:B13, B2:B13, ">="&F1, C2:C13, "

(Image showing SUMIFS example with logical operators)

SUMIFS with Other Functions

Conditions can depend on other functions. Embed these functions within the criteria.

Example: Sum "apples" delivered on or before today:

=SUMIFS(B2:B13, A2:A13, "apples", C2:C13, "

(Image showing SUMIFS example with other functions)

SUMIFS with Blank and Non-Blank Cells

• "=" for completely blank cells.
• "" for empty cells (including zero-length strings).
• "" for non-blank cells (including zero-length strings).

Example: Sum "apples" with a delivery date (non-blank C column):

=SUMIFS(B2:B13, A2:A13, "apples", C2:C13, "")

(Image showing SUMIFS example with blank/non-blank cells)

Summing with Multiple Criteria (OR Logic)

SUMIFS uses AND logic by default. For OR logic, use multiple SUMIF functions or SUMPRODUCT.

SUMIF with OR Criteria (Same Column)

For a few criteria in the same column, add SUMIF functions:

=SUMIF(A:A, "apples", B:B) SUMIF(A:A, "bananas", B:B)

For more criteria, use ARRAYFORMULA and SUM:

=SUM(ARRAYFORMULA(SUMIF(A2:A13, {"apples", "bananas", "lemons"}, B2:B13)))

Or SUMPRODUCT:

=SUMPRODUCT((A2:A13={"apples", "bananas"}) \* B2:B13)

(Image showing SUMIF example with OR logic in the same column)

SUMIF with OR Criteria and Results in Different Cells

To get subtotals in separate cells, use ARRAYFORMULA (without SUM):

=ARRAYFORMULA(SUMIF(A2:A13, {"apples", "bananas", "lemons"}, B2:B13))

(Image showing SUMIF example with OR logic and results in different cells)

SUMIFS with OR Criteria (Different Columns)

For multiple sets of conditions (AND within sets, OR between sets), use SUMPRODUCT:

Example: Sum amounts where column A is "apples" OR "oranges" AND column C is "3/16/2024":

=SUMPRODUCT(--(C2:C13="3/16/2024"), (--(ISNUMBER(MATCH(A2:A13, {"apples", "oranges"}, 0)))), B2:B13)

(Image showing SUMIFS example with OR logic in different columns)

This tutorial provides a comprehensive guide to using SUMIFS in Google Sheets. For further examples, refer to the provided spreadsheet.

The above is the detailed content of Google Sheets SUMIFS to sum cells with multiple AND / OR criteria. For more information, please follow other related articles on the PHP Chinese website!