# Tag Info

Accepted

### Google Sheets formula for "if contains"

You can use REGEXMATCH: =IF(REGEXMATCH(A1, "sites"), 1, 0) To explain, REGEXMATCH returns true if and only if the argument is a substring of your string.
• 4,819

### Google Sheets formula for "if contains"

If you, as Ryan Shillington suggests, want to know the number of cells within a range that has "sites" in it, you might try: =COUNTIF(A1:A100;"*sites*") The * acts as a wildcard notion, so it will ...
• 301

### How to return DISTINCT/UNIQUE list of text via Google QUERY?

In addition to using the ever-useful UNIQUE function around FILTER, as user79865 suggests, you can also use UNIQUE around the original QUERY function (which may be more applicable and helpful in other ...
• 351

### Google Sheets formula for "if contains"

This returns a #VALUE! error if the search term isn't found: =IF(SEARCH("sites", B2) > 0, 1, 0) This uses the same logic but catches the error and returns a 1/0 for success/failure: =IF(...
• 1,872
Accepted

### HAVING clause in Google Sheets QUERY

Based on this Google Forum answer, I've nested the query: =QUERY( QUERY( final!H2:H, "SELECT H, COUNT(H) GROUP BY H ORDER BY COUNT(H) DESC" ), "WHERE Col2 > ...
• 1,833
Accepted

### How can I create a unique list of data across multiple rows AND columns?

=UNIQUE({A:A;B:B;C:C}) =ARRAYFORMULA(UNIQUE(QUERY(TO_TEXT({A:A;B:B;C:C}), "select Col1 where Col1 is not null")))
• 27k

Why not using Pivot Table? It will give you more options and controls than writing a custom formula. Controls live Add Columns, Rows, Values, Filters
• 249

### How can I create a unique list of data across multiple rows AND columns?

=unique(flatten(A1:Z)) I think this will work and it worked for me.
• 131

### Google Sheets formula for "if contains"

A solution without REGEX: =if(iserror(FIND("sites",B2)),0,1) capitalization matters and if logic must be reversed, given that answer is true when the string is not found.
• 211

### Google Sheets queries: using WHERE IN

The =QUERY() language WHERE clause support regex; so you can "simulate" IN functionality using a regex group - ie: =QUERY(A:B, "SELECT A,B WHERE A MATCHES '(2|3)'")
• 201

### Can I use column headers in a =QUERY?

I was searching for a suitable answer to this question, also, since using column letters like A, B, C is not good for flexibility and becomes a maintenance hassle. Every time your source data columns ...
• 455
Accepted

### Google Sheets Filter Multi Column List With Another List Using A Function

Use a FILTER/MATCH combination. Example on your data below: =filter(C52:F57; match(C52:C57; I53:I54; 0))
• 1,689

Another way is from within the query: =QUERY(Data!A1:B6;"select A, sum(B), count(B) group by A order by sum(B) desc")
• 17.7k

### How can I make some data on a Google Sheets auto-sorting?

It can be done using Google App Script. The script will automatically sort whenever there is a change in sheet data. On your Google Sheet: Choose Tools –> Script Editor and paste the below script ...

### LEFT JOIN two and more tables in Google Sheets

one-cell solution (paste in D1): ={"material", "required", "total"; ARRAYFORMULA(IFERROR(VLOOKUP(B2:B, Sheet2!A2:C, {2,3}, 0), )), ARRAYFORMULA(IF(LEN(A2:A), C2:C*VLOOKUP(B2:B, Sheet2!A2:C, 3, 0), )...
• 27k

### How to return DISTINCT/UNIQUE list of text via Google QUERY?

Solution without helpers / wrapper functions: =QUERY(A1:B6,"SELECT MAX(A) WHERE B = 'SubCatA' GROUP BY A LABEL MAX(A) ''")

### Escape single quote in cell content in a QUERY

Plain simple quote "inversion": =query('Book list'!A:F,"select B,C,D,E where F=""free"" and D="""&E1&"""")
• 61

### Can I use column headers in a =QUERY?

Most of already provided answers requires string concatenation, which is quite inconvenient to use. Some answers involves custom script function, which is better approach. Here's a bit similar ...
• 150

### Is there a way in Google Sheets to order blank cells first?

One easy way to make the blank cells appear at the top of the sheet when sorted is to enter an apostrophe ' in the empty cells. The apostrophe ' will not be displayed in the cell, so it will continue ...

### Is it possible to use named ranges in Google spreadsheet queries so that the columns references are kept up to date?

Is there a way to write these queries so they don't need to be updated every time a column is added or removed? Is it possible to use named ranges in queries to solve this problem? Yes, you ...

### How to return DISTINCT/UNIQUE list of text via Google QUERY?

Use the Unique(range) function. Example: =unique(query(Sheet2!A3:F2372,"SELECT C WHERE A contains '" &E1& "' AND B CONTAINS '" &E2& "' ",-1)) This will work on one column retrieval....

### Google Sheets formula for "if contains"

This will return TRUE or FALSE: =IFERROR(SEARCH("keyword",A1)>0, FALSE)
• 151
Accepted

### Help with a "=QUERY(IMPORTRANGE(..." formula in Google Sheets

IMPORTRANGE requires authorization to access an external spreadsheet, but the authorization pop-up only is shown when IMPORTRANGE is not nested inside of other functions. Once you make the ...
Accepted

### Google Sheets, how to sort query results manually (Not in query)

you can't use Data > Sort sheet by... your sheet needs to be manually sorted by creating a filtered view by selecting your column and then: Data > Filter views... > Create new filter view >...
• 27k