Excel条件求和公式需求：基于指定列值调整数值正负求和

Got it, let's break down how to solve this exactly as you described. You need to calculate a combined total where values in Column I are treated as negative if their matching Column H status is "Complete", and positive for "Incomplete" or "Does Not exist"—then add that adjusted Column I sum to the full sum of Column L.

Formula for Your Core Requirement #

Here's the Excel formula you can drop right into your sheet:

=SUMPRODUCT(IF(H2:H61="Complete", -I2:I61, I2:I61)) + SUM(L2:L61)

How This Works #

Let's unpack the logic step by step:

• SUMPRODUCT handles the row-by-row adjustment for Column I:
  • For every row where H2:H61 equals "Complete", it converts the corresponding I2:I61 value to a negative number
  • For rows with "Incomplete" or "Does Not exist", it keeps the I2:I61 value positive
• SUM(L2:L61) adds the full sum of Column L to the adjusted Column I total, giving you the final combined number

Matching Your Example Scenario #

Let's test this with your sample data:

• H2 = "Complete", I2 = 1.5 → counts as -1.5
• H3 = "Incomplete", I3 = 0.5 → counts as +0.5
• H4 = "Complete", I4 = 2.0 → counts as -2.0

The adjusted Column I sum here is -1.5 + 0.5 - 2.0 = -3.0. If your original combined total (I + L) is 40, that means Column L's sum is 40 - (1.5 + 0.5 + 2.0) = 36. Adding that to the adjusted I sum gives -3.0 + 36 = 33, which aligns perfectly with flipping "Complete" values to negative as your initial requirement states.

Alternative Formula for Your Note's Adjustment Logic #

Your supplementary note mentions adjusting the original 40 total by subtracting "Complete" I values once (instead of flipping their sign). If that's the specific behavior you need, use this formula instead:

=SUM(I2:I61, L2:L61) - SUMIF(H2:H61, "Complete", I2:I61)

This takes your original combined total, then subtracts all I values where H is "Complete"—so 40 - 1.5 = 38.5 (as you noted), then 38.5 - 2.0 = 36.5 for your H4 example.

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