# Tag Info

### Decompose a column into multiple columns with TRUE/FALSE values instead

First Formula =ARRAYFORMULA( VSTACK( HSTACK("Name", "Present", "Absent"), HSTACK(A1:A10, IF(B1:B10="Present",true,), IF(B1:B10=&...
• 5,116

### Highlighting only text for google sheets

Partial text formatting is available when you are editing text in cell. Select a run of text with the mouse or arrow keys, then use format buttons in the toolbar or the usual shortcut keys to set text ...
• 14.5k

### Sorting a range of data based on a column within that data

To sort column A by column P, use sort(), like this: =sort(A2:A, P2:A, false) To get just a limited number of results, use sortn(). See sort() and sortn().
• 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,116

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

### COUNTIF with function in criterion

TL;DR =COUNTIF( INDEX(DATEVALUE(A1:A5)), ">0" ) =COUNTIF(BYROW(A1:A5, LAMBDA(r, ISDATE(r))), TRUE) BYROW  COUNTIF  DATEVALUE  INDEX  ISDATE  LAMBDA ISDATE & COUNTIF =COUNTIF(range, ...
• 5,116

### How to change Google Sheets interface language?

Just add ?hl=en after /edit in the URL. You can also use other two-letter language codes
• 276

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

### 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 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
1 vote
Accepted

### Finding preceding n results from a list based on conditions in Google Sheets

TL;DR OFFSET Formula: =OFFSET(A3:A, MATCH(C3, A3:A, 0)-D3-1, 0, D3) Handle case where requested number exceeds available: =LET(m,MATCH(C3, A3:A, 0)-1, IF(m=0, "first_value", OFFSET(A3:A, MAX(m-...
• 5,116

### How to combine two different columns

I tried doing ={(J2:J); (I2:I)} but it doesn't work. It isn't working because combining the two columns creates a column that is slightly less than double the height of your current spreadsheet. You ...
• 5,116

### In Google Sheets search for a string in a column, then return the row where found

As you suspected you need an array formula to return multiple matches. There are many ways to achieve this. You can wrap a non-array function in an array function such as ARRAYFORMULA or INDEX to ...
• 5,116

### Finding preceding n results from a list based on conditions in Google Sheets

Use match(), filter() and isbetween(), like this: =let( data, A3:A, loc, match(C3, data, 0), filter( data, isbetween( sequence(rows(data)), loc - D3, loc, true, false ...
• 14.5k

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

You can do that with map(), ifs() and filter(), like this: =ifna(map(A1:A, B1:B, lambda(a, b, ifs( row(a) = 1, "Status", b = "", iferror(ø), b = max(filter(B1:B, A1:A = a))...
• 14.5k
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,116

### Check if a string appears within another cell, and return the longer of the 2

=LET(data,{B1; A:A}, arr,SORT(data, INDEX(LEN(data)), 0), SORTN(arr,1,0, REGEXMATCH(arr, B1),0)) data stores your lookup range A:A as well as your lookup value B1. By including B1 in data it ...
• 5,116

### Google Sheets VLOOKUP with wildcards on range

Use REGEXEXTRACT to pull out the correct lookup string prior to doing the VLOOKUP. Place formula in B2 (not B3) so that you can sort your data rows without disturbing it. (?i) makes the regex case ...
• 5,116

### Google Sheets countif range with date/timestamp

This is an excellent candidate for using the HOUR function in an ARRAYFORMULA and COUNTIF to count how many of the times are in which hour. Basic Formula =ARRAYFORMULA( COUNTIF( HOUR(TOCOL(A:...
• 5,116

### Inverse column and row relationship

Here's one way to unpivot the data with lambda functions: =reduce({"Team","Manager"},A1:C1,lambda(a,l,vstack(a,map(tocol(filter(A2:C, A1:C1=l),1),lambda(v,{v,l}))))) Many answers ...
• 14.5k
1 vote
Accepted

### Inverse column and row relationship

The following will work if used on a different sheet to your source table (i.e. I am assuming that your source table is in tab 'Sheet1' and starts at A1): =arrayformula(query(split(tocol(Sheet1!A2:ZZZ&...
• 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

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

Using REGEXMATCH # SYNTAX =REGEXMATCH(text, regular_expression) Single Row Formula Needs to be copied down for each row. =INDEX( SUM( REGEXMATCH(D2:E2, "4|7") * 10, REGEXMATCH(...
• 5,116
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
Accepted

### Return the percentage of the month that has elapsed up to today's date

Use DAY w/ EOMONTH NA Locale =DAY(TODAY())/DAY(EOMONTH(TODAY(),0)) International =DAY(TODAY())/DAY(EOMONTH(TODAY();0)) Format the cell's number format as percentage % EOMONTH function Returns a ...
• 5,116
1 vote

### How do I change a cell to the color of the hexadecimal value of a cell in Google Spreadsheets?

This formula has worked for me: =SPARKLINE(1, {"charttype", "column"; "color", A1})

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

### Formula to return a value when the adjacent column's text contains any value from an array of text strings

This is an array formula that will return all the values or a blank for an entire column. =INDEX( IF( REGEXMATCH( A2:A, "(?i)"& JOIN("|", Tech_issues)), &...
• 5,116

### Sum a column if another column contains any of the values

SUMIFS Function While SUMIF returns the sum of a range based on a single condition, the SUMIFS function returns the sum of a range based on multiple criteria. Syntax SUMIFS(sum_range, criteria_range1, ...
• 5,116

### How to plot compounded time series on google sheets

SCAN function SCAN(initial_value, array_or_range, LAMBDA) The SCAN function scans an array and produces intermediate values by application of a LAMBDA function to each value. Returns an array of the ...
• 5,116
1 vote

### I want to sum a column in table A when a criteria is met in table B (per row)

The the-god-of-biscuits' approach is a good one. This is merely an alternative. BYROW aporoach =BYROW(UNIQUE(TOCOL(C2:C,1)), LAMBDA(r, {r, SUM(FILTER(A2:B, C2:C=r))})) Multiple Sheets Combining ...
• 5,116
1 vote

### How can I transfer the contents of hundreds of emails to a single Google Docs/text editor document?

The link above goes to cloudHQ, and while I think Export Emails to Google Sheets is great, and so is their other app Save emails to PDF, they recently released an "Export emails to Google Docs&...

### I want to sum a column in table A when a criteria is met in table B (per row)

Assuming your source table is in columns A:C of your sheet, you can use this formula anywhere to the side of those columns to generate the requested result. Ensure that the source table dollar amounts ...
• 1,140

### Keep matching columns aligned when stacking ranges from different tabs that are missing certain columns

Limit by Fully Populated Column If you are lucky enough to have a fully populated column (X in this example formula), you can use this approach. The LAMBDA q performs each QUERY while also clearing ...
• 5,116

### Can we simplify the if-zero condition

Using the Data > Named functions ability in Google Sheets, you can now do what you're looking for: Function name: IFZERO Function description: The IFZERO function evaluates a value. If the value ...

### How can I merge two columns by using QUERY?

Even better, combine the top two answers (of today) The answer from Rubén introduces perhaps the most direct way to merge the columns by simply adding ArrayFormula() to a formula combining with &. ...
• 111

### Data validation rule that accepts only 16-digit numbers

TL;DR # Three Approaches =AND(LEN(A2)=16,INT(A2)=A2*1) =AND(LEN(A2)=16,ISNUMBER(A2*1)) =REGEXMATCH(A2,"^\d{16}\$") Your Values are Text Based on the behavior you are describing, Sheets is ...
• 5,116

### Increment the reference to a cell formula by 1 in Google Sheets

Rather than using INDIRECT which is a volatile function, use an array formula that updates every time a new form record is received: =INDEX( IF(A2:A, IMAGE("https://chart.googleapis.com/...
• 5,116

### How to edit/append a cell value based on date range?

Formula in C2 Single array formula that will calculate all rows. =INDEX( IF(A2:A<>"", IF(WEEKNUM(TODAY())=WEEKNUM(A2:A), "", "x") & B2:B,))
• 5,116

### Increment the numeric value in column D by 1 if column E is TRUE

I adapted your script. Some notes, I eliminated some of the hard-coded values in favor of relative calculations. eg values.length instead of 40 row+i instead of 2+i range.getColumn()-1 instead of 4 ...
• 5,116

### Any idea why I am getting duplicated cells in google sheets?

I think where you might be having trouble is that the form submission sheet will be constantly reset upon new form submission and will overwrite what has changed from the submission before hand. What ...

### How to refer to 'this' cell in a conditional formatting formula?

You can use following combination of functions. INDIRECT(ADDRESS(ROW(),COLUMN()) You can write complex logics with it. E.g., Check if this cell's value is equal to this month, and the right cell's ...
1 vote

### Why does my count function stop counting the cells once I cross work order 9999?

It sounds like you are using count() to count numbers. A value like 9622-2 will be interpreted by Google Sheets as the date 1 February 9622. Dates are numbers and will be included in count() results. ...
• 14.5k
Accepted

### Google Sheets Auto Categorize Budget from Statement

Use filter() and search() or regexmatch(), like this: =+filter(Categories!B\$2:B, search(Categories!A\$2:A, E2)) See filter(), search() and regexmatch().
• 14.5k
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

### Exporting to PDF from Google Sheets removes emoji

The solution for me was instead of choosing to Download PDF via "File > Download > PDF", try the following: "File > Print > Next > Destination > Save As PDF" (...
• 1

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

Top 50 recent answers are included