Forum Discussion

areis1643's avatar
areis1643
Copper Contributor
Feb 28, 2024

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 ...
excel
Formulas and Functions
Macros and VBA
areis1643's avatar
areis1643
Copper Contributor
Feb 28, 2024

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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 28, 2024

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

  • areis1643's avatar
    areis1643
    Copper Contributor
    Feb 28, 2024
    Yes I want to extract numbers like this one 700+400=1100
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 28, 2024

      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 )
    )
)
      • Balint79's avatar
        Balint79
        Brass Contributor
        Mar 01, 2024

        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))))

         

Resources