Sum of All Numbers Inside the Parenthesis

[enzocontreras]

Hello Guys!
I'm currently working on a worksheet and i need help on the formula. Im trying to get the guest count from the long list given to me in an excel sheet where name of guest and head count is written together in 1 cell and in a single column.

1) I would like to get the sum of all the numbers inside the parenthesis..
Example:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">RIZA SMITH (2A/1C) = 3
LANIE ADAMS (2A) = 2
STEVE ANDREWS (1A/3C) = 4

TOTAL GUEST COUNT : = 9</code>2) or just autosum all the numbers inside the parenthesis in a column

Hope you guys can help me.. Thanks in advance! =)

 

[sandy666]

with your example (I assume that numbers are only between brackets, if not it will sum all numbers in the string)


[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]RIZA SMITH (2A/1C)[/td][td]

3​

[/td][td]=SUMPRODUCT(--(0&MID(A1,ROW($1:$100),TEXT(FREQUENCY(-ROW($1:$99),-ISERR(-MID(0&A1,ROW($1:$99),1))*ROW($1:$99))-1,"[<]\0"))))[/td][/tr]

[tr=bgcolor:#FFFFFF][td]LANIE ADAMS (2A)[/td][td]

2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]STEVE ANDREWS (1A/3C)[/td][td]

4​

[/td][td][/td][/tr]
[/table]

 

[sandy666]

or a little longer vwersion:

=SUMPRODUCT(--(0&MID(MID(A1,SEARCH("(",A1),SEARCH(")",A1)-SEARCH("",A1)),ROW($1:$100),TEXT(FREQUENCY(-ROW($1:$99),-ISERR(-MID(0&MID(A1,SEARCH("(",A1),SEARCH(")",A1)-SEARCH("",A1)),ROW($1:$99),1))*ROW($1:$99))-1,"[<]\0"))))

limited to ( )

 

[enzocontreras]

with your example (I assume that numbers are only between brackets, if not it will sum all numbers in the string)


[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]RIZA SMITH (2A/1C)[/TD]
[TD]

3​

[/TD]
[TD]=SUMPRODUCT(--(0&MID(A1,ROW($1:$100),TEXT(FREQUENCY(-ROW($1:$99),-ISERR(-MID(0&A1,ROW($1:$99),1))*ROW($1:$99))-1,"[<]\0"))))[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]LANIE ADAMS (2A)[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]STEVE ANDREWS (1A/3C)[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much! the formula works!

 

[sandy666]

You are welcome
look at the comment for the first formula, and if there will be any number outside the brackets - use second.

Have a nice day