Extracting numbers from text in cell range and summing them up using SUMPRODUCT

Hello,

I'm trying to extract hours of education (i.e., 4.58, 2 and 3.5) from cells A2:A6 and add them up in one formula using SUMPRODUCT. I'd like to do all this in one cell only. If I use a helper column B then simple SUM or SUMPRODUCT are straightforward, giving a correct total of 10.08 hours in cell E1. However, if I omit the helper column B, I end up with just 4.58 hours (cell E2). For the life of me, I've not been able to determine how to incorporate the range A2:A6 into the SUMPRODUCT formula in E2 (see below).

Please Login or Register  to view this content.

Please see attached spreadsheet.

Any advice on how to resolve this is more than welcome.

Keep life simple and use two columns.

It is not good practice to use columns (fields) to serve more than one purpose.

Try this ...

=SUM(IFERROR(--REPLACE(SUBSTITUTE(A2:A6,")",""),1,FIND("(",A2:A6),""),0))

Enter with Ctrl+Shift+Enter.

Thank you Phuocam. It works. Much appreciated