Excel sum cells with function

Question

I have a cell value like 100/20. I mean this text equals "5". I want to calculate each cell and get sum on bottom.

I calculate a cell like this (just for a cell)

=LEFT( V342;FIND( "/"; V342 ) - 1 ) / RIGHT( V342; LEN( V342) - FIND( "/"; V342 ) )

How can i calculate and sum all cells ?

Answer 1

You can use array version of SUM (confirmed with Ctrl+Shift+Enter):

=SUM(LEFT(A1:A6, FIND("/",A1:A6,1)-1) / (MID(A1:A6, FIND("/",A1:A6,1)+1,50000)))

Or SUMPRODUCT confirmed with Enter:

=SUMPRODUCT(LEFT(A1:A6, FIND("/",A1:A6,1)-1) / (MID(A1:A6, FIND("/",A1:A6,1)+1,50000)))

Answer 2

With data like :

In A1 through A3, in another cell enter:

=SUMPRODUCT(--LEFT(A1:A3,FIND("/",A1:A3,1)-1)/(MID(A1:A3,FIND("/",A1:A3,1)+1,9999)))

In your locale use ; rather than ,