The 2024 Developer Survey results are live! See the results

# 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,406

### 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), ...
• 15.1k

### 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(...
• 6,417
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 ...
• 6,417

### Insert space after every character of a text string

I solved my problem with the following formula: =TRIM( REGEXREPLACE( A1, "(.)", "\$1 ")) Credit to @Erik Tyler for suggesting the TRIM function which provided a much ...

### What's the difference between using "<>"&"" vs. "<>" in Google Sheets formulas?

TL;DR No difference. &"" is redundant. A.  They are equivalent The formulas ="<>"&"" and ="<>" both return <>. This is necessarily so ...
• 6,417

### 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,230

### 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_(&...
• 15.1k

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

### Return the most recent unit price of a matching item that is dated on or before a specified lookup date

VLOOKUP =VLOOKUP(A2, SORT(FILTER(Orders!A:C, Orders!B:B=B2)), 3, 1) FILTER  SORT  VLOOKUP FILTER Orders sheet for rows where item is "sand" and SORT by date. Use VLOOKUP to match the date ...
• 6,417

### Google Sheets Formula to find specific months

Pointing to the red triangle in the cell holding the formula, a popup details the error. Screenshot showing error popoup Error Array arguments to SUMIFS are of different size Putting the SUMIFS ...

### Google Sheets Formula to find specific months

SUMPRODUCT SUMPRODUCT is a single array function that avoids the need to combine SUMIF with ARRAYFORMULA. =SUMPRODUCT( Expenses!E4:E, Expenses!D4:D>=DATE(E3, C3, D3), Expenses!D4:\$D<=...
• 6,417

### What is the syntax for an indefinite range?

Your question wanders a bit but, in Google Sheets, you can reference the range "all the cells from B3 down to the bottom of column B, wherever that may be" as B2:B. In your example, it would ...
Accepted

### Formula that looks for any matching substrings between two cells

One way to do that is to create a regular expression, like this: =regexmatch(A1, substitute("\b(" & B1 & ")\b", " ", "|")) When B1 is foo bar, the ...
• 15.1k

The easiest way to use formulas in a table in Google Docs is to build your table in Sheets and then copy it over, keeping it linked when prompted. Here's an example where I copied three different ...
• 1,497
Accepted

### Selecting only rows corresponding to Thursday in a column of dates

As a formula: =filter(A2:A,weekday(A2:A)=5)) Using the filter option in the taskbar: Use 'filter by condition'/'custom formula is' and set the formula to =weekday(A2:A)=5
• 1,230
Accepted

### Stuck at understanding how duplicate prevention formula works in Google Sheets

The range reference \$A\$2:A2 is using a mixed address mode where \$A\$2 is absolute and A2 is relative. When the formula is "dragged down" by copy-pasting cells or applying it to a range in a ...
• 15.1k
Accepted

### SUMIF Not working in Google sheets

If your spreadsheet is in a locale that uses comma as decimal mark, use semicolon ; as formula argument separator instead of comma ,, like this: =sumif(E1:E; "A"; I1:I) Use File > ...
• 15.1k
Accepted

### Google sheets and semi-autopopulation of data for attendance of a private school where students come two days a week and get to choose the day

Formula in 'attendance list'!A2 =TOCOL( INDEX('student list'!A2:E,, MATCH(A1, 'student list'!A1:E1,0)), 1) If you prefer to have the results returned as a row, replace the TOCOL function ...
• 6,417

### How to set a minimum value for a cell in google sheets?

Use Data > Data validation > Is greater than or equal to > 20.
• 15.1k

### Why is cell that contains TRUE not equal to "TRUE"?

The Boolean value true is different from the text string "TRUE". Use proper Boolean literals, like this: =and(M2 = false, N2 = true, O2 = "A", P2 = "No") ...or, more ...
• 15.1k
Accepted

### Count cells with an integer in them as substring

Formula Option 1 =SUMPRODUCT(REGEXMATCH("'" & A2:A88, "5")) # Equivalent to =ARRAYFORMULA(SUM(REGEXMATCH("'" & A2:A88, "5"))) Formula Option 2 =...
• 6,417

### Googlel Sheets formula to SUMIFS with multiple AND and OR criteria

You would need more than one cell/column depending on how you implement. I'm assuming you want both bulletpoints as requirnments, as well as the ability to switch what they are searching for in the ...
• 139

### Generate formula programmatically

From the question Is there a way to "refresh" Google Sheet to let him interpret the formula properly? You might try to use find and replace to remove the parts of your "formula" ...
Accepted

### Generate formula programmatically

Overview You paste a text string, not a formula, into another cell and Sheets sets the cell's number format to match the value. You need to trigger a re-evaluation of the cell contents by Sheets ...
• 6,417
Accepted

### Combine two SUMIF formulas into a single formula

# Your Formula with SUMIFS =SUMIFS(I:I; F:F; "Word"; B:B; ">=2024-01-01") SUMIFS Function # Syntax =SUMIFS(sum_range; criteria_range1; criterion1; [criteria_range2; ...]; [criterion2;‎ ...]) # North ...
• 6,417

### 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, ...
• 6,417
Accepted

### How to concatenate all further columns from duplicated items of a first column?

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("-",...
• 6,417