Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
1 vote
0 answers
11 views

Google Sheets query with sum and date? [duplicate]

I'm trying to write a query from the following table: columns for date (A), description (B), amount (C), category (D) I have this query: =query(Transactions!A2:D501, "select D, sum(C) group by D&...
Luke's user avatar
  • 11
0 votes
1 answer
20 views

Stuck with a google sheets IF formula for generating boolean strings

I've been looking for a solution everywhere, but have been unsuccesful. I'm trying to make a formula on google sheets, that would help me utilize all added keywords from a column B into a sourcing ...
MaestroGGP's user avatar
2 votes
2 answers
43 views

Query form data by date to return counts by item and category

I'm a teacher and need help with a Google Form that collects the following info: Timestamp Name Class Lunch option All of the data comes into a Google sheet. I'm using successfully using the QUERY ...
elbennet's user avatar
1 vote
3 answers
36 views

Google Sheets Query: Selecting column, can only order descending

The following query works for me in google sheets: =QUERY(A3:A,"SELECT A order by A desc") Changing desc to asc however does not, instead returning #REF!: =QUERY(A3:A,"SELECT A order ...
Wiley Rush's user avatar
0 votes
1 answer
29 views

QUERY function to return records with multiple criteria

I have a dataset on Google Sheets similar to below: Name Team Account Date Jennifer East Meta 5/1/2023 Steve East Alphabet 4/1/2023 Claire West Google 4/23/2023 Roger West Apple 6/23/2023 Bob ...
Elaine's user avatar
  • 19
1 vote
1 answer
21 views

Google Sheets QUERY function to return records with multiple filter criteria based on cell reference

I have a dataset on Google Sheets similar to below. Name Team Account Date Jennifer East Meta 5/1/2023 Steve East Alphabet 4/1/2023 Claire West Google 4/23/2023 Roger West Apple 6/23/2023 Bob ...
Elaine's user avatar
  • 19
0 votes
1 answer
1k views

Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: D

I have data in a sheet called "PASTE HERE" that I want to import into another sheet in the same workbook. I don't want to use IMPORTRANGE because then copying the workbook would necessitate ...
LJNCRAFT's user avatar
2 votes
1 answer
106 views

How to apply ArrayFormula on a RegexReplace Query formula so that it autopopulates for all values in Column A?

I'm trying to create a single string for each product that includes different unit prices tied to purchase quantity. For example, a product might have a unit price of £10, but the unit price drops to £...
Richard Parnaby-King's user avatar
0 votes
2 answers
24 views

Google sheets - copy adjacent cells if value is identical

I have a table like this Letter Selected letter Number A B 1 B D 2 C 3 D 4 I'd like to have as a result: Selected letter Number B 1 D 4 I tried to compare the two firsts columns to ...
Dave Loepr's user avatar
0 votes
0 answers
19 views

Script to determine time after a dropdown status is changed (utilizing the edit history)

I have a Google sheet-based simple CRM for leads from Facebook ads. When I import leads into the sheet, I assign them the status of Raw in a dropdown column for status. My team then contacts them and ...
Usama Naveed's user avatar
1 vote
2 answers
36 views

Add cell value in query

When doing a multi-sheet query, I would like to bring in the value of a cell (A2) from each source sheet to a new column in the results. The query: =QUERY({CCL!A4:M; HOOD!A4:M; AAL!A4:M; BAC!A4:M; ...
A. Herskovitz's user avatar
3 votes
1 answer
34 views

How to add a column in query that adds 1 year to date column

I have a Google sheets with a bunch of sheets for each vehicle type. Each sheet is formatted the exact same: column A is the unit number, column B is the inspection date, and column C is the status (...
Spencer Lowery's user avatar
0 votes
1 answer
39 views

Query ignoring text

When I share data with sub-subcontractors, I use the QUERY function to return a subset based on a company name match. I never had any issues in the past, but lately QUERY has not been returning values ...
Elle's user avatar
  • 1
3 votes
1 answer
47 views

Filter a list of lottery results for those matching at least 4 numbers

I have a list of 3,000 winning lottery numbers. Each winning lottery number is on its own row and is comprised of 6 individual numbers, each in their own column. The winning number's draw date is also ...
Felipe's user avatar
  • 33
1 vote
0 answers
31 views

Google Sheets - pull multiple lines of data from Workbook1 based on A1 in Workbook2 [closed]

Ok. So I've been all over this looking for an answer to my problem. I found so much helpful info, but none of it quite solves my problem. So I am here to ask the experts myself. I work for a ...
user312381's user avatar
0 votes
1 answer
31 views

How do I return the value of a specific cell within a range on Google Sheets based on current date?

I have a spreadsheet that I'm using to track weekly performance metrics and want to create a summary page that only shows me the value of each metric for the current week. The worksheet is currently ...
Pascale's user avatar
1 vote
1 answer
88 views

Google Sheets Query, filtering with a range of values

I want to query data by checking if the data_range's column C (invoice#) matches this tab any value in column B (invoice#), while this tab column B will have variable data size ( copy from another ...
Seth YEUNG's user avatar
0 votes
1 answer
30 views

Formula to check is cells are blank. If the cell is not blank then assign a value

I have a data set on Google Sheets where for example, Name Stage 1 Date Stage 2 Date Stage 3 Date Stage 4 Date Bob 3/1/2023 3/5/2023 3/31/2023 4/15/2023 Joe 2/1/2023 Zoe 3/21/2023 4/1/2023 4/15/...
Elaine's user avatar
  • 19
0 votes
1 answer
22 views

Query to group files together by a unique box number and return the min/max range

I have a Google sheet that list of files with a corresponding box number(Col1)labeled "Box Number". I want to be able to gather all files by each unique box number and then return the min ...
NateM's user avatar
  • 13
2 votes
2 answers
133 views

Sum of all numbers from many columns that match text from multiple rows in another column

I have some data from analytics that I need to combine somehow and trying to find the best way of doing this. I have data similar to the following: URL Sessions / 59 /?variable=1 2 /?variable=2 37 ...
manc's user avatar
  • 135
1 vote
1 answer
69 views

How to QUERY - group SUMed results? [duplicate]

I'm new to query and can't figure out how to get grouped results of sum in two columns. There's data set in the picture of names and points which are in two columns and query formula to sum and group ...
Jan's user avatar
  • 11
-2 votes
2 answers
22 views

i want that webp urls are arranged in the same order as jpg urls

webp urls have the same names as jpg urls. i want that webp urls are arranged in the same order as jpg urls. they're in Y2:Y17 in google sheets. jpg urls are in Y2:Y9 and webp urls are in Y10:Y17. ...
PC Welcome's user avatar
0 votes
2 answers
101 views

How to Count Combinations In a Set (Including dupes)

Trying to figure out a formula where I have 5 columns of text data. I want to count how many times combinations of data in 2 contiguous columns appear in each row. Example data: A B C D E 1 A A A B ...
user310030's user avatar
0 votes
1 answer
21 views

SUMIF for unique entries

I need to do a SUMIFS statement in Google Sheets but am having a hard time not double counting duplicates in the data. For example: Volume Flavor Batch # 6000 CLM BT1000 6500 CLM BT1000 7000 WBG ...
Ben's user avatar
  • 13
0 votes
1 answer
227 views

Google Sheets use count in query without expanding array

My goal is to dedupe results based on column B, filter by last X days in column 13 (omitted in sample data I can work that part out myself), and then count the number of rows that remain with a value ...
Adam's user avatar
  • 449
-1 votes
1 answer
29 views

Pair and Sort the Columns in Desc Order [closed]

Hello I have the following data in my table: Columns A to E indicate the year tag, and columns from F to J indicate the years. Each tag corresponds to a year. For the first line: T1 -> 2004 T2 -&...
Ali Yucel Akgul's user avatar
0 votes
1 answer
25 views

Query that combines the contents of 3 columns, but also filters by a date in a 4th column

This is an example of my data that is located on a sheet called Data: Date Fruit 1 Fruit 2 Fruit 3 1/1/2020 Apple Mango Apple 2/1/2021 Cherry Banana Pear 2/1/2021 Orange 1/1/2022 Apple Cherry ...
Rob Hilken's user avatar
0 votes
1 answer
31 views

How to Eliminate Duplicates Within Same Cell with Separator

Say I have one column that contains cells: Apple||Banana Banana|Apple Apple||Apple Banana|Banana Apple||Banana||Apple etc... How do I eliminate any instance of a word appearing twice (separated by a ...
Trevor Stratton's user avatar
0 votes
1 answer
247 views

Google Sheets function to create hyperlink based on data in cell, but not if the cell is empty

The solution outlined here is ideal for my needs (converting an article ID in column C into a hyperlink to a CMS that displays in column D). However, I would like the formula to leave column D blank ...
Jack_Luke's user avatar
0 votes
0 answers
7 views

How to get company details using Importxml from this URL? [duplicate]

I need the list of companies from here https://www.capterra.com/event-management-software/ I am trying to use the Importxml formula but there seems to be an error. Can someone pls help. The formula I ...
Sandy's user avatar
  • 1
0 votes
1 answer
20 views

Formula to select certain people only

I have a spresdsheet in google sheets and use the following formula =QUERY('Main Data'!A2:U,"select B, sum(J), sum(E),count(E), sum(F), count(F), sum(G), count(U), WHERE A IS NOT NULL AND O>=...
cath's user avatar
  • 1
0 votes
1 answer
54 views

Best way to SUM all the columns between two particular columns

What should be the best approach to get a sum of cells that are between two columns A and E. Column A has a header of "Month Jan" and col E has a header of "Month Feb". Columns are ...
Areeb's user avatar
  • 3
-1 votes
1 answer
39 views

Sorting data extracted by QUERY by a new column in the destination list

I am pulling some filtered data out of a master list by QUERY. I am also adding another column, which does not exist in the master list, to the date in the destination list. My question is how should ...
Reza Farahipour's user avatar
0 votes
1 answer
31 views

Formula to randomly select the name associated with lowest value from data set & a random from the lowest options if tied with drop-down select filter

I have a data set on Google Sheets where for example, Name Scores Region State First Row Row Row Second Row Row Row On another tab, I have a drop-down list to select a specific Region and State. I ...
Elaine's user avatar
  • 19
2 votes
1 answer
58 views

How do I make the weight column in my medical log sheet get updated with the most recent weigh-in?

I am looking to get the weight column in my medical log sheet to be updated automatically when I key in the new weights on my Weight Log sheet. I take the weights weekly and update the info into a new ...
user307336's user avatar
1 vote
2 answers
82 views

Query+Importrange for multiple sheets based on values in Col26

=QUERY({ importrange("1HGW2VJFyhosCM-5AK7II6CRt19sPMsmGPbxEJvPVnK0","SUN!A3:AB5001"); importrange("1Fjbl3Ndh-thsb9ufNhJ2GgHjjIfvG1e7vUe4dNcwCew","SEA!A3:...
Bryson Williams's user avatar
0 votes
1 answer
227 views

Use MATCH & LARGE but with 2 identical highest values in Google Sheets

Thank you to doubleunary for quickly solving my first of two questions. I am now able to create a single cell that properly lists all of the top 5 largest value column titles separated by commas; even ...
Matthew W's user avatar
0 votes
1 answer
193 views

amending query statement difficulties

doubleunary kindly helped with the linked question however i'm struggling to amend the Query() section so as to pull different results. ie. for repairs and revenue also. Google Sheets - Formula or ...
James 'Mook' Pite's user avatar
1 vote
1 answer
34 views

How to filter data from various sheets based on dropdown selection

I am setting up a sheet to track renewable services for my company. There will be various tabs, for example, Domains, Hosting, Google Workspace and Phone Numbers. I would like to be able to filter all ...
Evie's user avatar
  • 11
1 vote
1 answer
140 views

Dynamic & extendible category names

I'm having trouble on something with Google Sheets: I need category names to be dynamic & extendible between 15-200 values, so it's not quite worth an Apps Script, would be super cool to put the ...
Virtuous Solutions's user avatar
1 vote
1 answer
126 views

Using QUERY with IMPORTRANGE as the data source not allowing referencing columns

(Link to Test Spreadsheet) I am working on a spreadsheet that has a list of people, and for each person, they have ten choices that correspond to adjacent columns with artist and song name. The data ...
Engineer_Man's user avatar
1 vote
1 answer
108 views

merge data of columns that have the same label vertically in google sheets

I have a couple of datasets with the same columns entered horizontally next to each other. I have a data / facebook leads / facebook adspend column for thee moonth of january in column a, b, c and the ...
MarioJBlidge's user avatar
1 vote
1 answer
36 views

Match taking into account characters like ä ë ï ö ü, á, é, í, ó, ú

Hey I am running against a small problem that I do not know the answer of. I have 2 lists: List 1 has all my data, for example Märio Toad Bowser Pëach Luigi List 2 has data too (but this list doesnt ...
Alawar Walherich's user avatar
0 votes
1 answer
196 views

How to repeat query for a range of data in google sheet

I have this table in Google sheet: Col A (Month) Col B (Date) January 1/1/2023 January 1/2/2023 January 1/3/2023 And I have another table: Col C (Dept.) Col D (KPI) Dep1 KPI1 Dep1 KPI2 Dep2 ...
Sarah's user avatar
  • 25
-1 votes
1 answer
63 views

Building a function that imports value for the neighbor cell of a matching cell from another spreadsheet

I’m trying to create a two-way communication “sync” system for neighboring cells with a matching cell between 2 separate Google Sheets Spreadsheets. So for example, 2 Spreadsheets called “A” and “B” ...
Joseph H's user avatar
0 votes
0 answers
31 views

How to join two datatables in google sheets?

I want to track my daily nutrition using google sheets. I have 1 sheet containing all items that I use: I want to use this in another sheet where I input how much of each product I use in a meal, ...
Friso's user avatar
  • 101
0 votes
1 answer
142 views

use a string as a parameter in a equation

I want to use a string as a parameter in an equation to get a value from another tab. For example, i want to use the string 'OKT' in the gray column as a reference inside the equation in the orange ...
Vincent's user avatar
0 votes
1 answer
23 views

Google sheets: auto sorting based on color and alphabetic order

So I have this list: B (white background) A (white background) C (green background) And I would like to make a function where I sort it as follows: C (green background) A (white background) B (...
user avatar
-1 votes
1 answer
635 views

Unable to parse query string for Function QUERY parameter 2: ADD_COL_TO_GROUP_BY_OR_AGG: Col2

I have two query formulas, with one inside the other (This is the way my manager figured it out and wants to proceed using this methodology for some reason). What's happening in the initial query ...
Lofton Gentry's user avatar
1 vote
1 answer
94 views

sheets query requires splitting last returned column by comma

Table with data in columns A-N. Data in column 'M' contains CSV data. Using this query can return data, need what's in 5th returned column (E) to be split into additional columns through one command. ...
Inetquestion's user avatar

1
2 3 4 5
19