CODEWITHSUNDEEP
excelvbasumformula
Tabaraei
Tabaraei

Reputation: 495

Excel Formula - SUM LEFT on non-blank cells

In the following table at column E, I'm trying to extract the number before parentheses (which is 4 and 5 here) and then return the total summation on extracted values (which is 4 + 5 = 9 here)

The main problem is that the LEFT function can't handle blank cells, So I combined it with the IF function to replace blank cells with "0". But somehow the LEFT function replaces "0" with blank again and it doesn't work.

Any suggestion for proper formula to "sum left number in column"?

enter image description here

Upvotes: 0

Views: 549

Answers (3)

JvdV
JvdV

Reputation: 75870

You can use:

=SUMPRODUCT(--IFERROR(LEFT(E23:E25,FIND("(",E23:E25)-1),0))

enter image description here

Upvotes: 3

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

You can use below approach to bypass the error.

=SUMPRODUCT(--LEFT("0"&E23:E25,FIND("(",E23:E25&"(")))

Here we prefix the numbers with 0 which has no effect on value per your data and then we suffix the data with extra "(" which works for blank cells when searching using FIND function.

Upvotes: 3

andykanu
andykanu

Reputation: 36

LEFT extracts the number from your cell as text. To convert it back to a number, you can use the VALUE function.

Taking your example. the following formula works for me:

=SUMPRODUCT(IF(E23:E25="",0,VALUE(LEFT(E23:E25,1))))

A warning: this will only work for single digits because it takes the first character in the cell. You could add some if statements to check if there are spaces following the first character if you need to consider numbers greater than 10 for this formula.

Upvotes: 0

Related Questions