New answers tagged

0 votes

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=&...
Blindspots's user avatar
  • 5,116
3 votes

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 ...
doubleunary's user avatar
  • 14.5k
0 votes

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().
doubleunary's user avatar
  • 14.5k
4 votes
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 ...
Blindspots's user avatar
  • 5,116
7 votes

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,&...
Martín's user avatar
  • 1,366
0 votes

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, ...
Blindspots's user avatar
  • 5,116
4 votes

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
vstepaniuk's user avatar
4 votes

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(...
Blindspots's user avatar
  • 5,116
3 votes

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)...
Fe2O3's user avatar
  • 151
6 votes

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), ...
doubleunary's user avatar
  • 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-...
Blindspots's user avatar
  • 5,116
0 votes

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 ...
Blindspots's user avatar
  • 5,116
0 votes

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 ...
Blindspots's user avatar
  • 5,116
2 votes

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 ...
doubleunary's user avatar
  • 14.5k
0 votes

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))...
doubleunary's user avatar
  • 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( ...
Blindspots's user avatar
  • 5,116
0 votes

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 ...
Blindspots's user avatar
  • 5,116
0 votes

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 ...
Blindspots's user avatar
  • 5,116
0 votes

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:...
Blindspots's user avatar
  • 5,116
2 votes

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 ...
doubleunary's user avatar
  • 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&...
The God of Biscuits's user avatar
3 votes

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_(&...
doubleunary's user avatar
  • 14.5k
0 votes

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(...
Blindspots's user avatar
  • 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", ...
doubleunary's user avatar
  • 14.5k
0 votes
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 ...
Blindspots's user avatar
  • 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})
Albert Arslanov's user avatar
3 votes

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'...
The God of Biscuits's user avatar
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 ...
Blindspots's user avatar
  • 5,116
2 votes
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("-",...
Blindspots's user avatar
  • 5,116
0 votes

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)), &...
Blindspots's user avatar
  • 5,116
0 votes

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, ...
Blindspots's user avatar
  • 5,116
0 votes

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 ...
Blindspots's user avatar
  • 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 ...
Blindspots's user avatar
  • 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&...
Natasha Ursen's user avatar
2 votes

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 ...
The God of Biscuits's user avatar
0 votes

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 ...
Blindspots's user avatar
  • 5,116
0 votes

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 ...
T Hallman's user avatar
0 votes

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 &. ...
Mike M's user avatar
  • 111
0 votes

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 ...
Blindspots's user avatar
  • 5,116
0 votes

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/...
Blindspots's user avatar
  • 5,116
0 votes

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,))
Blindspots's user avatar
  • 5,116
0 votes

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 ...
Blindspots's user avatar
  • 5,116
0 votes

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 ...
Marshalls Best's user avatar
0 votes

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 ...
JohnnyEast's user avatar
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. ...
doubleunary's user avatar
  • 14.5k
0 votes
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().
doubleunary's user avatar
  • 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, ...
TheMaster's user avatar
  • 3,352
0 votes

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" (...
Shea's user avatar
  • 1
2 votes

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, ...
Blindspots's user avatar
  • 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(...
doubleunary's user avatar
  • 14.5k

Top 50 recent answers are included