SUMIFS公式异常求助：支出计算正常，收入无法核算

Hey there! Let’s figure out why your SUMIFS works perfectly for expenses but falls flat when calculating income. I’ve broken down the most common culprits and fixes to check step by step:

• Double-check range alignment
  Make sure your sum range (the column with income values) and criteria ranges (like the column labeling "收入"/"支出") cover exactly the same rows. For example, if your criteria range is A2:A100, your income sum range should be B2:B100—not B1:B100 or B2:99. Mismatched ranges are one of the top causes of blank or incorrect results.

• Verify exact text matching
  Tiny text discrepancies often break SUMIFS:

  • Extra spaces in cells (e.g., a cell has "收入 " with a trailing space, but your formula uses "收入")
  • Full-width vs. half-width character differences (like ＂收入＂ vs. "收入")
    Test by referencing a known "收入" cell directly in your formula (e.g., =SUMIFS(B2:B100, A2:A100, A5) where A5 is a valid "收入" entry). If this works, you’ve found a text matching issue.

• Ensure income values are formatted as numbers
  If your income column is set to text format instead of numeric, SUMIFS will ignore those values. Fix this by:

  1. Selecting the income column
  2. Right-clicking > Format Cells > choosing "Number"
     Or, in Excel, use -- to convert text to numbers on the fly: =SUMIFS(--B2:B100, A2:A100, "收入")

• Check for hidden/filtered rows
  While SUMIFS normally includes hidden rows in calculations, a stray filter could be excluding income entries without you noticing. Clear any active filters (click the filter icon in the header) and test the formula again.

• Rule out sign mismatches
  If your spreadsheet uses negative numbers for expenses and positive for income, double-check that your income formula isn’t accidentally negating values. For example, don’t use =SUMIFS(-B2:B100, A2:A100, "收入") unless your income values are stored as negatives.

• Simplify the formula for testing
  If your original SUMIFS has multiple criteria, strip it down to the basics first: =SUMIFS(收入列, 类型列, "收入"). If this works, add back your other criteria one by one to identify which one is causing the issue.

Pro tip: Sharing your exact formula and a snippet of your data (redacting sensitive info) would help narrow things down even faster!

内容的提问来源于stack exchange，提问作者Lee-Ann