Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
924
questions
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&...
- 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 ...
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 ...
- 21
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 ...
- 119
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
...
- 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
...
- 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 ...
- 1
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 £...
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 ...
- 101
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 ...
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;
...
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 (...
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 ...
- 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 ...
- 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 ...
- 19
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 ...
- 1
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 ...
- 11
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/...
- 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 ...
- 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
...
- 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 ...
- 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. ...
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
...
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
...
- 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 ...
- 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 -&...
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
...
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 ...
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 ...
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 ...
- 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>=...
- 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 ...
- 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 ...
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 ...
- 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 ...
- 23
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:...
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 ...
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 ...
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 ...
- 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 ...
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 ...
- 13
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 ...
- 35
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 ...
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
...
- 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” ...
- 1
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, ...
- 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 ...
- 1
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 (...
user305241
-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 ...
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. ...
- 11