what is the problem in this formula

[areis1643]

=SUMPRODUCT(IF(ISNUMBER(FILTERXML("<t><s>" & TEXTJOIN("</s><s>", TRUE, H9:H100) & "</s></t>", "//s")), FILTERXML("<t><s>" & TEXTJOIN("</s><s>", TRUE, H9:H100) & "</s></t>", "//s"), 0))

thank you everyone for help.

[#2]

@areis1643 

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.

[#3]

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

[#4]

@areis1643 

Do you mean extract numbers from texts and sum them, e.g. 700N, 400N => 700+400=>1100

or just count number of values which are not equal to "*" ? If so the answer for above will be 2

[#5]

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

[#6]

@areis1643 

As variant that could be

=REDUCE(
  0,
  FILTER(range, range <> "*"),
  LAMBDA(a,v,
    a + IFERROR(
      --LEFT( v,
          XMATCH(
            FALSE,
            ISNUMBER(--MID(v, SEQUENCE(LEN(v)), 1) )
          ) - 1
      ), v )
    )
)

[#7]

I've found it helpful to ask these "what's wrong" questions to chat GPT and see what it gives you. It's not always perfect, but it's an immediate response that you can try out.

[#8]

@SergeiBaklan

...never used before the "--" in the meaning of "*1" thank you for tip. my version is slightly different and also a bit uglier

=SUM(
MAP(range;
LAMBDA(iter;
IFERROR(
LEFT(iter;
MAX(
IFERROR(
XMATCH(
SEQUENCE(10;;0)&"";
MID(iter;
SEQUENCE(
LEN(iter);;1);1);0;-1);0)))*1;0))))

[#9]

@Balint79 

Thanks for sharing. Didn't test, but I guess your variant takes all text numbers into account, i.e. if the have 1000 QNB2 the latest 2 won't be ignored.

As for the double negations, aka double dashes, that's more or less standard option The double negative in Excel formulas | Exceljet . I personally prefer to use it instead of 1* or +0 or VALUE(), but that's each person choice. Everything in Excel could be done by several ways.