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?
CodePudding user response：
Maybe this can help. Typed some words and numbers mixed, and formatted all those cells as text.
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.
CodePudding user response：
One option:
=SUMPRODUCT(IFERROR(--I1:I1000,0))
Depending on your version of Excel, may need confirmed with Ctrl Shift Enter.
Adjust the range as necessary.
CodePudding user response：
Format Strings to Numbers and then use sum formula. Please follow below page.
Or write a vb script to do this. Directly not possible.