Excel多条件求和公式报错求助：COUNTIFS使用错误排查

Hey there, let's get your cost summation working properly—you've got two key issues going on with your current formula, plus a quick note about working across sheets:

1. You're Using the Wrong Function #

First off, COUNTIFS is designed to count rows that match multiple conditions, not sum values. Since you want to total the numbers in column M, you need to use SUMIFS instead—it's the sum-focused equivalent for multi-condition calculations.

2. Your Parameter Structure Is Incorrect #

The COUNTIFS (and SUMIFS) function requires paired arguments: each condition needs a corresponding range to check. Your original formula ends with Diversey!M:M without a matching condition, which is why you're getting the "too many arguments" error.

For SUMIFS, the syntax follows this structure:

=SUMIFS(求和范围, 条件范围1, 条件1, 条件范围2, 条件2, ...)

Corrected Formula #

Here's the fixed version tailored exactly to your needs:

=SUMIFS(Diversey!M:M, Diversey!A:A, "1", Diversey!I:I, "CUST: POD signed unchecked")

Let's break this down clearly:

• Diversey!M:M: The column holding the cost values you want to sum
• Diversey!A:A, "1": First condition—only include rows where column A equals "1" (your target week number)
• Diversey!I:I, "CUST: POD signed unchecked": Second condition—only include rows where column I matches this exact text string

3. Working in a Separate Worksheet #

Since you're operating from a different sheet than your source data (the Diversey sheet), the formula already explicitly references the Diversey sheet for every range. You just need to enter this formula directly into the cell of your target worksheet—no extra navigation or setup required.

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