CODEWITHSUNDEEP
excelexcel-formula
Subrat Sahu
Subrat Sahu

Reputation: 81

Excel formula to add numbers stored as text

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)).

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

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!

Upvotes: 2

Views: 6621

Answers (3)

user24462076
user24462076

Reputation: 1

use this formula: =sum(value(__YOUR_ARRAY__))

Upvotes: -1

anumula narendra
anumula narendra

Reputation: 66

the value in the cell must be stored as a number because sum works on numbers only if you give text then it will display #VALUE.

you can store value as numbers using numformat('0.00').you can get more info from this page https://support.office.com/en-us/article/Number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68.if you are reading then you have to convert it.

Could you please let me know which package(exceljs,Node-xlsx) are you using so that i can send you the code require?

Upvotes: 0

Mrig
Mrig

Reputation: 11712

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)

Upvotes: 5

Related Questions