Excel中用单元格引用实现SUMIFS多条件或逻辑时公式报错，如何解决？

Hey there! I see the issue you're facing—when you swap out the hardcoded constant array {"Apples","Oranges"} for {"D4","D5"}, Excel doesn't recognize those as cell references. Instead, it treats them as literal text strings (looking for cells in column B that equal the text "D4" or "D5"), which is why your formula breaks.

Here's how to fix it properly:

Solution 1: Use a Cell Range Directly #

Instead of wrapping cell references in a curly-brace array, pass the actual cell range to SUMIF. This works because SUMIF can accept a range of conditions and returns an array of sums (one for each condition), which SUM then adds together.

Your corrected formula would be:

=SUM(SUMIF(B:B, D4:D5, C:C))

• For older Excel versions (pre-365/2021): You'll need to enter this as an array formula by pressing Ctrl+Shift+Enter instead of just Enter. Excel will automatically add curly braces around it (don't type them manually!).
• For modern Excel (365/2021+): Dynamic arrays handle this automatically—just press Enter and it works.

Solution 2: SUMPRODUCT for Cross-Version Compatibility #

If you want a formula that works across all Excel versions without needing array entry, use SUMPRODUCT instead of SUM:

=SUMPRODUCT(SUMIF(B:B, D4:D5, C:C))

This achieves the exact same result, but doesn't require any special keystrokes.

Why Your Original Broken Formula Failed #

When you wrote {"D4","D5"}, you created an array of text values, not cell references. Excel was trying to match cells in column B to the literal strings "D4" and "D5", not the values stored in cells D4 and D5. That's why it returned incorrect results or errors.

Let's say D4 contains "Apples" and D5 contains "Oranges"—the corrected formulas will look for those values in column B, sum the corresponding C:C values for each, then add those two sums together. Perfect for your "or" condition!

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