The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
1
vote
2answers
15 views

How to get a true when a character is NOT in range

I want to filter all cells in a range which are not empty and does not contain a certain character. So I tried everything with regexmatch =filter(Planning!G$5:G$37; regexmatch(Planning!G$5:G$37 ; "![...
2
votes
1answer
35 views

Comparing Unique Values to a Range

I have the following formula =CONCATENATE (FILTER(boxmod!AC2:AC, boxmod!I2:AB=concatboxmod!C2 )) The intention of this formula is to take a range and compare it to a list of unique identifiers on ...
0
votes
2answers
19 views

Copy other cells if EITHER column has specific text

In a single sheet wanting to copy D3:N if either B3:B or C3:C = S2. I'm sure there is a simple formula to do this I am just unable to find it!
1
vote
2answers
63 views

Copy the contents of a row to another sheet based on a specific value

I have a Google sheet with various rows of data, in one column (Column E), the values for each row follow the following pattern: A1 A2 B1 B2 C1 C2 etc. I want to copy the rows ...
1
vote
1answer
58 views

Left join 2 sheets on 2 key values, add constant value

I'd like to join by date on these 2 sheets (Income & Expense), on key values month and day and add constant value from FixedCost sheet, Income: Expense: Add FixedCost to every row/date: ...
1
vote
1answer
695 views

Google Sheet Query smarter way to get multiple criteria

I use a spreadsheet where I want a list of items which contains multiple criteria. (I need all cells in a range which contains the letter 'a - g' The formula I use now is =query(Planning!G5:G37; "...
1
vote
1answer
20 views

Link to a date cell in QUERY criteria

I have the following formula and it works fine =unique(query(ARRAY_CONSTRAIN(ARRAYFORMULA(QUERY( {Database!A1:AB, (Database!N1:N)-(Database!M1:M)}, "where Col9 <> '' AND Col1 >= date'2019-...
0
votes
2answers
16 views

Importing data found to be true from one Google sheet to another

I am trying to find a way to import data (text) from one sheet to another, via the same book. I assume it's going to need an IF or IFS formula because I only want data is that found to be "TRUE" (via ...
1
vote
1answer
28 views

Averaging cells that have both blanks and numbers for values that are in non continous columns with nine columns in between them

I need help averaging cells that contain numbers ranging from -1200 to 1200 approximately, or blank values only. I will be averaging them by groupings of 100 increments ie. -1200 to -1100, -1100 to-...
0
votes
1answer
13 views

How to find a string then find the sum of all cells next to the found string

So I'm trying to get G4 to automatically find "Bananas" in all of C and for every time it finds a value it adds up the corresponding number in D.
0
votes
1answer
662 views

Pivot Table Filters - Custom Formula

In my pivot table, I want to only display the entries in the column "Which Event" if the name contains "Baseball" or "Softball". I can very easily do part of this by adding a filter for the field "...
0
votes
1answer
295 views

QUERY with Week Number

I use query to extract two columns from another sheet with data. Column A has dates Column B - numbers I want to extract only rows that correspond to a specific calendar week number. For example 19. ...
0
votes
1answer
20 views

Google Sheets Pivot Table, Summary by Day

I have a sheet with 2500 dates & times of when customers have placed their order. I want to do a heatmap or a bar graph to show which days of the week are the busiest. I rounded the exact times ...
0
votes
1answer
13 views

How do I turn a SQL database downloaded table into a Google Sheets multi-line chart?

I want to turn this table (comes from an SQL database) ...into a line chart in Google Sheets that looks like this
1
vote
1answer
192 views

Query select all values of specific month

A have a table where Column A has dates. I want to get all values of a specific month I have the following query for May for example =query(Database!A1:AD,"Select A,E,F,U,V,W,X,AB,AC where Month(A) =...
0
votes
0answers
34 views

ARRAYFORMULA + QUERY + IMPORTRANGE - Final cell reference in IMPORTRANGE is acting like it's absolute when I need it to be relative

I know I'm chaining a lot together here, but it's SO close to functional. Here is my formula with the doc address altered for privacy. =ARRAYFORMULA(IF(ISBLANK($AE$3:$AE),(query(importrange("https://...
1
vote
1answer
82 views

Google Sheets Formula Import Range with Indirect

I have this formula and want the reference to Archery to reference Archery which will be in Cell E1. I tried using the indirect formula but can't get it to work with the formatting for Google Sheets. ...
1
vote
2answers
120 views

How to return values in a formula, from rows of latest 4 nonconsecutive dates recorded

Using an ARRAY_CONSTRAIN formula we want to capture the latest 9 records (rows) over a period of nonconsecutive dates. =ARRAY_CONSTRAIN(sort(vlookup(query({row(EBPP!D:D),sort(EBPP!A:E)},Q15,0), {...
1
vote
1answer
20 views

Query with dropdown not working

In my Google sheet I'm getting an error: Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: I've added a dropdown so the formula will auto-update depending on the tab ...
0
votes
2answers
97 views

How to use IMPORTRANGE when I have multiple tabs?

I have to export some data from a Sheet with 30 tabs(a tab for each day of the months) and I have to extract data under specific criteria from all the sheets which have to be done every month on a new ...
1
vote
1answer
11 views

Set certain days of the week on repeat [closed]

I'm making an attendance sheet for my team, how do I make only certain days like Mon Tue Wed appear in the top column?
0
votes
1answer
18 views

Query Function Not Putting Consolidated Data in the Right Place

So I don't know how to explain this completely, but basically, I'm using the query function to consolidate data from multiple sheets into one Master Sheet. The function itself seems to be working fine ...
1
vote
1answer
18 views

Rotate and duplicate row values in Google Sheets

I'm trying to rotate values in a sheet and then duplicate the first value in the row as a header. For example: a | b | c | d would become: a | b ----- a | c ----- a | d Is there any way to do this ...
0
votes
1answer
14 views

How to pull data from a random cell on page load in Google Sheets?

Basically, I'm just trying to figure out how to make a cell pull a random cell's value within a range at random when the page loads. For example, have cell A1 pull a cell at random from range B1:B20 ...
0
votes
1answer
26 views

Filter column based in other column (using query prefer)

I am trying to make a filter with a query based in a column, but I cannot make it works. https://docs.google.com/spreadsheets/d/1zKMW8vuyHRpS8eVEmRgAOg_dBQ0r2OIXnqV3_TFEm9o/edit?usp=sharing This is ...
1
vote
1answer
29 views

Is there a formula to list all possible 2 card combinations of 4 playing cards in Google Sheets?

Example: Is there a formula to list all possible 2 card combinations of 4 playing cards? 4 playing cards: A♥ A♦ A♣ A♠ The result should be 6 combinations only (Not 4 x 4 = 16 Combinations): 1: A♥ ...
1
vote
1answer
36 views

How to import/merge data from from 2 separate sheets from another spreadsheet

I need to import data from "Spreadsheet A" to "Spreadsheet B" but all the data is in two separate sheets "MY" and "NY" in "Spreadsheet A" I basically need exactly this but with a function to import ...
1
vote
1answer
20 views

How can I have Google Sheets automatically parse and calculate text that follows a certain pattern?

I have the following sheet: https://docs.google.com/spreadsheets/d/1iEMjWVEwyuqbn_imW1UZ-AF2NlE__IbRZdHQifKJjko/edit?usp=sharing I would like to calculate "Total Work" by deriving it from my own ...
1
vote
1answer
41 views

Trying to Query a list against another list

First time ever using stack exchange so please excuse any formatting problems. I'm trying to create a sheet where Sheet1 is a dump of data and Sheet2 contains: A date range A list of items True/...
0
votes
2answers
24 views

Add columns based on rows

I'm currently working on setting up a sheet, but I want to add columns in one worksheet based on rows in another worksheet. I have no idea if this is even possible, I've tried googling it. Might be ...
1
vote
1answer
37 views

How to group values ​by month and sum previous values?

I would like to sum the values ​​of a table grouped by months but adding up the previous months. Example: Result that I want: The closest I've come so far is: =Query(B2:C4;"select sum(C), B group ...
1
vote
1answer
26 views

Analyse multiple columns per row

I need to combine the data of three different columns. Thus, I have this: ------------------------------------------------------- | ID | ID Data | Markdown 1 | Markdown 2 | Markdown 3 | --------------...
1
vote
1answer
35 views

Google Sheets Query Where Cell value

I'm looking for help editing a Google Sheets formula. I want the formula to Import Range from another worksheet where value equals a specified cell. I can only make the where in the formula equal a ...
0
votes
2answers
150 views

Using a checkbox to auto-populate a row on another sheet

I have this spreadsheet which is used for receiving and recording work. SST responses sheet is where people submit requests, Workload is where we record all work. I'm wanting it so that when someone ...
0
votes
1answer
14 views

How can I have a 2nd function start in the cell after the output of the 1st function?

So the problem I am trying to solve is where I have Google finance fetching the price of a stock and outputting it, but the length of cells of its price history is increased by 1 each day. In the ...
0
votes
0answers
15 views

Edited Google Form questions not same when open in spreadsheet

I used google form to create few surveys. I do some edit on the questions, questions sequence, etc. However, the questions sequence in spreadsheet is not the same as in google form. If there are new ...
1
vote
1answer
26 views

Search max value of column after individual operation on each value of that column using arrayformula or similar

I have a Google Sheets with a column D with identifiers in a certain format (i.e. Y19_XXX): Y19_234 Y19_213 Y19_814 Y19_215 Y19_817 ... Now I want to determine the highest value XXX in that column ...
0
votes
2answers
197 views

Applying IMPORTRANGE formula in Google Sheets between tabs

I have one Google sheet having three tabs, Sheet 1, Sheet 2, Sheet 3. I have another tab having the name "Sheet 4" in which I want to import all data from these three tabs, but the problem is data in ...
1
vote
3answers
41 views

How to use FILTER to select only values repeated more than X times

I am trying to use the filter function to select from 2 columns, only the values repeated. I will give you an example: My column is like: Names Jobs Value - Gerard player 4 Gerard player 2 ...
1
vote
0answers
121 views

Google Spreadsheets - Query Select A, B, MIN(C), group by A only

thanks for the help! Sorry if I'm expressing myself in a confusing manner, I'm a beginner at this... I'm trying to create a spreadsheet to help me plan and analyze grocery expenses more efficiently. ...
0
votes
1answer
67 views

Filtering a QUERY using MATCH - how to pass column value into function? Or is there a better way?

I want to get a subset of data from another tab. Eventually, this will become a script and pull data from a different sheet. I can use QUERY to get all the data available, but I only want to return ...
2
votes
1answer
26 views

How to SUMIFS text beginning with apostrophe

I am receiving a list of products from an external data source. One of the product names begins with an apostrophe. When I attempt to produce a summary of the products and total quantity using UNIQUE()...
0
votes
1answer
42 views

Average first 3 values excluding empty cells in google sheets

Trying to find the average of the first 3 values in the column. I'm already running a query to find the average data within the last 3 days =average(query({$A$10:$A$107,$E$10:$E$107},"Select avg(...
1
vote
1answer
19 views

Shortening this Formula, and Future Proofing it

I am creating a Google Sheet to use as an employee schedule. On this sheet, I have Cells which will contain the Total number of Hours worked per day by all employees. At the moment the formula I ...
1
vote
1answer
479 views

Query(Importrange) or Query with dates

I am trying to show or limit what is seen to just entries made in 2019. I found a few places that referenced this and have tried the following without success. =query(IMPORTRANGE("1bSyor-...
2
votes
1answer
69 views

Finding the average from the last 14 days in Google Sheets

I would like to figure out a formula that will look at the last 14 days of data in column D and calculate the average. But I would like it to dynamically update every day so it's always showing the ...
0
votes
2answers
48 views

Can Weekday value be shown in natural (Su, Mo, Tu..) format in Array Formula?

For Google sheet with the following data in A2:B4 06/29/2019 1:25 PM 1 06/30/2019 1:32 PM 1 07/01/2019 1:40 PM 1 The output will show Monday 1 1 Saturday 1 1 Sunday 1 1 Can this be ...
0
votes
1answer
134 views

Count in QUERY to include counts for all unique items (dates)

=query(A:B,"Select A, Count(B) where A is not null and B='A' group by A,1")
0
votes
1answer
363 views

Equivalent of IMPORTRANGE with script in Google Sheets [closed]

Since the Importrange() is not working as expected, do you know how I could express e.g the following function with a script? ={IMPORTRANGE("SheetKey","SheetName1!A:J") ; IMPORTRANGE("SheetKey","...
0
votes
1answer
64 views

Dynamically reference DATA in Query in Google Sheets

I have a query in a Google Sheet structured like so: =QUERY({IMPORTRANGE(Expenses_2018Q4,"DATA_Expenses"); IMPORTRANGE(Expenses_2019Q1,"DATA_Expenses");IMPORTRANGE(Expenses_2019Q2,"DATA_Expenses")},"...