How to sum values of cell presented in different ways EXCEL?

Question

I have 3 type of cells:

• just number
• numbers as a column (in a single cell)
• empty cells

Desired output is: 5 (sum of all numbers by taking into account empty cell as well)

Answer 1

For examle:

Formula in D1:

=SUM(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN(CHAR(10),,A1:C1),CHAR(10),"</s><s>")&"</s></t>","//s"))

Note1: TEXTJOIN requires Excel 2019 or O365, but otherwise you could just concatenate the three cells using CHAR(10).

Note2: FILTERXML requires Excel 2013 or higher.

---

For old versions maybe use:

Formula in D1:

=SUM(IF(ISNUMBER(--A1),--A1,SUM(--MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",LEN(A1))),(ROW(A$1:INDEX(A:A;LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*LEN(A1)+1,LEN(A1)))))

Note: It's an array formula and needs entered through CtrlShiftEnter.

Drag the formula three cells to the right and add the three returned values in a seperate column (I used G1).