Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
2k views

List all records with certain criteria

Help. I just cannot get this going even after 2 hours of searching Google. I have a Google Spreadsheet that has a "List" sheet and an "Outstanding" sheet. I want to see what's outstanding from a ...
4
votes
3answers
10k 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 ...
1
vote
3answers
2k views

Google sheet, lookup a value to get whole column

Here is my problem: I have a table starting in A1 with D D C B A 1 2 3 4 5 6 7 8 I want a function that returns me the column of data of whatever letter give it. ie input:=function("C") output: ...
1
vote
1answer
147 views

Sort auto update

I have this table, which is a ranking, whose values are updated automatically (data gathered from other files). What I want is that every time these values are updated the ranking automatically ...
2
votes
3answers
11k views

query all dates within the date range

I have a Google Spreadsheet that I'm querying and the date is set as dd/mm/yyyy. How can I query between the start and finish (ref cells) of each month, so the query only returns dates between the ...
3
votes
3answers
5k views

Import current price from website to spreadsheet

As you can probably gather from the title, the goal is to plug the current price of this product into a spreadsheet. Perhaps I'm over-complicating things, but there must be an easy way to do this. The ...
2
votes
2answers
4k views

SUMIFS with text value of cells

Is there a way to use the text value of cells with a conditional statement such as SUMIFS so that on one sheet I can have all the expenses: # A B C 1 R. Tusk Travel 1,...
6
votes
2answers
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: ...
1
vote
1answer
4k views

Query to copy from Sheet A to B based on TRUE/false

I'm trying to copy data from Sheet A to Sheet B based on if one of the columns has TRUE or False. I'm trying to use: =QUERY( 'A'!A1:I200 , "select * where I = 'True'" ) but this only gives me ...
4
votes
1answer
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 ...
23
votes
8answers
53k 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 ...
4
votes
1answer
7k views

Multiple conditions look up

I am having trouble building a spreadsheet, the problem its my data table. I want to be able to pull data from the table, something that will match data from column A, and column B (on the same row) ...
7
votes
2answers
22k 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 ...
3
votes
1answer
8k views

How to search multiple columns in Google Spreadsheet and return values in other columns?

I have two spreadsheets and would like to enter a value into cell A1, have it check multiple columns in a spreadsheet named 'data' and output information from other columns if it's a match. ----------...
2
votes
1answer
593 views

Group by and ordering inside a group in Google Spreadsheets

I have the following data in a Google Spreadsheet: 1 shiplu rice 2 sharmin rice 3 sharmin fast food 4 sharmin salad 5 rafiq burger 6 nazia noodles 7 rafiq salad 8 rafiq ...
1
vote
1answer
2k views

Repeat a FILTER or ARRAYFORMULA n times?

I would like to repeat an ARRAYFORMULA or FILTER n times. Let's assume I have: A 1 a 2 b 3 c Now I set =ARRAYFORMULA(A1:A3) and I get the same output. But now I want it to repeat n times, let'...
4
votes
2answers
17k 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 ...
2
votes
1answer
1k views

How to make a query with Google Spreadsheets that returns items from past 15 days?

I have tried using: =QUERY('Noah''s Weight'!A:D, "select * where A > date ' " &text(TODAY()-15;"yyyy-MM-dd" ) &" ' ") Row 1 is the headers, column A is the time stamp from the form, ...
3
votes
2answers
8k views

Import Google Spreadsheet range if a column contains a particular value

I want to import a range of data from one Google Spreadsheet (1) into another (2), but only when Column J in spreadsheet 1 contains a 'yes'. I'm using the following formula: =query(ImportRange("...
3
votes
1answer
11k views

How to filter, SUM and display multiple values with multiple conditions added daily?

I have a spreadsheet that collects dozens of payments made daily through different means. For example, utilities bills, credit cards bills, personnel fees, expenses, etc. They are made in cash, by ...
9
votes
5answers
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?
3
votes
1answer
12k views

How can you add a conditional statement to a =QUERY function

How can I add a conditional statement (similar to an if elseif in .php) to the query listed below so that is will return columns D and E from Sheet2 if column C in the responses sheet == 1st or ...
4
votes
5answers
19k 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 ...
1
vote
1answer
276 views

Importing only certain rows

I am attempting to create a spreadsheet based off another spreadsheet. I only wish to import certain rows based on the second column of the first spreadsheet. If that column contains certain users ...
2
votes
1answer
2k views

formulas to transfer certain data from one sheet to another

I have a list of volunteers in sheet one. They have selected what programs they want to participate in with a yes or no in the column under that program. How can I create a formula that says if this ...
1
vote
2answers
6k views

How to use an IF statement over several cells?

I'm making a budget for myself. Currently, my Google Spreadsheets looks something like this: ----A---- -----B---- food 50 alcohol 20 food 120 dance ...
1
vote
1answer
165 views

Publish last 5 entries from a Google Form

We currently create and html table to list our last 5 Webinars. This is done manually. We are adding external contractors into the mix and would like to give them the ability to enter basic webinar ...
3
votes
2answers
22k views

How to GROUP and ORDER data in a Google Spreadsheets?

I want to group and sort a particular data set. I've prepared the data using a QUERY function and using other functions. Can I order by sum? This is an example file I created: order by sum
1
vote
1answer
7k views

Google Spreadsheet: Query function date formatting not working

I'm trying to make this query work on my spreadsheet on chrome Win7: =QUERY(A1:D5;"SELECT MAX(A), COUNT(A), SUM(C) GROUP BY (A) FORMAT MAX(A) 'MMMM' ") Everything is ok except for the ...
37
votes
2answers
72k 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 ...
9
votes
6answers
26k 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 ...
5
votes
1answer
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 ...
0
votes
2answers
5k views

Query multiple Google spreadsheets

I have the following sheets: ListA ListB ListC Note: at each sheet I have a column which contains YES or NO. How do I query all my lists (spreadsheets) where myBooleanColumn equals to YES/NO?
1
vote
1answer
751 views

How to make a query that returns rows if deadline is two weeks / one month / two months from today, etc

I have a spreadsheet that contains several columns with project information. I want to modify: =QUERY( 'Compilation'!A2:L; " select A, B, F, I, L order by A" ; 0 ) so it returns values in L that ...
4
votes
2answers
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 ...
154
votes
3answers
239k 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), ...
10
votes
5answers
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 ...
4
votes
1answer
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 ...
12
votes
8answers
56k 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. ...
2
votes
2answers
40k views

Google Spreadsheet formula, MINUS?

I have a spreadsheet with 3 columns: | ITEM | COST | SUBTOTAL | Subtotal, for example, is 1000 to start, like: -------------------------- | ITEM | COST | SUBTOTAL | -------------------------- | ...
82
votes
10answers
294k 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 ...

1
12 13 14 15
16