Calculate numbers within one cell, when certain criteria is met

[Microsoft]

Hi Guys,

As the title suggests I would like to perform a sum on a group of numbers, which will be in one cell, with text associated with them (the text lines will always remain the same, example data below)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Data[/TD]
[TD]Answer[/TD]
[/TR]
[TR]
[TD]Rob 5
Jack 10
Stan 5
Denise 17[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The above shows how I would like my calculation to work, if the criteria was to state that the sum should only be performed on the numbers which are on the same line as Rob, and Jack. Stan and Denise's figures should be ignored.

Is this possible to do? Ideally I'd like to avoid VBA as this workbook will be used in O365 by multiple people.

Thanks in advance!

 

[footoo]

Have a look at the SUMIF function.

 

[Microsoft]

A SUMIF isn't possible, as the values are all in the same cell (A1 in the example)

Thank you for your response.

 

[PaddyD]

"...which will be in one celL"

Why? organising your data like that makes things hard...

 

[bosco_yip]

Your example data (assume put in A1) is not consistent, try this formula in B1:

=SUM(0+TRIM(MID(SUBSTITUTE(SUBSTITUTE(" "&A1,CHAR(10)," ")," ",REPT(" ",99)),{2,4}*99,99)))

or this,

=SUM(0+TRIM(MID(SUBSTITUTE(SUBSTITUTE(" "&A2,CHAR(10),"")," ",REPT(" ",99)),{2,4}*99,99)))

Regards
Bosco