Web Applications Stack Exchange
Web Applications Stack Exchange

Questions tagged [google-sheets-query]

Use for questions about QUERY built-in function from Google Sheets.

QUERY(data, query, [headers]) is Google Sheets built-in function. Please read the official documentation before posting a question.

This tag could be used together with

  • only when you are using QUERY with another Google Sheets feature like the arrays handling feature or the IMPORTRANGE built-in function.
  • only when a script is used to modify the QUERY arguments.
955 questions
192
votes
3 answers

How to group data in a Google Spreadsheet?

I have the following table in a Google Spreadsheet: Name | Value A | 10 B | 100 A | 20 B | 200 C | 1000 How can I create another table based on a query: select name, sum(value), count(value) from table group by name so it looks like…
serg555
  • 2,295
  • 3
  • 17
  • 12
158
votes
10 answers

Google Sheets formula for "if contains"

I'm trying to figure out how to identify IF a list of items in one cell containing a value or string. EXAMPLE Cell A1 contains sites, sheets, docs, slides. I want cell B1 to display a 1 'if' cell A1 contains the string sites. FORMULA =if(A1 ??????…
Mr. B
  • 2,967
  • 11
  • 38
  • 46
98
votes
10 answers

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

Let's say I want Google Sheets to automagically sort some data, for example column C. How do I do that? I know that I can manually sort data by right-clicking and selecting Sort data, but this is not what I'm looking for.
Diego Petrucci
43
votes
3 answers

FILTER(condition OR condition) syntax?

I am using FILTER() to display a value from Sheet 2 where the Name columns match. My problem is where I want: =FILTER(Sheet2!A:F, Sheet2!A:A="Combat Medic" OR Sheet2!A:A="Universal") as OR results in an error. What is the correct syntax? Must I use…
Rushmik
  • 471
  • 1
  • 4
  • 6
37
votes
13 answers

Can I use column headers in a =QUERY?

Reading the documentation for the =QUERY function1, 2, 3, some of it seems to imply that I should be able to use column headers directly in my query. For example, the =QUERY function takes a third optional parameter, HEADERS, which lets you specify…
Vidar S. Ramdal
  • 17,409
  • 11
  • 71
  • 82
32
votes
7 answers

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

Given the list of categories and sub-categories: CatA SubCatA CatA SubCatA CatB SubCatB CatB SubCatB CatC SubCatC CatC SubCatC I'd like to return all unique main categories based on the secondary category. I don't want to…
kenorb
  • 4,542
  • 14
  • 48
  • 62
16
votes
1 answer

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

So like my title states I would like to select via a query only the rows where certain cells are not blank/empty. I have 4 pools that I have a form for collecting their chemistry. In separate sheets I would like to display each pool's data. I'm…
CamSyl
  • 527
  • 3
  • 5
  • 13
15
votes
3 answers

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

Google Sheets has a UNIQUE() function which when given a column of values, will output a column of all unique values. This works well for column-specific data, however, you have multiple columns and rows of data that needs to be summarized, this…
RLH
  • 307
  • 1
  • 2
  • 10
15
votes
1 answer

HAVING clause in Google Sheets QUERY

My Problem I have built a count query that returns the most common elements from column H in the final sheet: =QUERY(final!H2:H,"SELECT H, COUNT(H) GROUP BY H ORDER BY COUNT(H) DESC") I would like to filter out elements that appear only once. In…
Adam Matan
  • 1,873
  • 5
  • 20
  • 33
13
votes
8 answers

How to make a query with Google Spreadsheet, that checks for a date

I have a range that goes from B4:H124. All the cells in column B are dates. I would like to retrieve the data from a row (B:H), where the DATE equals to the content of a specific cell, lets say Q4. How do I construct a proper query? So far, what I…
Kenci
  • 277
  • 1
  • 3
  • 7
12
votes
4 answers

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

I have numbers in range D7:D and there are times when some cells are empty. I want the average of the last 7 numbers but to skip blanks. So if in the last 7 there's only 3 that are full, I want it to go back further and find 7 total and average them…
Spongebob Squarepants
  • 359
  • 3
  • 9
10
votes
1 answer

Remove header and blank row from the results of a SUM, GROUP BY query

I have a two column table where one column is some sort of key where duplicates are allowed and the other column includes amounts. I then have the following formula somewhere else on the sheet: =QUERY(A1:B, "SELECT SUM(A) GROUP BY B") The results…
Robert Hickman
  • 602
  • 1
  • 6
  • 11
10
votes
3 answers

How to sort a Google Sheets Query by a custom ordering

I have a table of choir members with a “name” and a “voice type” column. How can I write a query that orders them by decreasing pitch level of the voice type (i.e. soprano > contralto > tenor > bass)? Is there in the Google Query Language something…
törzsmókus
  • 339
  • 1
  • 4
  • 14
10
votes
5 answers

Count the number of words in a string in Google Sheets

I have a simple Google spreadsheet with a number of text strings. I just want the number of words contained in each cell. Is there an in-built Google spreadsheet function I may use?
Nishant
10
votes
6 answers

Split content of one row into multiple rows?

I have a table where each row contains data like this: Team name | First member | Second member | Third member | Team description Would it be possible to transform/break this data so that each row contains only one member? Like this: Team name |…
Dom Delimar
  • 1,864
  • 3
  • 14
  • 12
1
2 3
…
63 64