Summing And Joining Numbers In Brackets

**Lucas75** · 10-13-2011, 06:11 PM

Hey guys, just going to state first off i'm really sorry for my ignorance. My grasp on excel is limited at best and i'm only familiar with limited formulas. Have a one-time assignment at a new job that i am having trouble completing. What i have is data in the form:

placeholder 1 (22)2
placeholder 2 (1)1
placeholder 3 (15)2

etc. etc. - a number inside the brackets and a number outside, but in a cell separate from the name. I need a formula that will sum them all and display it in the same format ie. (38)5 for the example given. I am completely lost on this. It's a one-time thing though so i'm eager to get this taken care of. Anyone's help would be much obliged.

**tom1977** · 10-13-2011, 06:25 PM

look at attachment

regards

**tom1977** · 10-13-2011, 06:29 PM

with array formulas

**daddylonglegs** · 10-13-2011, 06:31 PM

Hello Lucas75, is the data (22)2 in a separate cell? Are there limits to the number lengths, will the the number outside the brackets always be a single digit, what about inside, is that a maximum of 2 or is there no maximum?

**Lucas75** · 10-13-2011, 06:35 PM

Thanks for your help Tom, i at least understand what you did there... I think haha, but the problem remains that they need to be summed and displayed in the same cell, with the same format as they appear. So i need the cell directly beneath them to read (38)5.

**Lucas75** · 10-13-2011, 06:36 PM

Originally Posted by daddylonglegs

Hello Lucas75, is the data (22)2 in a separate cell? Are there limits to the number lengths, will the the number outside the brackets always be a single digit, what about inside, is that a maximum of 2 or is there no maximum?

The digits will never exceed two but could fluctuate between 1-2 on either side of the brackets depending on the inputs

**daddylonglegs** · 10-13-2011, 06:46 PM

Do you mean you could have 12(34) and/or (12)34? What about 1(23)4?

With data in A1:A3 try this formula

=TEXT(SUMPRODUCT(MID(A1:A3,FIND("(",A1:A3),FIND(")",A1:A3)-FIND("(",A1:A3)+1)+0),";(0)")&SUMPRODUCT(REPLACE(A1:A3,FIND("(",A1:A3),FIND(")",A1:A3)-FIND("(",A1:A3)+1,"")+0)

**Lucas75** · 10-13-2011, 07:10 PM

In my testing here at home, that works wonderfully, thanks for your help. It really takes a certain type of brain to just not get lost somewhere on your formula journey, and i don't have it. You have my thanks and my respect, the response on this board was amazingly helpful and fast, this could be the best forum, of any type, that i've seen.