# Sum of number on the left in a cell

#### veeejay

##### New Member
In my C column I have some data which looks like this : 0(1), 1(0), 0(0), 2(1), 3(0), 0(0), etc.

I'm trying to sum up the numbers on the LEFT of the parenthesis. This example would nomally return 5.
I tried using this fomula: =sumproduct(--(LEFT(C1:C30),1))) but i get a #VALUE ! error.

Right now I mostly have single digits on the left of the parenhesis and within as well but it's not excluded that I might have double digits in the futur.

Any help?

--BONUS questions : Would it also be possible to count Inside the parenthesis ?
I might be interested in finding out the sum of it too.

Thanks

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Leftmost values sum:

=SUM(--IFERROR(LEFT(C1:C30,FIND("(",C1:C30)-1),0))
with Control+Shift+Enter.

Inside the () values sum:

=SUM(-IFERROR(MID(C1:C30,FIND("(",C1:C30),99),0))
with CSE.

This example would nomally return 5.

Really ?

Shouldn't it be 6 ?
1+2+3

Oops! You got me there!

Leftmost values sum:

=SUM(--IFERROR(LEFT(C1:C30,FIND("(",C1:C30)-1),0))
with Control+Shift+Enter.

Inside the () values sum:

=SUM(-IFERROR(MID(C1:C30,FIND("(",C1:C30),99),0))
with CSE.

Sorry but i'm not sure what CSE is?

In any case for the leftmost value thanks it worked great!

Sorry, CSE is just an abbreviation for Control+Shift+Enter. Glad the leftmost value works for you, it looks for the parenthesis, so it will work if the leftmost value has more than 1 digit.

Alright! It worked! Thanks!

SumProduct may work.

Excel 2010
BC
160(1)
261(0)
3140(0)
42(1)
53(0)
60(0)
70(2)
80(10)
3a
Cell Formulas
RangeFormula
B1=SUMPRODUCT(--(LEFT(C1:C8,1)))
B2{=SUM(--IFERROR(LEFT(C1:C30,FIND("(",C1:C30)-1),0))}
B3{=SUM(-IFERROR(MID(C1:C30,FIND("(",C1:C30),99),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

The reason I went with the array SUM formulas is because if the range has empty cells at the end, the SUMPRODUCT formula will return a #VALUE error, which is the problem the OP mentioned in the first post.

Although something like this might work if there are no gaps in the values:

=SUMPRODUCT(--LEFT(C1:INDEX(C1:C30,COUNTA(C1:C30))))

Replies
7
Views
396
Replies
16
Views
693
Replies
3
Views
222
Replies
2
Views
78
Replies
8
Views
367

1,203,192
Messages
6,054,031
Members
444,696
Latest member
VASUCH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back