# Tag Info

### Converting "4.8 million" into an actual number

You can use a formula like this one: =IF(A1="","",IF(ISNUMBER(A1),A1,VALUE(REGEXEXTRACT(A1,"[0-9\.]*"))*IF(REGEXMATCH(A1,"million"),1000000,IF(REGEXMATCH(A1,&...
• 1,366

### Formula to count rows between positive values

Use reduce(), like this: =reduce(tocol(æ, 2), A2:A, lambda(a, c, let( na, "NA", pattern, "[" & na & "]*\$", streak, regexextract(join("", a), ...
• 14.5k
Accepted

### Converting "4.8 million" into an actual number

Optimized Array Formula This formula is faster than my comparable array formula at the end. I favor shorter code where possible but apparently, that doesn't always go hand-in-hand with shorter ...
• 5,150

### Formula to count rows between positive values

doubleunary's formula is a good one. If you don't want an array formula, here is a single formula that can be copied down column B stating in B1: =IF(A1>0, IF(ROW()<>1, ROW()-IFNA(XMATCH(...
• 5,150

### Formula to count rows between positive values

This is "amateur standard" (my level) but may fill the bill. Formula for cell [B 1]: =if(A1<0,"NA",row()-0) Formula for cell [B 2]: =if(A2<0,"NA",row()-sum(\$B\$1:\$B1)...
• 151

### Formula to calculate the sum of consecutive 0's and -1's

Here's an alternative simpler answer, if you are interested: =min(scan(0,A1:A10,lambda(acc,arr,ifs(arr=-1,acc-1,arr=0,acc,true,0)))) scan the array, subtracting 1 from the accumulator each time a '-1'...
• 1,140

### alphanumerics in duration in sheets - how to convert to a number of minutes

Use regexextract(), like this: =let( get_, lambda(s, ifna(regexextract(A2, "(\d+)" & s), 0)), duration, value(join(":", get_("h"), get_("m"), get_(&...
• 14.5k

### Multiply/Concatenate String Arrays

This formula is very efficient in case you need to use larger data sets: LET(a,A1:A, b,B1:B, c,C1:C, qr,LAMBDA(q, FILTER(q,q<>"")), calc,LAMBDA(x,y, TOCOL(TOROW( BYROW(x, ...
• 5,150
Accepted

### Can an Google Sheet Filter function base its include on a whole row?

Formula =LET(arr,QUERY(A1:C, "WHERE Col1 <> ''", 1), itm,INDEX(arr,,1), kcols,CHOOSECOLS(arr, SEQUENCE(1,COLUMNS(arr)-1,2)), key,BYROW(kcols, LAMBDA(r, TEXTJOIN("-",...
• 5,150
1 vote

### I wanted to set the maximum value of each unique input as active

Put the following formula in C1 (headings row) so that your data and any manipulation don't interfere with it: ={"Status"; LET(rng,A2:B, ARRAYFORMULA( IF( COUNTIF( ...
• 5,150
1 vote
Accepted

### How do I make dropdown selections add value to a totals column in Google Sheets

One way is to use map() and ifs(), like this: =sum(map(D2:2, lambda(week, ifs( week = "N/A", 0, week = "", 0, week = "Week 4", 10, week = "Week 7", ...
• 14.5k
1 vote
Accepted

### Formula to calculate the sum of consecutive 0's and -1's

=LET(x,TOCOL(A1:A, 1), -MAX( INDEX( LEN( SPLIT( JOIN(, IF(x<-1, 2, IF(x>0, 2, SUBSTITUTE(ABS(x), 0,)))), 2))))) As a ...
• 5,150
1 vote

### Multiply/Concatenate String Arrays

You can create a cartesian product by just concatting (&) first array with the TRANSPOSEd second array and then FLATTENning the result: =LET( cart_prod, LAMBDA( a, b, ...
• 3,352
1 vote
Accepted

### Multiply/Concatenate String Arrays

You are asking how to create a Cartesian product of three sets. Assuming the data is in A2:C, try something like this: =let( table, A2:C, blank, iferror(ø), first_, lambda(array, tocol(...
• 14.5k

Only top scored, non community-wiki answers of a minimum length are eligible