Excel公式输出文本转数值：适配Autosum自动求和的修改方案

Got it, the core issue here is that your current formula returns text-formatted numbers (wrapped in double quotes like "1" or "2.6") instead of actual numeric values. Excel treats these as text strings, which is why Autosum can’t recognize them for calculations.

Modified Formula (Returns Numeric Values) #

=IF(ISNUMBER(SEARCH("collect",Q2,1)),1,
 IF(ISNUMBER(SEARCH("dhl",Q2,1)),2.6,
 IF(ISNUMBER(SEARCH("DPD",Q2,1)),1,
 IF(ISNUMBER(SEARCH("inpost",Q2,1)),1.2,
 IF(ISNUMBER(SEARCH("hermes",Q2,1)),0.6,
 IF(ISNUMBER(SEARCH("royal",Q2,1)),0.5,
 IF(ISNUMBER(SEARCH("force",Q2,1)),0.5,
 IF(ISNUMBER(SEARCH("ups",Q2,1)),0.6,
 IF(ISNUMBER(SEARCH("yodel",Q2,1)),0,
 IF(ISNUMBER(SEARCH("collection",Q2,1)),0, NA()))))))))))

Key Changes Explained: #

• Removed all double quotes around numeric values (e.g., changed "1" to 1, "2.6" to 2.6). This tells Excel to treat these as actual numbers, not text strings.
• Replaced the final "No" with NA() (optional but recommended). NA() displays as #N/A for unmatched cases, which Autosum automatically ignores. If you prefer a blank cell instead, use ""—blank cells are also ignored in sums.

Bonus: Simplify with SWITCH (Excel 365/2021+) #

If you’re using a newer Excel version, replace the messy nested IFs with a cleaner SWITCH function. It’s easier to read and maintain:

=SWITCH(TRUE,
 ISNUMBER(SEARCH("collect",Q2)),1,
 ISNUMBER(SEARCH("dhl",Q2)),2.6,
 ISNUMBER(SEARCH("DPD",Q2)),1,
 ISNUMBER(SEARCH("inpost",Q2)),1.2,
 ISNUMBER(SEARCH("hermes",Q2)),0.6,
 ISNUMBER(SEARCH("royal",Q2)),0.5,
 ISNUMBER(SEARCH("force",Q2)),0.5,
 ISNUMBER(SEARCH("ups",Q2)),0.6,
 ISNUMBER(SEARCH("yodel",Q2)),0,
 ISNUMBER(SEARCH("collection",Q2)),0,
 NA())

After updating the formula, your Autosum function will correctly recognize the numeric values and calculate the total as expected.

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