Excel公式添加以文本形式存储的数字 [英] Excel formula to add numbers stored as text

问题描述

我的NodeJS程序在一个单元格中填充一个excel公式，该单元格又​​计算一列中所有数字的总和.数字以文本形式存储在单元格中. 使用的公式为:= SUM(0+(I5:I19999)).

My NodeJS program populates an excel formula in a cell which in turn calculates the sum of all the numbers in a column. The numbers are stored as text in the cells. The formula used is : =SUM(0+(I5:I19999)).

所有数字都显示在"I"列中，但不一定要到第19999行(很少有空白).

All the numbers are present in column 'I' but not necessarily till 19999th row (few may be blank).

但是，此公式的结果始终为#VALUE!. 我似乎找不到这个问题. 非常感谢在此问题上的任何帮助. 谢谢！

However, the result of this formula is always #VALUE!. I can't seem to find the issue in this. Any help in this issue is really appreciated. Thanks!

推荐答案

您用来添加存储为文本的数字的公式，即=SUM(0+(I5:I19999))是一个数组公式，需要通过按 Ctrl 进行提交+ Shift + 输入.

Formula you are using to add numbers stored as text i.e. =SUM(0+(I5:I19999)) is an array formula and needs to be committed by pressing Ctrl+Shift+Enter.

如果要使用非数组公式，可以使用

If you want non-array formula you can use

=SUMPRODUCT((I5:I19999)*1)

这篇关于Excel公式添加以文本形式存储的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！