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

 

[Shakeable_Drip]

Here other to try:

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

This did it! now I'm going to have to learn substitute and rewrite all my other similar formulas. Would you have the time to explain why the nested substitute works? specifically what does the double negative "--" before mean?

 

[DanteAmor]

Would you have the time to explain why the nested substitute works? specifically what does the double negative "--" before mean?

Of course, I'll explain it to you with the first example: <Repeat> 16 </Repeat>

SUBSTITUTE(A2:A4,"/","")
SUBSTITUTE(<Repeat> 16 </Repeat>,"/","")
Result, Just remove the slash /
<Repeat> 16 <Repeat>

SUBSTITUTE(SUBSTITUTE(<Repeat> 16 </Repeat>,"/",""),"<Repeat>","")
Result:
SUBSTITUTE(<Repeat> 16 <Repeat>,"<Repeat>","")
Remove both <Repeat> then:
16 <--- With whitespace before and after, This is a text, since the previous formulas return a text, so we must convert it to a numeric value to be able to add it.

-- 16
Converts a text into a numeric value, technically multiplies the value *-1 and multiplies the result again by *-1
Continuing with the example it would be:
*-1 *-1( 16 )
*-1 (-16)
16
You will also find something like this:

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

Adds 0 to a text and converts it to a numeric value. You can use any of the 2, with which you feel more comfortable.

-------------------
IFERROR(--SUBSTITUTE(SUBSTITUTE(A2:A4,"/",""),"<Repeat>",""),0)
The IFERROR is used for those with the text <Time>, since by putting -- it returns #Value error, in that case IFERROR changes them to 0.

I hope the explanation helps you to use the formula in other occasions.

 

[Shakeable_Drip]

Of course, I'll explain it to you with the first example: <Repeat> 16 </Repeat>

SUBSTITUTE(A2:A4,"/","")
SUBSTITUTE(<Repeat> 16 </Repeat>,"/","")
Result, Just remove the slash /
<Repeat> 16 <Repeat>

SUBSTITUTE(SUBSTITUTE(<Repeat> 16 </Repeat>,"/",""),"<Repeat>","")
Result:
SUBSTITUTE(<Repeat> 16 <Repeat>,"<Repeat>","")
Remove both <Repeat> then:
16 <--- With whitespace before and after

-- 16
Converts a text into a numeric value, technically multiplies the value *-1 and multiplies the result again by *-1
Continuing with the example it would be:
*-1 *-1(16)
*-1 (-16)
16
You will also find something like this:

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

Adds 0 to a text and converts it to a numeric value. You can use any of the 2, with which you feel more comfortable.

-------------------
IFERROR(--SUBSTITUTE(SUBSTITUTE(A2:A4,"/",""),"<Repeat>",""),0)
The IFERROR is used for those with the text <Time>, since by putting -- it returns error, in that case IFERROR changes them to 0.

I hope the explanation helps you to use the formula in other occasions.

thank you so much! I've always used *1 to convert to numeric, and I just heard about +0 a few days ago but I love the -- Very clear explanation!

 

[Shakeable_Drip]

@DanteAmor ,

I tried applying this method against my data set with a different variable "<count>" but I received a different result and I'm investigating. unfortunately the author has reused this variable. not cool.
in the below example, please ignore "." their presence is to stop the forum software from removing "extra" spaces. I searched my data set for "<count>" and logged the results below, using your formula I got 10022, strange that I didn't get 10850. I would like to get 450.

=SUM(IFERROR(--SUBSTITUTE(SUBSTITUTE(txt_data!A:A,"/",""),"<Count>",""),0))

data set
. <Count> 10000 </Count>	<---- ignore, and static (I could have subtracted this but....)	5spaces
. <Count> 400 </Count>	<---- ignore, and dynamic (this will change)	5spaces
. <Count> 400 </Count>	<---- SUM	7spaces+<count>
. <Count> 50 </Count>	<---- SUM	7spaces+<count>

***edit** I see where I went wrong I was not looking at my refereanced collomn A:A, I was looking at over at E:E, the solution is simple then, fomula changes to the below

=(SUM(IFERROR(--SUBSTITUTE(SUBSTITUTE(txt_data!E:E,"/",""),"<Count>",""),0))-10000)/2

 

[DanteAmor]

strange that I didn't get 10850

The problem is that they are not spaces (character 32), what you have before the <Count> text are control characters (character 160).

These characters, apparently spaces, are a real pain in the head, you must first remove them or replace them with blank characters.
Generally these characters come from when you copy information from the network and paste it into your sheet.

In the network you find several answers to eliminate the character 160.

The formula without those characters returns 10850. I even exaggerated the number of spaces in A2 so that you can see that the result is correct.

ignore, and static (I could have subtracted this but....)

I don't understand that part, if you put the formula to add the entire column A:A then it will also consider A1, A2, A3, A4, etc.
If you don't want to add A1, then start the formula at A2 and up to A1000, for example: