Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
563
questions
0
votes
1answer
10 views
Google Sheets Query - Group by more than one column without a pivot table
I have a timesheet dataset containing 4 columns:
Column 1 - A workers name
Column 2 - The date they worked
Column 3 - The project they worked on
Column 4 - The hours they worked on that date, on ...
0
votes
2answers
39 views
Extract the Day of the month (dd) from a date in Query()
I have a list of transactions for a month, all include a date (Column B - dd/mm/yyyy) and a £in figure (Column F). I want to create a table that shows the total taken on each day of the month so for ...
1
vote
1answer
16 views
Sumif and filter using regexmatch with multiple criteria
I need to sum specific cells in 1 column (Q) based on if the other columns (R, S) have a certain word(s) within the cell.
For example in the picture if a cell in R or S contains the word toy or green,...
0
votes
0answers
8 views
Google Sheets - Preventing certain rows from being sorted
I'm trying to make a list of all my games and where they're from to try to kill somewhat of a backlog.
The main problem I'm having is the sorting function since I don't know how to freeze multiple ...
0
votes
1answer
26 views
Pretty print google sheet formula?
Finding answers on Web Apps SE or Reddit is wonderful, but if you don't understand them you can't modify them.
I recently came across the following as part of an answer:
={"Corrected List 2";...
1
vote
1answer
38 views
How to query on a Google Sheets to sort by month
I'm using this Sheetgo Attendance template on Google Sheets, but it's designed for US Schools so it's needed to sort by January to December. I will provide below the assets that I've have about it. ...
0
votes
0answers
13 views
My function links all required cells between google sheets except one!
I have this query
=query(IMPORTRANGE("<https://docs.google.com/spreadsheets/d/1uxIkcjOTxFAd25Ukzji3VJKoFbAKybuFXfHlBzaGDWc/edit#gid=1385200762>","Master!A3:y"),"select Col2, Col3, Col12, Col13"...
0
votes
0answers
16 views
Output word within a string of words, if it matches to a value within a named range
I have a list of search queries for cars like:
hyundai ioniq hybrid
rav4 hybrid
hyundai ioniq electric
toyota yaris petrol
vauxhall automatic car
I also have a list of models of the cars (ioniq, ...
0
votes
0answers
28 views
Google Sheets - Multiple outcomes using If And statements?
I'm working on categorizing tires and rims that I own. I want the sheet to look into the "Width", "Ratio" and "Rim Size" columns to provide me with data to determine which car it would fit on.
For ...
1
vote
1answer
95 views
Google Sheets calculate average on variable number of non-adjacent columns?
A Google Sheet is being used to insert markings for students tests.
When a single test is done by students, teacher assigns multiple grades for it. For instance, one marking for writing, one for ...
2
votes
0answers
29 views
How to use COUNTIF and UNIQUE
I have a Google Sheet with two columns. One containing a list with order numbers - the order numbers can be listed several times/duplicates. The other column consists of the a free text with the name ...
1
vote
1answer
36 views
How to populate column with every unique instance in another column
I have a bunch of data on users and the user's name will be in Column A. The user's will always be unique, but they may show up more than once. I want to have another 2 columns (say E and F) that ...
2
votes
1answer
25 views
QUERY for two columns
I am trying to use one query to search in two nonconsecutive columns.
So far I have the below formula.
=QUERY(combine!A1:D,"SELECT * WHERE LOWER(A) LIKE LOWER("""&B1&""") or SELECT * WHERE ...
2
votes
1answer
25 views
If cell contains this text return that cell value
Look at the example I have below.
A-.375-VG-M761
A-.375-VG-O630
A-.375-VG-O780
A-.75-VG-602
A-.75-VG-603
A-.75-VG-604
In one column I have two "SKUs": A-.375-VG and A-.75-VG.
...
1
vote
0answers
16 views
Summarize all data from other tabs with same format and column
I have customer sheets which i must summarize into a single tab so i use this query;
=QUERY({'ROC155'!B2:AB;'ROC152'!B2:AB;'ROC135'!B2:AB;'ROC002'!B2:AB;'ROC009'!B2:AB;'ROC145'!B2:AB;'ROC048'!B2:AB;'...
1
vote
0answers
27 views
How to filter a query by one more column
As a data analyst, I'm trying to get a specific string from Google Sheets with a query, so that my table can be more filtered with useful information.
This is my current query which is working, but ...
2
votes
1answer
36 views
How to find, out of many days, 1 day that is most profitable on Google Sheets?
Ok, I've been looking for this type of formula but not finding it. So here I am asking for help.
I'm a day trader on stocks and I want to analyze my performance. For example:
+-----------------+-----...
1
vote
0answers
10 views
How do I auto timestamp a sheet when something is moved to it
I am using this function to automatically move a row to a secondary sheet when a set value is entered.
function onEdit() {
var sheetNameToWatch = "Tracker";
var columnNumberToWatch = 4;
var ...
1
vote
1answer
35 views
Dynamic Query using TRUE or FALSE in Google Sheets
I have a checkbox in Column R. (values are TRUE OR FASLE).
I want a dynamic query. I will ask the user to select a value in B2 where "true" or "false" is selected.
The below query is working (no ...
1
vote
0answers
13 views
Date Formatting Issue in Google Sheets Query
I created a shared shipment tracker with google sheets where updates are made in a master tab, then filtered into several regional tabs via query function to better visualize shipment status by region....
0
votes
1answer
41 views
Need to create a formula with the operand in a different cell in Google Sheets
For eg .
Existing formula which works
Query(A4:K,"Select A Where I = > 0.3")
should be replaced by
Query(A4:K,"Select A Where I = "&N1&"")
with cell N1 containing value '>0.3'
Appreciate ...
0
votes
1answer
34 views
Converting multiple columns to list
I'm currently using Google Sheets and I'm capturing data with Google Forms.
What I'm receiving from Forms, inside the corresponding sheets is an entry with timestamp, clientID, item1, quantity1, ...
1
vote
1answer
22 views
Can someone help me with this google sheets group by query?
I have 3 columns. I would like to sum up the total revenue of Revenue by month in Dec-2019 (As you can see there are 2). And also sum up the Net profit by month(As you can see there are 2), last but ...
1
vote
1answer
27 views
Find value in 2 different sheets
So, as you can see from the example google sheet below, is there any way to have a formula in which when you try to find the value from both sheets (Example 1, Example 2; if lets say in Example 3, ...
0
votes
0answers
20 views
Combining Duplicate Data Within A Table? | Google Sheets
Table Data:
This is a dulled down version of the Data Table I am trying to filter - which is a massive list of leads.
Basically, I am trying to find a way to combine all rows with similar contact ...
1
vote
0answers
46 views
Create new sheet with a “template” based on cell value
I'm trying to do run some stats on the AFL (Australian Football league) here in Australia and want to get some better understanding on players form based on some key stats.=
In order to do this, I ...
2
votes
0answers
55 views
Sheets QUERY (Select, Where, Contains, Limit) that works, except when blank cell
I have a Google sheets QUERY formula that pretty much works:
=query(data,"SELECT A WHERE B contains '"& A5 &"' LIMIT 10",1)
Essentially what the query is doing is, for the given data range, ...
0
votes
0answers
15 views
New solution to identify BY as a column label instead of an identifier?
I'm new to this, so I've posted this question as an "answer" to this post
QUERY error for BY column
a previous solution for this issue no longer works, and I need a new way to tell google (via code) ...
0
votes
0answers
37 views
Google Sheets - Query - Sum and group by multiple columns into single column
I have 4 separate column that represents in and out transactions..
Columns
A, B, C, D
a = transaction out (some dollar amount)
b = account type (checking, savings, credit)
c = transaction in (some ...
0
votes
1answer
22 views
Google Sheets SUMIFS issue
I'm trying to do a SUMIFS where one of the criteria has multiple strings in the same column, but the others are just based upon 1 criteria. As you can probably guess I'm having the issue that only ...
0
votes
0answers
28 views
Splitting multi-line cells with formulas - syntax problems
I have a data set, that is constantly updated/changing, that I would like to have one column's data separated into multiple rows (and copying adjacent columns to the rows);
I tried to use a script ...
0
votes
1answer
35 views
Ways to iterate and append a date calculation a set number of times in Google Sheets
Let's say I have a sheet with this dataset:
Start Date Number of Days
3/1/2019 2
From this input set, I want to make the following transformation:
Date
3/1/2019
3/2/2019
3/3/2019
A ...
0
votes
0answers
38 views
Google Sheets trying to combine and un-combine text join
Unfortunatly due to security I cannot share the page, but I am using the following codes:
=ARRAY_CONSTRAIN(
ARRAYFORMULA(iferror(
TEXTJOIN(",",TRUE,unique(IF(($D4=Section!C$2:AW$2),Section!C$3:AW$4,"...
-1
votes
1answer
87 views
UNIQUE ,INDEX AND IMPORTRANGE
I am stuck. I use the import range to get a set of data however now I need to the unique values that that matches the import range that I have.
Say for example below is randome sample. I have import ...
1
vote
1answer
40 views
Count query based on multiple conditions
I have a spreadsheet that I use for my World of Warcraft guild, and I've hit a bit of a wall while trying to add new functionality to it.
For reference, when our guild goes through dungeons, we ...
0
votes
0answers
30 views
Query Import Range returns two rows
I've been using =Query(Importrange( functions for quite a while now and something I can't figure out is why the import is returning with two rows. Here's the formula I've been using:
=QUERY(...
0
votes
1answer
16 views
Moving Rows of Google Form Responses to a new Sheet based on a specific answer in a column
I have a Google Sheet collecting responses from a Google Form. Based on answers in Column C (Col3), I would like the entire row imported to a completely different Google Sheet.
I figured out a ...
0
votes
0answers
10 views
Change entire sheet information based on input of one cell
I'm trying to create a function on a secondary sheet to monitor which of my blogs are getting the most traffic. Currently my secondary sheet has a formula that works to show me blogs that have over ...
2
votes
1answer
33 views
How to automatically calculate averages from ID in Google Sheet?
I've been looking around for an answer but couldn't find what I was looking for.
The problem is that I want to use an arrayformula, but can't figure out a dynamic formula that:
Looks at the unique ...
1
vote
1answer
13 views
help with query finding multiple entries in 1 column
I am trying to query several different colors from one column. This is what I have so far:
=query('Raw Data'!C1:J1000,
"Select E, D, I, J, C, F, H
where H contains 'Monday'
and F='Green'
...
0
votes
1answer
30 views
Google Sheets: Formula to get data from other tab
I have a Google Sheet to get an overview of all workshops/events we are hosting.
There is a tab INPUT. There is a column for Date, Month, Event, Location, Presenter, Language. The column month is ...
0
votes
0answers
11 views
Google Sheets data not loading in pdf
I have a Google Sheets with some tabs, but not yet a massive amount of information.
I have one tab where data is put in and then two tabs where this data is being displayed by a query formula. This ...
1
vote
0answers
13 views
Using a query on an importrange, not pulling information
I am trying to do a query on importrange but it is only showing me the headers not any of the data. I am really stuck.
=QUERY(IMPORTRANGE("19Nx2EtMktFW-xwNMjY4IiQpN33QHrXIwcqDsvKqCiHo", "MASTER!A1:...
1
vote
0answers
29 views
Multiple columns and get unique on preferred column
I want to get 3 columns only if a preferred column is unique.
What is the correct syntax in Google Sheets?
=Query('Sales'!A:AX,"Select A, B, C where A is unique")
1
vote
2answers
130 views
Select All Columns Where Cell In Columns Contain A Word - Google Sheets
I am looking for a query formula that will allow me to select all columns (entire column) that contain a specific word within the column.
You can see a test sheet here: https://docs.google.com/...
2
votes
1answer
31 views
Google Sheets Query: Making the selected column depend on formula
I would like to use a normal QUERY with where operating in the usual way where A = 'Speed' but with the selected column being determined based on a formula. Specifically, I want to return only the ...
2
votes
2answers
55 views
How can I filter a list based on whether it contains any of a list of substrings?
I have a list of Japanese words. I want to produce a formula to filter that list, based on a filter list of substrings (kanji characters) which I'm looking for.
So for example, say my filter list ...
0
votes
0answers
38 views
Loading Data Error Query with Importrange Gsheet
I am trying to use QUERY with importrange function to fetch custom data from another spreadsheet, it worked for a while but now it through loading data error consistently. if I refresh the sheet it ...
0
votes
1answer
27 views
Query with sums and 2 columns on the left
I have problem with the query:
=Query(Raw_Data!J$2:$BL,"Select J, Sum(U),Sum(V),Sum(W),Sum(X),Sum(Y),Sum(Z),Sum(AA),Sum(AB),Sum(AC),Sum(AD),Sum(AE),Sum(AF) where J contains 'CUS' and (AL='1-Revenues'...
0
votes
1answer
14 views
Counting with Query
I am using queries, in order to create a dashboard,
=query('Usuarios icorp'!I2:M522; "count(F) when F contains '"&B3&"' ", -1)
I am having issues with my query function.
I want to count how ...
