SUM OF VALUES IN A TEXT STRING, OF A COLUMN

[Shakeable_Drip]

can sumifs with MID-INDEX-MATCH be used to sum target text strings in a column? hopefully without a helper column.
unfortunately, mini table doesn't appear to be working for me, anyways. this adds to a project im working on over here.
the sequence of arguments or criteria is getting murky, maybe there is a better way?

=MID(INDEX(A:A,MATCH("*"&"      <Repeat> "&"*",A:A,0)),SEARCH(">",INDEX(A:A,MATCH("*"&"      <Repeat> "&"*",A:A,0)))+1,SEARCH("</",INDEX(A:A,MATCH("*"&"      <Repeat> "&"*",A:A,0)))-SEARCH(">",INDEX(A:A,MATCH("*"&"      <Repeat> "&"*",A:A,0)))-1)*1

DATA SET	MID FOR "REPEAT"	SUM OF ALL REPEAT
<Repeat> 16 </Repeat>	16	32
<Time> 145383 </Time>	0
<Repeat> 16 </Repeat>	16

 

[DanteAmor]

Try:

=SUM(--TRIM(MID(SUBSTITUTE(FILTER(A2:A4,ISNUMBER(SEARCH("Repeat",A2:A4)))," ",REPT(" ",99)),99,99)))

 

[kweaver]

Here's another way:

Book3
	A	B	C
1	DATA SET	MID FOR "REPEAT"	SUM OF ALL REPEAT
2	<Repeat> 16 </Repeat>	16	342
3	<Time> 145383 </Time>	0
4	<Repeat> 26 </Repeat>	26
5	<Repeat> 100 </Repeat>	100
6	<Repeat> 200 </Repeat>	200
7	<Time> 1111 </Time>	0
Sheet3

Cell Formulas
Range	Formula
C2	C2	=SUM(B2:B7)
B2:B7	B2	=IF(ISNUMBER(SEARCH("<Repeat>",A2)),--TEXTBEFORE(TEXTAFTER(A2,"> "),"<"),0)

 

[kweaver]

A dynamic array also works.

Book3
	A	B	C
1	DATA SET	MID FOR "REPEAT"	SUM OF ALL REPEAT
2	<Repeat> 16 </Repeat>	16	342
3	<Time> 145383 </Time>	0
4	<Repeat> 26 </Repeat>	26
5	<Repeat> 100 </Repeat>	100
6	<Repeat> 200 </Repeat>	200
7	<Time> 1111 </Time>	0
Sheet3

Cell Formulas
Range	Formula
B2:B7	B2	=IF(ISNUMBER(SEARCH("<Repeat>",A2:A7)),--TEXTBEFORE(TEXTAFTER(A2:A7,"> "),"<"),0)
C2	C2	=SUM(B2#)
Dynamic array formulas.

 

[Peter_SSs]

If the sample data is representative, then other options (depending on functions available) are

23 06 03.xlsm
	A	B
1	DATA SET	SUM OF ALL REPEAT
2	<Repeat> 16 </Repeat>	342
3	<Time> 145383 </Time>	342
4	<Repeat> 26 </Repeat>
5	<Repeat> 100 </Repeat>
6	<Repeat> 200 </Repeat>
7	<Time> 1111 </Time>
Sum Repeat

Cell Formulas
Range	Formula
B2	B2	=SUM(--TEXTBEFORE(TEXTAFTER(FILTER(A2:A7,LEFT(A2:A7,2)="<R"),">"),"<"))
B3	B3	=SUM(IFERROR(--SUBSTITUTE(LEFT(A2:A7,FIND("</",A2:A7)-1),"<Repeat>",""),0))

 

[Shakeable_Drip]

If the sample data is representative, then other options (depending on functions available) are

23 06 03.xlsm
	A	B
1	DATA SET	SUM OF ALL REPEAT
2	<Repeat> 16 </Repeat>	342
3	<Time> 145383 </Time>	342
4	<Repeat> 26 </Repeat>
5	<Repeat> 100 </Repeat>
6	<Repeat> 200 </Repeat>
7	<Time> 1111 </Time>
Sum Repeat

Cell Formulas
Range	Formula
B2	B2	=SUM(--TEXTBEFORE(TEXTAFTER(FILTER(A2:A7,LEFT(A2:A7,2)="<R"),">"),"<"))
B3	B3	=SUM(IFERROR(--SUBSTITUTE(LEFT(A2:A7,FIND("</",A2:A7)-1),"<Repeat>",""),0))

this looks like exactly what I need, but I get a #calc! error about empty arrays, is that because I'm trying to do the entire column of A:A or is this one of those equations you need to hold shift and enter<---- that didnt fix it.

 

[Shakeable_Drip]

Try:

=SUM(--TRIM(MID(SUBSTITUTE(FILTER(A2:A4,ISNUMBER(SEARCH("Repeat",A2:A4)))," ",REPT(" ",99)),99,99)))

I like this method too but I got a value is of the wrong type error and I cant figure out what it is

 

[DanteAmor]

Here other to try:

=SUM(IFERROR(--SUBSTITUTE(SUBSTITUTE(A2:A4,"/",""),"<Repeat>",""),0))

 

[Shakeable_Drip]

A dynamic array also works.

Book3
	A	B	C
1	DATA SET	MID FOR "REPEAT"	SUM OF ALL REPEAT
2	<Repeat> 16 </Repeat>	16	342
3	<Time> 145383 </Time>	0
4	<Repeat> 26 </Repeat>	26
5	<Repeat> 100 </Repeat>	100
6	<Repeat> 200 </Repeat>	200
7	<Time> 1111 </Time>	0
Sheet3

Cell Formulas
Range	Formula
B2:B7	B2	=IF(ISNUMBER(SEARCH("<Repeat>",A2:A7)),--TEXTBEFORE(TEXTAFTER(A2:A7,"> "),"<"),0)
C2	C2	=SUM(B2#)
Dynamic array formulas.

Thank you for your post! I am hoping to do this without the helper column B or "mid for repeat"

 

[Shakeable_Drip]

Here other to try:

=SUM(IFERROR(--SUBSTITUTE(SUBSTITUTE(A2:A4,"/",""),"<Repeat>",""),0))

its so simple I could cry lmao. 'll try it against my real data set and see what happens thanks!