Excel nesting formula with SUM

Question

I am trying to have a cell that has the sum of all the value in the row. But my value cell is nested with Date ; Value, in which I used the Right function along with LEN and SEARCH to single out the value. My question here is that is it possible to have some type of function for me to add it all together, instead of having to repeat this until AQ, like how I am having it in the attached picture?

Thanks in advance!

Answer 1

Or using Sumproduct+Replace function

In C2, enter formula :

=SUMPRODUCT(0+REPLACE(D2:E2,1,FIND(";",D2:E2),""))

Answer 2

Use SUMPRODUCT:

=SUMPRODUCT(--RIGHT(D2:E2,LEN(D2:E2)-SEARCH(";",D2:E2)))

You can also just use SUM, which depending on your version of Excel may need to be confirmed with Ctrl+Shift+Enter:

=SUM(--RIGHT(D2:E2,LEN(D2:E2)-SEARCH(";",D2:E2)))