A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
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
35 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
27 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
91 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
23 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
26 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
32 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
21 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
45 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
54 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
35 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
78 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
28 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
15 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
32 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
27 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
115 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
54 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
37 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 ...
0
votes
1answer
44 views

In Google Sheets I need to find a string and return true/false, or even a count into a cell so I can perform an action

In my example i have a column with Names of signs we product in our sighshop and I need to be able to isolate similar named signs So say I need to return a a value if the cell is Bud Light and only ...
0
votes
0answers
26 views

Unable to filter rows in a sheet populated by QUERY

I am trying to filter one column A-Z to pair items listed in more than one selling app. However, if I apply an A-Z filter for a column with item codes, the sheet rearranges everything per the filter ...
1
vote
1answer
39 views

Can I use “countIFS” with a filter?

https://docs.google.com/spreadsheets/d/1PlPmShZ4SnkxAqBTg5xY_LHaNokGaFli4Wc7MjV_Jqo/edit?usp=sharing I want to count how many paid is for city MB and then how many paid is for city UB. I tried to ...