Excel SUMPRODUCT函数不区分大小写求和结果异常咨询

Hey there, the problem you're facing is that Excel's default text comparison is case-sensitive. Your original formula only matches entries where the case exactly matches A1, so it's missing those rows in Item_mst where the item is AAA (while A1 is aaa). That's why you're getting 60 instead of the expected 70.

The Fix: Standardize Case Before Matching #

To ignore case when matching items, use either UPPER() or LOWER() to convert both the cell value and the lookup column to the same case. Here's the adjusted formula:

=SUMPRODUCT(Item_mst!$H$1:$H$4,--(UPPER(A1)=UPPER(Item_mst!$B$1:$B$4)))

How It Works #

• UPPER(A1) turns your aaa in Sheet1 into AAA
• UPPER(Item_mst!$B$1:$B$4) converts all entries in Item_mst's B column to uppercase too—so both aaa and AAA become AAA
• The -- converts the TRUE/FALSE match results to 1s and 0s, which SUMPRODUCT uses to multiply against the corresponding values in column H. Summing those gives you the total of all matching entries, regardless of case.

Using your sample data:

• Column H values in Item_mst: 20, 10, 20, 20
• All entries now match after case conversion, so the sum is 20+10+20+20 = 70, which is exactly what you expected.

Quick Note for Future Reference #

If you ever need case-sensitive matching (the opposite of this scenario), you'd use the EXACT() function instead. But for this case, standardizing case is the right approach.

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