what is the problem in this formula

Question

=SUMPRODUCT(IF(ISNUMBER(FILTERXML("&lt;t&gt;&lt;s&gt;" &amp; TEXTJOIN("&lt;/s&gt;&lt;s&gt;", TRUE, H9:H100) &amp; "&lt;/s&gt;&lt;/t&gt;", "//s")), FILTERXML("&lt;t&gt;&lt;s&gt;" &amp; TEXTJOIN("&lt;/s&gt;&lt;s&gt;", TRUE, H9:H100) &amp; "&lt;/s&gt;&lt;/t&gt;", "//s"), 0))&nbsp;thank you everyone for help.

sergeibaklan · Answer

areis1643&nbsp;
As variant that could be
=REDUCE(
  0,
  FILTER(range, range &lt;&gt; "*"),
  LAMBDA(a,v,
    a + IFERROR(
      --LEFT( v,
          XMATCH(
            FALSE,
            ISNUMBER(--MID(v, SEQUENCE(LEN(v)), 1) )
          ) - 1
      ), v )
    )
)

sergeibaklan · Answer

areis1643&nbsp;
Nothing is wrong with formula, but it depends on which kind of values you have in H9:H100. Plus it's not necessary to use FILTERXML on 365, TEXTSPLIT could work.

areis1643 · Answer

SergeiBaklanhi thanx for your answer. i want to calculate total amount of yellow colored numbers on the right side of the photo.

sergeibaklan · Answer

areis1643&nbsp;
Do you mean extract numbers from texts and sum them, e.g. 700N, 400N =&gt; 700+400=&gt;1100
or just count number of values which are not equal to "*" ? If so the answer for above will be 2

areis1643 · Answer

Yes I want to extract numbers like this one 700+400=1100

Forum Discussion

what is the problem in this formula

Share

Resources