Excel中按另一列文本值排除单元格求和的公式设置

Got it, let’s break down exactly how to set up this calculation for your commission report—this is a straightforward but critical task for tracking upcoming payments. I’ll cover the most common tools (Excel and Google Sheets) since those are the go-to for ongoing, updatable reports.

Core Logic #

You need to sum all values in the Commission Amount column where the corresponding Paid? cell is set to NO. This works for both static ranges and dynamically updating reports as you add new data.

Excel Solution #

Basic SUMIF Formula

If your data is in a regular range (e.g., Commission Amount is column C, Paid? is column D), use the SUMIF function:

=SUMIF(D:D, "NO", C:C)

• D:D: The entire column containing your "Paid?" statuses
• "NO": The criteria we’re filtering for (unpaid entries)
• C:C: The column with commission amounts to sum

Dynamic Range (For Ongoing Updates)

If you want the formula to automatically include new rows as you add them, convert your data to an Excel Table (select your data range → press Ctrl+T). Then use a structured reference:

=SUMIF(Table1[Paid?], "NO", Table1[Commission Amount])

Replace Table1 with your actual table name—now any new rows added to the table will be included in the sum automatically, no manual range adjustments needed.

Handle Case Insensitivity (If Needed)

If your "Paid?" column has inconsistent capitalization (e.g., no, No, NO), use SUMPRODUCT to ignore case:

=SUMPRODUCT(--(UPPER(Table1[Paid?])="NO"), Table1[Commission Amount])

Google Sheets Solution #

Google Sheets uses nearly identical logic, with a couple of flexible options:

Basic SUMIF Formula

Same as Excel—just adjust columns to match your sheet:

=SUMIF(D:D, "NO", C:C)

Dynamic Table Reference

Convert your data to a Google Sheets Table (Data → Named ranges → Define as table) and use:

=SUMIF(Table1[Paid?], "NO", Table1[Commission Amount])

New rows added to the table will automatically be included in the calculation.

QUERY Function (For Advanced Flexibility)

If you might need to add more filters later (e.g., date ranges, specific sales regions), the QUERY function is a powerful option:

=QUERY(A:D, "SELECT SUM(C) WHERE D = 'NO' LABEL SUM(C) ''")

This returns just the total without extra labels and automatically ignores empty rows.

Key Notes to Avoid Errors #

• Consistent Values: Make sure your "Paid?" column only uses YES or NO (no extra spaces, typos, or mixed case—unless you use the case-insensitive formula above).
• Numeric Formatting: Ensure the "Commission Amount" column is formatted as a number (not text) so the sum calculates correctly.
• Range Limits: If you don’t want to use the entire column, specify a fixed range (e.g., D2:D1000) instead of D:D to slightly improve performance in large sheets.

内容的提问来源于stack exchange，提问作者Brendan C.