Formula Help: Summing numbers from text cells, that occur before/after specific characters

[Four Fried Chickens]

Hi all,

I need help creating different formula strings that can return a sum from text cells that contain multiple different numbers. Not the best way to collect data, I know, but until our org develops skills in Microsoft Access this is a direction we're trying (need to make it easy for many users, put all the data in one spot, etc.).

Here's an example:

Event	Johnny	Suzie	Billy	Sums: # committed to invite (need help)	Sums: # invited (need help)	Sums: # responded yes (need help)
Midnight Jamboree	Attending Invited 5/5 1 yes 2 no 2 unknown	Attending Invited 0/5	Attending Invited 1/1 1 yes
Monster Mash	Can't be there	Attending Invited 2/5 2 no	Attending Invited 3/7 2 yes 1 no
Time Warp	Can't be there Invited 1/1 1 yes	Can't be there	Attending Invited 3/4 3 unknown

I've been working with SUM, LEFT, MID, LEN, RIGHT, FIND, SEQUENCE, and VALUE functions. I've gotten close, but I'm at the point where I need to ask for help.

Column E (# committed): Need to extract and sum all numbers preceding "/" (or after "Invited")
Column F (# invited): Need to extract and sum all numbers directly following "/" on the second line
Column G (# yes): Need to extract all numbers that directly precede the word "yes"

All numbers will be one digit 98% of the time. There's a chance there would occasionally be two digits - but if the solution can only be achieved by harvesting a single digit, that would be ok, we can manually add a 10 in those cases.

Thanks!

 

[ExceLoki]

try this
--------------

Book1
	A	B	C	D	E	F	G
1	Event	Johnny	Suzie	Billy	Sums: # committed to invite (need help)	Sums: # invited (need help)	Sums: # responded yes (need help)
2	Midnight Jamboree	Attending Invited 5/5 1 yes 2 no 2 unknown	Attending Invited 0/5	Attending Invited 1/1 1 yes	6	11	2
3	Monster Mash	Can't be there	Attending Invited 2/5 2 no	Attending Invited 3/7 2 yes 1 no	5	12	2
4	Time Warp	Can't be there Invited 1/1 1 yes	Can't be there	Attending Invited 3/4 3 unknown	4	5	1
Sheet2

Cell Formulas
Range	Formula
E2:E4	E2	=SUM(VALUE(IFERROR(RIGHT(TEXTBEFORE(B2:D2,"/",1),1),0)))
F2:F4	F2	=SUM(VALUE(IFERROR(LEFT(TEXTAFTER(B2:D2,"/",1),1),0)))
G2:G4	G2	=SUM(VALUE(IFERROR(RIGHT(TEXTBEFORE(B2:D2," yes",1),1),0)))

 

[Dave Patton]

Try the following
I recommend that you do not use merged cells.
N.B. You can post an extract of your sheet with the forum's tool named Xl2BB.

TextBefore.xlsm
	A	B	C	D	E	F	G
1
2
3	Event	Johnny	Suzie	Billy	Sums: # committed to	Sums: # invited	Sums: # responded yes
4					invite (need help)	(need help)	(need help)
5	Midnight Jamboree	Attending	Attending	Attending	6	11	2
6		Invited 5/5	Invited 0/5	Invited 1/1
7		1 yes 2 no 2 unknown		1 yes
3c

Cell Formulas
Range	Formula
E5	E5	=TEXTAFTER(TEXTBEFORE(B6,"/"),"Invited ")+TEXTAFTER(TEXTBEFORE(C6,"/"),"Invited ")+TEXTAFTER(TEXTBEFORE(D6,"/"),"Invited ")
F5	F5	=TEXTAFTER(B6,"/")+TEXTAFTER(C6,"/")+TEXTAFTER(D6,"/")
G5	G5	=TEXTBEFORE(B7," yes")+IFERROR(TEXTBEFORE(C7," yes"),0)+TEXTBEFORE(D7," yes")

 

[ExceLoki]

here's another version with the merged cells
-----------------

Book1
	A	B	C	D	E	F	G
13	Midnight Jamboree	Attending	Attending	Attending	6	11	2
14		Invited 5/5	Invited 0/5	Invited 1/1
15		1 yes 2 no 2 unknown		1 yes
16	Monster Mash	Can't be there	Attending	Attending	5	12	2
17			Invited 2/5	Invited 3/7
18			2 no	2 yes 1 no
19	Time Warp	Can't be there	Can't be there	Attending	4	5	1
20		Invited 1/1		Invited 3/4
21		1 yes		3 unknown
sum numbers from text

Cell Formulas
Range	Formula
E13,E16,E19	E13	=SUM(VALUE(IFERROR(LEFT(TEXTAFTER(B14:D14," ",1),1),0)))
F13,F16,F19	F13	=SUM(VALUE(IFERROR(LEFT(TEXTAFTER(B14:D14,"/",1),1),0)))
G13,G16,G19	G13	=SUM(VALUE(IFERROR(RIGHT(TEXTBEFORE(B15:D15," yes",1),1),0)))