Forum Discussion
areis1643
Feb 28, 2024Copper Contributor
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 ...
SergeiBaklan
Feb 28, 2024MVP
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 )
)
)
Balint79
Mar 01, 2024Brass Contributor
...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))))
- SergeiBaklanMar 01, 2024MVP
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.