CODEWITHSUNDEEP
excelexcel-formula
Mennan
Mennan

Reputation: 4497

Excel sum cells with function

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 ?

enter image description here

Upvotes: 3

Views: 94

Answers (2)

BrakNicku
BrakNicku

Reputation: 5990

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)))

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96791

With data like :

enter image description here

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)))

enter image description here

In your locale use ; rather than ,

Upvotes: 1

Related Questions