Google表格SUMIFS函数同一列多条件求和返回0问题求助

Oh, I’ve run into this exact pitfall with SUMIFS before! The reason your formulas are returning 0 boils down to how SUMIFS handles multiple conditions: it uses an AND logic by default. When you specify 'Sheet1'!C1:C100;"Criteria1";'Sheet1'!C1:C100;"Criteria2", you’re asking for rows where column C is both Criteria1 and Criteria2 at the same time—something that’s impossible, hence the 0 result.

Let’s break down the correct solutions for your use case:

1. Nested SUMIFS for OR Logic on the Same Column #

Split your same-column conditions into separate SUMIFS calls, then add them together. This keeps the AND logic for other columns (like your Criteria3 on column D) while applying OR logic to column C.

=SUM(
  SUMIFS('Sheet1'!G1:G100;'Sheet1'!C1:C100;"Criteria1";'Sheet1'!D1:D100;"Criteria3");
  SUMIFS('Sheet1'!G1:G100;'Sheet1'!C1:C100;"Criteria2";'Sheet1'!D1:D100;"Criteria3")
)

2. SUMIFS with Array Conditions (Shorter Syntax) #

You can use an array for the same-column conditions, then wrap the whole thing in SUM to aggregate the results. This works because SUMIFS will calculate a sum for each criteria in the array, and SUM adds those totals together.

=SUM(SUMIFS('Sheet1'!G1:G100;'Sheet1'!C1:C100;{"Criteria1","Criteria2"};'Sheet1'!D1:D100;"Criteria3"))

Note: Your Example 2 was missing the outer SUM and the additional Criteria3 condition—without SUM, you’d get an array of individual sums instead of a total.

3. FILTER + SUM (Most Intuitive Logic) #

If you prefer a more readable approach, use FILTER to first narrow down the rows that match your OR/AND conditions, then sum the resulting values. The + operator acts as OR in FILTER’s conditions, while * acts as AND.

=SUM(FILTER(
  'Sheet1'!G1:G100;
  ('Sheet1'!C1:C100="Criteria1") + ('Sheet1'!C1:C100="Criteria2");
  'Sheet1'!D1:D100="Criteria3"
))

Quick Checks to Avoid Future Issues #

• Match Exact Text: Ensure your criteria don’t have extra spaces or mismatched special characters compared to the data in column C/D.
• Consistent Ranges: Make sure all column ranges (G1:G100, C1:C100, D1:D100) cover the exact same rows—even one row off can break the calculation.

内容的提问来源于stack exchange，提问作者MD Barb