177 votes
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.
user avatar
28 votes
Accepted

Google Sheets Query Get Row When Any of These Cells are Not Empty

Please try: =QUERY('Form Responses 1'!A:N,"SELECT C,E,G,H where G is not null or H is not null order by C,E")
user avatar
  • 16.8k
26 votes

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 ...
user avatar
  • 261
23 votes

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 ...
user avatar
  • 331
21 votes

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(...
user avatar
  • 1,814
17 votes
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")))
user avatar
  • 24.7k
16 votes

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

Another option without a script is: =QUERY(A1:C3,"SELECT * ORDER BY C") The range is restricted (A1:C3) because where the order is ascending blank entries will appear first.
user avatar
  • 16.8k
14 votes
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 > ...
user avatar
  • 1,693
11 votes

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.
user avatar
  • 111
10 votes

How to group data in a Google Spreadsheet?

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
user avatar
  • 209
10 votes

Can I use column headers in a =QUERY?

Is it at all possible to use column headers like this? Yes, it is possible. First, you need to use MATCH to get the column number of the column whose value matches 'Name'. Then you need to use ...
user avatar
9 votes

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

You need to add a helper column within the QUERY formula, like so: =QUERY( {A1:B6, A1:A6}, "SELECT Col1, COUNT(Col3) WHERE Col2 = 'SubCatA' GROUP BY Col1 LABEL COUNT(Col3) ''" ) Adding ...
user avatar
9 votes

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)'")
user avatar
9 votes

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.
user avatar
  • 91
7 votes

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 ...
user avatar
7 votes

How to sort a Google Sheets Query by a custom ordering

Make a new sheet inside your current spreadsheet. Put two columns in it: VoiceType Sort order SOPRANO 1 CONTRALTO 2 TENOR 3 BASS 4 In your main worksheet (the one with the list ...
user avatar
  • 7,219
7 votes
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))
user avatar
  • 1,529
6 votes

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) ''")
user avatar
6 votes

Average of last 7 non-empty non-blank cells in Google Sheets

TL;DR =AVERAGE(OFFSET(A:A;COUNT(A:A)-7 + MATCH(TRUE;INDEX(A:A<>0);0)-1;0)) Example Explanation First we count how many numbers are in row A with =COUNT(A:A). Cells containing text won't be ...
user avatar
  • 290
5 votes

How to GROUP and ORDER data in a Google Spreadsheets?

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")
user avatar
  • 16.8k
5 votes

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 ...
user avatar

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