Forum Discussion
what is the problem in this formula
=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 ...
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
Yes I want to extract numbers like this one 700+400=1100
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 ) ) )...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))))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.
