Excel IF函数公式报错#NAME? 求实现指定逻辑的正确写法

Hey there, let's fix that #NAME? error in your Excel formula right away! The issue comes from two syntax mistakes that Excel can't recognize.

=IF(SUM(E16:E20)>240, E21/0.3, E21)

Why You Got the #NAME? Error & Fixes #

• Mistake 1: Invalid SUM Function Usage
  You wrote sum=E16:E20 which isn't how Excel's SUM function works. To calculate the total of E16 to E20, you need to use the proper function syntax: SUM(E16:E20). The sum= part here is completely incorrect and confuses Excel, leading to the name error.
• Mistake 2: Unnecessary sum= Prefix
  The part (sum=E21/0.3) also has an extra sum= that doesn't belong. The second argument of the IF function just needs the value you want to return when the condition is true—so just E21/0.3 works perfectly.

How This Formula Works #

This formula exactly matches your needs:

1. First, it calculates the sum of cells E16 to E20 using SUM(E16:E20)
2. If that sum is greater than 240, it returns the result of E21 divided by 0.3
3. If the sum is 240 or less, it simply returns the value in E21

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