Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
181 votes
3 answers
310k views

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), ...
serg555's user avatar
  • 2,185
151 votes
9 answers
767k views

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 ...
Mr. B's user avatar
  • 2,867
96 votes
10 answers
359k views

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 ...
user avatar
39 votes
3 answers
85k views

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 ...
Rushmik's user avatar
  • 431
36 votes
13 answers
81k views

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 ...
Vidar S. Ramdal's user avatar
32 votes
7 answers
180k views

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 ...
kenorb's user avatar
  • 4,442
16 votes
1 answer
123k views

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 ...
CamSyl's user avatar
  • 527
14 votes
3 answers
70k views

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 ...
RLH's user avatar
  • 297
13 votes
8 answers
59k views

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. ...
Kenci's user avatar
  • 277
12 votes
1 answer
15k views

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 ...
Adam Matan's user avatar
  • 1,803
12 votes
4 answers
4k views

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 ...
Spongebob Squarepants's user avatar
10 votes
1 answer
26k views

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: =...
Robert Hickman's user avatar
10 votes
3 answers
23k views

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)? ...
törzsmókus's user avatar
10 votes
5 answers
46k views

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?
user avatar
10 votes
5 answers
9k views

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

Using Google Spreadsheets, you can write queries. However, if you have column letters in quotes, then they aren't updated as column order changes. Is there a way to write these queries so they don't ...
Alex B's user avatar
  • 273
10 votes
6 answers
31k views

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 ...
Dom Delimar's user avatar
  • 1,864
8 votes
2 answers
29k views

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

I have two lists (A and B). I want to filter list "A" (a multi column list) with values in list "B" to create a Final List, list "C". List "A" has multiple records for each student ID number. List "B"...
Mr. B's user avatar
  • 2,867
8 votes
4 answers
8k views

Using Query with column headers instead of column letters

I have a formula that I use to dynamically get the column letter for a 1-Dimensional array that are the column headers of a sheet. In order to use QUERY() with column names instead of letters. ...
Douglas Gaskell's user avatar
7 votes
2 answers
24k views

Google Spreadsheets Query( "where a matches '/[A-Z+]/' ") condition

According to Google Query documentation: matches - A (preg) regular expression match. haystack matches needle is true if the regular expression in needle matches haystack. Examples: where ...
Gleb's user avatar
  • 313
7 votes
7 answers
41k views

LEFT JOIN two and more tables in Google Sheets

Say I have two tables. Table A (material requirements): product_id | material | required 1 | A | 0.5 1 | B | 0.7 2 | A | 0.2 3 | A | 0....
emihir0's user avatar
  • 187
7 votes
2 answers
95k views

Query Select Where is not null vs <>'' not filtering properly

I have an issue with my query where I have numeric cells and text cells that if all are empty I do not want to retrieve them however if anyone of the cells are not empty I want to retrieve that row. ...
CamSyl's user avatar
  • 527
7 votes
2 answers
1k views

How to Create a Google Sheets Script for Reporting 26-Week New Highs using GOOGLEFINANCE Function?

I'm looking to use GoogleSheets to display ticker symbols and prices in real-time of stocks that have created new 26-week highs, i.e. today's highest trading price is greater than any trading price ...
P.D.'s user avatar
  • 81
7 votes
4 answers
19k views

How to make SQL CROSS JOIN of two tables with Google Spreadsheet?

I am just figuring out how to achieve an apparently simple problem. I use to have data as in database tables in my spreadsheets. It's simple data but I often need to use =QUERY function and would be ...
Emilio Nicolás's user avatar
7 votes
2 answers
35k views

Stacking multiple query formulas throws "an Array Literal was missing values for one or more rows"

I am attempting to run multiple queries where it adds rows where if F is blank, then it skips those rows, then runs the query again and replaces row F with row G. I have a much more complicated ...
Melly Lilly's user avatar
7 votes
3 answers
40k views

Unable to parse query string for function Query, when using IMPORTRANGE

This is the problem I'm seeing: Error - Unable to parse query string for function QUERY parameter 2: NO_COLUMND The formula is of the form: =query(importange("...", "Sheet!A:V"), "select D where ...
jas9531's user avatar
  • 107
7 votes
2 answers
12k views

Query Multiple Ranges with Results in Row

I am trying to query five ranges with the results displayed across a single row. To do this, I was using { } with commas separating each range, but it's only giving me the first range in return. ...
Bardo's user avatar
  • 317
7 votes
2 answers
23k views

How to count items meeting criteria in multiple columns

In Google Spreadsheets I need to count instances of a certain value but only when they match either of two values in another column. So below I would ask for the count of all brown animals that are ...
VivaNOLA's user avatar
  • 173
7 votes
1 answer
8k views

Calculate Percentage with QUERY on Google Sheets

I have a Google Sheets database like this: Name | Value | Answer A | 100 | Y B | 200 | N C | 10 | Y A | 100 | N A | 1000 | Y B |...
T. Fontanello's user avatar
6 votes
4 answers
23k views

Google Sheets queries: using WHERE IN

My problem I have a table of ids and terms in Google Sheets. I would like to concatenate a few terms by their ids. For example, (2,3) should yield Talla de zapato de mujer, Women’s shoe size. I want ...
Adam Matan's user avatar
  • 1,803
6 votes
2 answers
10k views

Query syntax using cell reference

I'm having trouble figuring out a fairly simple QUERY statement in Google Spreadsheets. I'm trying to use a cell reference instead of static values and I'm running into trouble. I keep getting Error: ...
Mr. B's user avatar
  • 2,867
6 votes
3 answers
40k views

Query(IMPORTRANGE) function to skip columns

I'm currently using the following function to import data from one spreadsheet to another based on a value in Column K of the source spreadsheet: =QUERY( IMPORTRANGE("URL","Sheet ...
SJKM's user avatar
  • 63
6 votes
1 answer
10k views

filter or sort Google Spreadsheet based on condition

I have an 8 column spreadsheet. I want to sort/filter based upon the value of the 4th column. In a separate sheet within the same spreadsheet, I want to print a list of each row matching a speific ...
John K.'s user avatar
  • 63
6 votes
3 answers
36k views

Google Sheets Query to select a large range of columns without manually entering each column

I have a column with dozens and dozens of columns in it that I'm querying data from. I'm using something similar to below to do this, but I'm wondering if there is an easier way? For instance, ...
Mr. B's user avatar
  • 2,867
5 votes
2 answers
8k views

Get rid of row in result of Google Spreadsheets query

I use the following formula in my Google Spreadsheet: QUERY(Inkomster!A2:G;"SELECT SUM(C) WHERE G="&B3&" AND F='"&A3&"'") This returns the following (On two different rows that ...
Anton Gildebrand's user avatar
5 votes
3 answers
13k views

Query or filter the data where the start time is past 3:00 pm

I've started using Google Sheets for our logbook and it's been a great help. I have data with a time component in it (start and end time) and I'd like to query or filter the data where the start time ...
Jason's user avatar
  • 51
5 votes
1 answer
4k views

Query is returning a single concatenated string of values THEN the rows it found

I'm getting some really weird results from my QUERY() Here is an image with an example: I'm querying for the name where No Show = "No Show". It find the correct name Name 5 but also returns a ...
Douglas Gaskell's user avatar
5 votes
1 answer
5k views

Google spreadsheets: Find max value for a name

I have spreadsheet with 2 columns - names and values, say: John 2 Doe 8 Mary 17 John 5 Bill 12 Mary 6 John 3 What formula can I use to find the maximum value for each name? (E.g. in column C ...
Aivars's user avatar
  • 201
5 votes
1 answer
291 views

How to address the columns of an inline array?

Suppose you want to run a query against an inline array: QUERY({"apple"; "orange"; "banana"}, "select A") This yields Unable to parse query string for Function QUERY parameter 2: NO_COLUMNA. Is ...
Fadeway's user avatar
  • 185
5 votes
1 answer
16k views

Google Sheets Query group by multiple

My sheet is here, and we are looking on the sheet called "Paste". I would like a query that finds and sums the total dollar amounts when the same customer receives the same service code more than ...
Neill Dorn's user avatar
5 votes
2 answers
4k views

COUNTIFS returns incorrect result when applied to the output of QUERY

In my spreadsheet the chart for Negative Feedback weekly pulls data from the Query sheet, but the logic doesn't work to separates negative feedback from positive feedback. The logic works fine on the ...
Simon Rhee's user avatar
5 votes
2 answers
3k views

Dropdown list based on the result of another cell's dropdown

I want to populate a dropdown list based on a selection of another cell's dropdown list. Here is my spreadsheet. E.g., if the user does not make a choice from D20 dropdown menu, therefore F20 ...
MJM's user avatar
  • 81
5 votes
2 answers
1k views

Is it possible to edit the column a Google Form response saves to?

I have a client who uses a Google Form to save data to a Google Sheet. They deleted and recreated one question, and the responses to that question are now being stored in a new column. This messes up ...
Catherine Pearce's user avatar
5 votes
1 answer
3k views

How can I stop Google Sheets charts from rounding down durations?

I've got a Google Sheets chart that displays the average pace of a day's rowing machine workouts (e.g. average the time to row 500m). It looks like this: The Query I'm using to get the data is this: ...
Leon Overweel's user avatar
5 votes
2 answers
5k views

How to nest IMPORTRANGE into the Query function in New Google Sheets

We have applicants who come in and fill out a form via Google Forms. I have fields that auto-populate from the Response tab into a separate tab that is organized to fit our trackers. I am looking for ...
JWCole's user avatar
  • 71
4 votes
2 answers
27k views

COUNTIFS with multiple OR

I have two sheets, one is a dump sheet of values like below: and the second sheet is using values from this to the total. I am trying to get the total count for the corresponding zone and test name ...
kirsty's user avatar
  • 41
4 votes
3 answers
12k views

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

When I sort a column in Google Spreadsheets, the blanks always seem to sink to the bottom. Can I have rows with blank cels be organized first / highest? Alternatively, is there a function wherein I ...
New Alexandria's user avatar
4 votes
3 answers
4k views

Sorting unique rows by a related SUMIF column

I am putting together a worksheet to keep track of expenses. I currently have the following source table called "Expenses": A         B C D 1 Date Description Category Amount 2 1-Jan ...
jared's user avatar
  • 41
4 votes
2 answers
11k views

Escape single quote in cell content in a QUERY

My Problem I have a Google Spreadsheet with two tabs - one containing regexes and values: And the other containing phrases which matches regex in the first tab, and a QUERY Formula that picks the ...
Adam Matan's user avatar
  • 1,803
4 votes
3 answers
8k views

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

My google sheet is importing data from another tab just for sorting purpose, however, when the sort is done manually (through Google Sheet menus), results are unpredictable. The sheet is populated ...
Riccardo's user avatar
  • 589
4 votes
5 answers
29k views

How automatically to add spaces inbetween each character in a text string?

Teachers I work with, enter four scores for a student’s assessment in a single cell, like this: 2113, with no breaks or spaces. How can I separate those characters so as to be able to create a chart ...
TonyF's user avatar
  • 41

1
2 3 4 5
19