CODEWITHSUNDEEP
excelexcel-formula
Zebraboard
Zebraboard

Reputation: 210

Sum of strings in Excel

I'm trying to take the sum of numbers that are formatted as strings in Excel without firstly reformatting them as numbers manually, on row I.

How is this done?

My best attempt so far is

=VALUE(SUM(I:I))

But does not work :( Any ideas?

Upvotes: 1

Views: 2117

Answers (2)

BigBen
BigBen

Reputation: 49998

One option:

=SUMPRODUCT(IFERROR(--I1:I1000,0))

enter image description here

Depending on your version of Excel, may need confirmed with Ctrl+Shift+Enter.

Adjust the range as necessary.

Note: =SUM(IFERROR(--I1:I1000,0)) is a valid (and shorter!) option.

Upvotes: 3

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

Maybe this can help. Typed some words and numbers mixed, and formatted all those cells as text.

enter image description here

My formula in C13 is an array formula:

=SUM(IF(ISNUMBER(VALUE(A1:A10))=TRUE;VALUE(A1:A10)))

Because it's an array formula, it must be entered with CTRL+ENTER+SHIFT

IMPORTANT: Sometimes when referencing cells formatted as text in a formula, the active cell gets formatted to text. So make sure the cell contanining the formula (in my image, C13) is formatted as Standard.

Upvotes: 2

Related Questions