How to extract all numbers ending in a certain string from cell, and sum them together-CodePudding

So in a table form, here is what I want to achieve (fill in the Total column automatically summing up all numbers that have exactly h behind them, preferably with arrayformula.

Stuff	Total
Apples 1,5h, Banana 2x, Oranges 3h	4,5h
5 Pears 1h, 2 Kiwis 1x, Melon 4h, 2h	7,0h

Stuff 1g, Stuff 2 1,5h, Stuff 3 2j	1,5h

I've tried everything I can come up with, and I'm outta luck. I can get to a point where I have just the correct numbers as a split with

=arrayformula( if( len( A2:A );
      substitute( substitute(
        iferror(
          if(
            regexmatch( iferror( split(A2:A; " ") ); "[0-9]*[,]?[0-9]h " );
            iferror( split(A2:A; " ") );
          )
        );
      "h," ;"" ); "h"; "" ) 
; "") )

But I don't know where to go from there. I've tried summing the numbers up in a million ways with mmult but always end up short somehow.

Oh, note that I'm using ; to separate commands and , as a decimal separator, regional things and shouldn't matter in your answers.

Doing this thing in script was quite trivial, but usage of it is slow and it updates all the cells every time you add a row. I'm sure it's doable as a cell formula too so just eager to know how. Thanks in advance.

CodePudding user response：

try:

=ARRAYFORMULA(IF(A1:A10="",,TEXT(MMULT(IFERROR(SUBSTITUTE(
 REGEXEXTRACT(SPLIT(A1:A10, ", ", ), "(\d (?:.\d )?)h"), ",", "."))*1, 
 SEQUENCE(COLUMNS(SPLIT(A1:A10, ", ", )), 1, 1, 0)), "0.0\h")))

for non-english sheets go for:

=ARRAYFORMULA(IF(A1:A10="";;TEXT(MMULT(IFERROR(
 REGEXEXTRACT(SPLIT(A1:A10; ", "; ); "(\d (?:.\d )?)h"))*1; 
 SEQUENCE(COLUMNS(SPLIT(A1:A10; ", "; )); 1; 1; 0)); "0.0\h")))