Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
167 votes
3 answers
264k 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), ...
  • 2,035
147 votes
9 answers
716k 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 ...
  • 2,797
87 votes
10 answers
326k 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
37 votes
3 answers
75k 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 ...
  • 411
32 votes
12 answers
65k 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 ...
27 votes
7 answers
154k 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 ...
  • 3,982
15 votes
1 answer
115k 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 ...
  • 517
12 votes
3 answers
59k 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 ...
  • 277
12 votes
8 answers
58k 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. ...
  • 267
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 ...
11 votes
1 answer
11k 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 ...
  • 1,733
10 votes
5 answers
44k 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
8k 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 ...
  • 273
9 votes
1 answer
25k 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: =...
9 votes
6 answers
28k 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 ...
  • 1,853
8 votes
3 answers
22k 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)? ...
8 votes
6 answers
34k 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....
  • 197
7 votes
2 answers
81k 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. ...
  • 517
7 votes
4 answers
17k 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 ...
7 votes
2 answers
32k 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 ...
7 votes
3 answers
38k 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 ...
  • 107
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 ...
  • 81
7 votes
4 answers
7k 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. ...
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. ...
  • 307
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 ...
  • 173
6 votes
3 answers
18k 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 ...
  • 1,733
6 votes
2 answers
25k 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"...
  • 2,797
6 votes
2 answers
9k 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: ...
  • 2,797
6 votes
3 answers
31k 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, ...
  • 2,797
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 ...
5 votes
3 answers
36k 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 Name!A2:P1000"), ...
  • 53
5 votes
1 answer
4k 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 ...
  • 201
5 votes
1 answer
251 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 ...
  • 185
5 votes
1 answer
9k 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 ...
  • 53
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 ...
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: ...
5 votes
2 answers
4k 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 ...
  • 71
5 votes
1 answer
6k 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 |...
4 votes
2 answers
20k 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 ...
  • 41
4 votes
2 answers
20k 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 ...
  • 283
4 votes
3 answers
11k 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 ...
4 votes
5 answers
23k 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 ...
  • 41
4 votes
1 answer
3k 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 ...
  • 41
4 votes
2 answers
7k 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 ...
  • 529
4 votes
1 answer
12k views

QUERY where column IS NOT EQUAL to ANY cell in column A of a second sheet

I want it to pull the max value from column F where the corresponding row has C matching the number 14, and A does not have any exact text matches in any cell in column A of a separate sheet. I feel ...
4 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 ...
4 votes
2 answers
6k views

Google Sheets Query(Importrange) with named range?

I am looking for a way to query an imported range using named range. Something similar to this, however I cannot makeit to work Sheet ID#A has a column J whose named range is "Signed" that may ...
  • 529
4 votes
3 answers
10k 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 ...
  • 41
4 votes
1 answer
2k views

In Google Sheets, can you apply a regex to a generated range?

I have a list of UK postcodes, looking roughly like this. Entries may not be unique. A1 1AA A1 2BC A1 3PN B2 4XY B2 5LH C3 6NJ They're in a range named Postcodes. I've set up the following ...
  • 143
4 votes
1 answer
5k views

Query MAX group by

I'm trying to Query in only the most recent entries of each part number. I can usually figure this stuff out but cannot figure out why it only returns the (date) and not the (part number) and (most ...
user avatar

1
2 3 4 5
19