Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
88 views

How to modify Query Options no_format to allow some format, but not others?

I have to replicate a Google Sheet, and am using ImportRange and Query together. I need Query also because the first sheet has urls under them, which I cannot have on the copied sheet. I was able to ...
  • 1
1 vote
1 answer
24 views

If cell in Col A is blank substitute with cell in Col B

I'm attempting to combine two columns from another sheet into one through the QUERY function. My plan is to make it so if a cell in Col A is empty then a cell in Col B will be substituted. However, ...
  • 13
0 votes
1 answer
46 views

Combine data from multiple sheets to create a stacked line chart

I have multiple sheets with three columns each: Date, Invested Amt, and Return Amt. I am trying to merge the values and get a Result dataset to create a single stacked line chart of Invested vs Return ...
1 vote
1 answer
72 views

How to stack two columns in Google Sheet with column name in another column?

I have these columns in the Google Sheet. Date A Date B 1/1/2020 3 5/2/2021 328.28 1/2/2020 41 5/3/2021 322.75 1/9/2020 23 5/25/2021 342 1/12/2020 54 5/28/2021 ...
  • 11
0 votes
1 answer
29 views

Cannot get Query to display time

I need help getting to show the result of a dropdown. If you look at C22, there's a dropdown for a specific date. I'm currently working on row 24 and have applied the formulas I think should work. I ...
  • 1
0 votes
2 answers
97 views

How to make variables dynamic in ARRAYFORMULA/QUERY Combination

I'm trying to self populate the first column of the "Main" sheet with a query that returns the first column of the "Finance Office" sheet that matches all three names. I came up ...
0 votes
1 answer
444 views

How to create Column Identifiers in Google Sheets?

I'm confused by Google's documentation where it states: Columns are referenced by the identifiers (not by labels). For example, in a Google Spreadsheet, column identifiers are the one or two ...
  • 269
0 votes
0 answers
38 views

What is the Google Sheet query for getting rows excluding non matching rows

The tables will have multiple entries for amount debited and credited details. It also has 5% debit and credit and 1% debit and credit entries as well. Data includes unique flag for districts, Date ...
1 vote
1 answer
114 views

What's the formula to select non matching rows by comparing rows in Google Sheets query?

In Google Sheets query, I need to get only non matching debit rows by comparing rows say if amount debited (S1 flag - row 1) matches with amount credited (S1 flag - row 3), it needs to be excluded in ...
0 votes
1 answer
21 views

Filtering views based on a cell having a value

I am trying to sort out a spreadsheet which has multiple columns in but don't really know where to start! I am looking to filter the sheet where column C has more than 3 errors for the ID on column B. ...
1 vote
1 answer
59 views

How to use use cell references in the WHERE part of a QUERY / IMPORTRANGE formula

Can you tell me what's wrong with my formula? = QUERY (IMPORTRANGE ("url googlesheets database"; "Data range"); "SELECT Col1 WHERE Col1 =" A3 "AND Col2 =" B3 "") The "A3" and "B3&...
0 votes
1 answer
630 views

How to have a query wrapped in an arrayformula auto-populate many rows

I have a query that queries over a set of rows. I want to run this query, itself, for each row in another set of rows. The one value that changes in the query for each row is something in the where ...
1 vote
1 answer
82 views

Google Sheets - COUNTIFS with timestamp

I have a column in a table that contains the date and time. This is the form 15.06.2020 09:43:24 17.06.2020 08:21:04 18.06.2020 12:11:34 or also 2021-01-21 11:57:34 2021-01-21 11:58:02 2021-01-21 11:...
  • 13
1 vote
1 answer
29 views

No query result when one of the columns used in the output never has a value

I have a query where the output involves summing two columns. If either of the columns never has a value (given a set of filter criteria in the where clause), there is no result. Why? Here is an ...
0 votes
1 answer
44 views

How do I chart timeseries data organized by one of the columns?

Here's what seems to be a simple common thing in excel/sheets that I can't seem to be able to google in the right way. three columns: timestamp, email, things_done. I want a chart with time as the X ...
-1 votes
1 answer
70 views

how to select and automate "copy by value" to persist data in Google Sheet

I would like to use Google Sheets as a form to query for data, select and store the data I'm interested in. Below what I'd like to try. Please let me know if feasible, and if so, could you tell how to?...
1 vote
3 answers
361 views

'contains' and 'starts with' cannot match text with a character after a hyphen

Google Sheets queries cannot match text using contains or starts with when there the text to match contains a hyphen, and the hyphen is not the last character in the text to match. Am I doing ...
3 votes
2 answers
178 views

cannot drag query across multiple rows (overwrites data)

Is it possible to click and drag queries? I describe my problem below and also have a reference sheet. cells A1:B5 Category Withdrawal CatA 4 CatB 2 CatA 6 CatB 11 cells C1:C3 Check For CatA ...
0 votes
1 answer
350 views

Best way to change dates in Google Sheets in all the tabs?

I have a sheet that is for accountability in my business, I have each month with it's own tab with five week sections and the dates are done by a Monday thru Sunday sections with the date that is ...
0 votes
2 answers
70 views

Why doesn't "format A 'yyyy-mm' options no_values" strip the day from the output?

I have some input that contains a set of dates. I want to strip the day from the input and sort by just the month and year. I know there are other ways to do this, but why can't I set the format to ...
1 vote
1 answer
181 views

Google Sheets: VLOOKUP formula for a multi line cells range

I have here a sample project, where columns A to C are in FORM RESPONSES TAB/SHEET and columns D and E are in RECORDS TAB/SHEET. In the RECORDS I have a list of names, in alphabetical order, and a ...
0 votes
1 answer
30 views

How to find closest similar value above current value, and return a different column on the same row

I'm looking for a way to find the closest similar value(in distance) above the current value(column B), and return the column next to that found value(column C) in column D. A sort of vlookup where it ...
  • 115
2 votes
1 answer
172 views

GOOGLEFINANCE in Google Sheets to get high & low and timestamp of high/low from the day I took trade

I wanted to keep a track of high/low of a trade from the day I took the trade. For a day I am able to get high/low but how to get from the date I took my trade which is stored in one of cell. =IFERROR(...
1 vote
0 answers
15 views

How to add date criteria to query function? [duplicate]

I am using query function to copy all data from sheet1!A2:B10 to Sheet2 if B column is equal to specific data/time. Lets say A1 = 5 April 2021 A2:A10 names B2:B10 has Timestamp And I used this formula ...
1 vote
1 answer
480 views

How to run a google sheets query against a dynamic range

Background I have a spreadsheet that displays data internet consumption by users that looks like this userid feb1 feb2 feb3 . feb29 u1 100 34 23 . 4 u2 ...
  • 1,109
0 votes
2 answers
188 views

Why does google sheets query stop working on 6 decimals (but works on 5)?

I was creating a google sheets query that's pretty simple: query(<range>,"select Col2 where Col1 contains <number with decimals>") I noticed that it works when the number with ...
  • 1,109
0 votes
1 answer
165 views

Concat columns from query result in Google Sheets

I have a sheet that has a column with labels in a single cell. A cell can contain 1 or more labels, each separated by a comma. +-----------+ | Labels | +-----------+ | Foo,Bar | | Bar,Baz | | ...
0 votes
0 answers
60 views

Gsheets Query Importrange not giving expected results

I'm having an issue with the Query(Importrange) function of Google Sheets. I'm using the following formula =Query(IMPORTRANGE('Importrangelink,"'Sheetname'!X1:AA130"),"select Col1,Col2,...
  • 1
0 votes
1 answer
14 views

Get row reference from validated drop down list

So I have two sheets, masterIngredientList and shoppingList. I have data validation in my shoppingList which is bound to a column in the masterIngredientList. In the master ingredient sheet, there are ...
0 votes
1 answer
24 views

How may I add an average count of rows by weekday to this example Google Sheets

Here is an example Google Sheet that summarizes Col A, D by weekday totals and counts. I would like to add an Average number of items by Weekday, like K9 L9 but show it in formula K1. There are two(2) ...
  • 85
1 vote
1 answer
323 views

Google Sheet split cell to rows - data manipulation

I have a sheet, where there is a list of times in Col. C. What I want is for it to be split into different rows & also the relevant order number & name to be copied with the rows. Resulting ...
  • 191
3 votes
1 answer
32 views

The QUERY is giving double results on a single cell

I recently started to use QUERYs on Google Sheets. I just encountered a problem where after typing my query =QUERY('Copy of TEST'!A1:E7,"select A, sum(D) group by A") it is compounding the ...
1 vote
0 answers
31 views

How can I use a dropdown menu in Google Sheets filter out data and populate cells for a an expense tracker?

I have created a personal expense tracker (budget) that uses google form responses and categorizes them by fortnights and expenditure categories. This has been successful on a sheet called 'Yearly ...
0 votes
2 answers
106 views

Formula parse error on importrange query

I'm trying to recreate what's happening in this video at 7:17 https://youtu.be/5S7laJS9meU?t=437 I want to merge two sheets and remove the empty rows. =QUERY({IMPORTRANGE(L2 ,"sheet1!A2:F20")...
0 votes
1 answer
190 views

How to reference named range as sheet name in query function?

=QUERY(ActiveMonth!A7:L,"select A,D where K matches 'Admission|Pending'",0) I am using sheets name/tabs (January, February.. December). ActiveMonth is a namedRange of a cell containing the ...
1 vote
1 answer
178 views

How to reference a cell outside range using query function in Google Sheets

I'm trying to copy cells B to F to another sheet if the text in cell J contains specific text. As an example case, I'm using the query function as: =query('Sheet 1'!B1:J,"Select * Where J ...
0 votes
2 answers
490 views

How to make the cells of query function results with borders that are dynamic according to rows/column numbers?

https://docs.google.com/spreadsheets/d/1VsEvt2KnYGnlN_qHjkjPohVHF5MUqfyHD7tSk0VpkqA/edit?usp=sharing I would like to make the cells that have query results in All_Patients tab with borders instead of ...
1 vote
2 answers
68 views

How do i use the query in google sheets to return N results from each tab

How do I use the query in Google Sheets to return N results from each tab. So I have this formula: =QUERY({sheet1!A2:D;sheet2!A2:D}; "select * where Col4>=65 order by Col4 desc Limit 3") ...
  • 23
1 vote
3 answers
44 views

Count instances of character in a column

Is there a way to count instances of characters down a column, without creating further columns that do row-by-row counts of each character? Below is an outline of what I'm trying to do. Note that no ...
  • 117
1 vote
1 answer
75 views

Combine multiple IF statement/condition into one Google Sheet column

How can I achieve in Google Sheet so that the column will auto populate the correct "vendor" based on the selection on "model" and "size". Refer below: There will be 2 ...
  • 13
1 vote
1 answer
81 views

How to use a query to find a row of same name but last entry, then copy a value next to it (update request)

(This is an updated version of this question - I am trying my best to make myself clearer this time.) I am trying to use a query to copy values from one column to another. Both source and destination ...
  • 137
0 votes
1 answer
22 views

How to extract desired text from a cell

As you can see below I have these data in single cell I want only car nos as my result
2 votes
1 answer
82 views

How to use a query to find a row of same name but last entry, then copy a value next to it

I am trying to use a query to copy values from one column to another. Both source and destination columns have a name on their left columns, and the name must match. The source names could be ...
  • 137
2 votes
1 answer
57 views

Counting rows based on the date it has and the current end of the week

Problem to solve: Given 2 rows, containing the author's name and a date, you have to count how many rows that author has. The tricky part here is that I have to use the date row and check how many ...
  • 23
1 vote
1 answer
37 views

Problem with results of filter function from tabs that have no data yet

I made a master sheet that combines all data from 12 tabs within the workbook sheet. These tabs use query functions and importrange from other sheets. Some of sheets have no data yet like May 2021 ...
0 votes
2 answers
1k views

Change all unique country abbreviations to country names and place in one cell

I need a formula that can add all the countries with that is in a column. Example: In sheet 'Export' I have the below countries of origin: And in other sheet I need to show all the countries of ...
  • 11
0 votes
1 answer
47 views

Look up rate for specific person in effect on or after a date

In this Google Sheet, I have one sheet, "Transactions", with a rate entry that should be auto populated from a table in the "Rates" sheet. The formula should look up the rate for ...
0 votes
1 answer
314 views

How to use FLATTEN function in query function

Below in my query result: In the above table "Form Response" table is the response received in Google Sheets from Google Form. I want to convert "Form Response" table to "...
  • 1
1 vote
2 answers
1k views

Select Unique on 1 Column in Google Sheets - Distinct Doesn't

I'm trying to set up a kind of a bid thing, where the top 5 respondents receive a prize. I was hoping to use SQL to get the top 5, but I can't remove duplicate bidders and only take their top bid. I ...
1 vote
1 answer
26 views

How to create an average value for every cell which contains a certain phrase?

I need to find the average value of cells containing a certain set of initials to create a scoreboard. The value of the cell being the score and the brackets containing the initials of the score ...

1 2 3
4
5
18