183
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.
- 4,761
30
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 ...
- 301
24
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 ...
- 341
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(...
- 1,844
18
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")))
- 26.2k
17
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 > ...
- 1,793
13
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.
- 131
11
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
- 219
11
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 ...
- 119
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.
- 211
9
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))
- 1,659
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)'")
- 191
8
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 ...
- 445
7
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 ...
- 71
6
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")
- 17.4k
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) ''")
- 201
6
votes
Accepted
Query(IMPORTRANGE) function to skip columns
No, it's not possible to "skip columns".
Instead you could use two query functions, one to be used to get the columns A - C, the other to get the columns F and following.
First formula (A1):
=...
- 44.6k
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 ...
- 290
6
votes
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), )...
- 26.2k
5
votes
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 ...
- 44.6k
5
votes
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....
Only top scored, non community-wiki answers of a minimum length are eligible