Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
18 views

How can I encode HTML literals for output within the Sheets Query function?

I have a query in a spreadsheet. What I need to do is output a bit of simple HTML text between each column being pulled out of another tab. Sample code looks like this: =Query(Official!A7:P617, "...
0
votes
3answers
28 views

Summing every other row depending on the classification of the row above

I've got a confusing question, I can't figure it out. I have this spreadsheet of states population and their political control, each on separate alternating rows (political control is designated with ...
1
vote
2answers
37 views

Return values to another sheet if a cell is not empty, otherwise skip to next cell

I have one sheet with a list of phone extensions in which some have names associated with them and some do not. I am trying to use a function to display one the extensions with names onto another ...
0
votes
1answer
110 views

Summing hours spent on a subject in Google Sheets' “Assignment Tracker”

Hi everyone! I love the idea of this Assignment Tracker in Google Sheets. But my goal for time management is to find out how much time I'm spending on each subject. Is there a way to automatically ...
0
votes
1answer
266 views

Linking a dynamic inventory price list to another file in Google Sheets

I've got a master inventory price sheet for all the ingredients we use in our recipes that I want to reference in all our recipe files to dynamically calculate costs. This way if we change suppliers ...
1
vote
2answers
173 views

How do I get the count number of weekdays in a datetime column in Google Sheets?

I have a timestamp in mm/dd/yy hh:mm:ss format from January to March. How do I count "how many days are Monday" in the range of A1:A199? (Or How Many are Tuesday, Wednesday, Saturday? This gives me a ...
0
votes
1answer
12 views

Cross Comparing values between two columns for voting purposes

The current content of our Google sheet, is formatted as such: - PERSON 1 | PERSON 2 | YES - PERSON 1 | PERSON 3 | YES - PERSON 2 | PERSON 1 | YES - PERSON 2 | PERSON 3 | YES - PERSON 3 | PERSON 1 | ...
0
votes
1answer
42 views

SUM across other worksheets (without hardcoding their names)

I've got a spreadsheet for my organisation's management accounts. Each project has a worksheet, setting out income and expenditure by financial year. This is aggregated together in a few sheets ...
0
votes
1answer
58 views

Single QUERY for most recents of two groups

I have two sheets with the same columns. I need a single query to pull the most recent matching record from each of the two sheets. Here I'll just show them as adjacent ranges: | Stu | Date ...
-1
votes
1answer
403 views

Reconnecting form responses to current sheet after file move to Team Drive

I have searched for an answer for the "current" Google Forms problem I am having. It seems the answers here for this issue are outdated as Google keeps changing their technology about as fast as I ...
1
vote
1answer
593 views

Using QUERY and IMPORTRANGE to get a value from Col8 when some of the rows in Col1 are subheadings

I just want to return the value in Col8 corresponding to the row that has "Residential". I can only seem to get the value that is in the top row rather than the row corresponding with "Residential". =...
0
votes
1answer
309 views

ImportRange not pulling in Dates from first sheet in correct format

I'm trying to pull in a date cell from one sheet to another with an importrange function: =iferror(query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1UClhpev01z-...
-1
votes
1answer
16 views

Query with Date Selector. Both must be inputted or no information is available?

Currently using: =IFERROR({QUERY(PRI!A4:T,D1,1);QUERY(NON!A5:T,D1,0)},"Nada") This is used to grab information from two spreadsheets with the same number of columns, with PRI grabbing data with ...
1
vote
1answer
23 views

If a dropdown value is selected, how do I add it to another column and then gather the sums?

So I have a dropdown in Col K with "Apple, Oranges, Grapes, Cherries". If a kid wants to order a specific fruit, I want it to auto-populate the section of Col N in green and then sum the total ...
1
vote
1answer
349 views

Arrayformula for running MAX of separate column

I'm trying to use ARRAYFORMULA in Google Sheets to get the max figures from a separate column only up to the row being calculated each time but can't for the life of me figure out how. It sounds more ...
1
vote
1answer
39 views

Sort by a Column While Maintaining Information in a Row

I had a previous question with lots of support and it solved my problem. The next issue I have is sorting the data while bringing the information in the corresponding row with it. A link to the ...
1
vote
1answer
94 views

Report the date/column header for the first and last non-zero entries for each client/row

I have a sheet with client names (Column A) and the number of visits to the business by mm-yyyy (Columns B - AC). I'm trying to find the first month that they visited the business and the last month ...
1
vote
1answer
43 views

vlookup sum to calculate total hours assigned next to dates

I'm attempting to calculate a total number of hours based on entering 2 different dates. Each day between the dates has assigned 'hours worked'. I'm trying to use Sumproduct or Arrayformula and Sumif,...
1
vote
1answer
27 views

How can I list rows meeting a criteria and limit the amount of rows by a sum of the list?

I am trying to create a scheduling assistant within Google Sheets. I need to list out tasks currently scheduled for a specific date but there is a volume cut off that needs to happen. Client ...
0
votes
1answer
24 views

How to sum values of the same row from different columns in a separate sheet with QUERY?

I'm trying to add up the value of cells in the same row but different columns to another Google sheet. Example below:
1
vote
1answer
791 views

Using custom function in QUERY in Google Sheets results in “unknown function:”

I have a custom function written in Google Script for Sheets named sheetnames(). It returns the names of individual sheets inside a worksheet. When used in a regular cell-like =sheetnames(), it ...
1
vote
2answers
364 views

How to get a Sample Standard Deviation values that match condition?

I am trying to get the standard deviation of a subset of data. I have two sheets Name Value a 1 a 2 a 1 b 5 b 5 In a separate sheet I have: Name StdDev a b GOAL: I am trying ...
0
votes
1answer
235 views

Use of IMPORTRANGE to juxtapose variable-length lists

Let's say I have two Google sheets, Doc1 and Doc2, both having only one sheet featuring two lists with the same schema, say two columns Name and Age. The lists have an unknown number of entries, but ...
0
votes
1answer
238 views

How to count how often a value is submitted to a Google sheet via Google Forms per day, and put that count in different Google Sheets workbook

I have two Google Sheets workbooks, Workbook A and Workbook B. Workbook A is generated from a Google form that collects email addresses submitted from users. Workbook A has two columns next to each ...
0
votes
2answers
62 views

Insert column into Google sheet generated from a Google form to calculate the difference between two columns of data

I have a Google form where people track their company mileage and input "Mileage In" and "Mileage Out." I want to insert a column next to Mileage In and Mileage Out that shows the difference between ...
0
votes
2answers
25 views

Extract and graph information from a days-hours-minutes-seconds format [closed]

I'm working in Google Sheets, and I have data for race finish times in "8d 3h 40m 13s" format and I'm want to extract that information so I can plot it in a chart. Any ideas for how to achieve this? ...
0
votes
2answers
26 views

How to count duplicate names as one value in Sheets?

I have a large dataset in Sheets and I'm grouping together the data in a pivot table and have a hard time figuring out how to avoid counting every instance of a name instead of counting the name as ...
0
votes
1answer
55 views

Count attendance percentage for top 10 specific events

Data I have an attendance sheet for multiple events and for multiple people. The sheet looks like this (mock data): X | A | B | C | D | E | F | G | ... 1 | |...
1
vote
1answer
213 views

Generate custom sequence in Google Sheets

I would like to create a column with a custom sequence: A-Z,a-z,0-9. (Essentially the Base64 character set with the last two characters, + and /, left out) So, A1 would be AAA, A2 would be AAB ... ...
-1
votes
1answer
123 views

Count worked days per employee

I am creating a spreadsheet in Google Sheets for a client who wants the sheet to pull data from their Google Calendar to calculate how many days each employee works. They use Calendar for their ...
1
vote
1answer
105 views

Using Google Sheets to dynamically add entries to column based on logic?

I have a list of people that I have a drop down for if they are attending or not to an event. If "Yes" I want them to be added to another column on sheet 2. Except I don't know how to add them ...
1
vote
1answer
31 views

Conditionally format rows with partial matches

I have a Google Sheets table that looks like this: (col a) (col b) (col c) (col d) Company Cohort Session Count Group -------------------------------------------- Acme ...
1
vote
1answer
3k views

How to combine two QUERY results into one column

I have the following QUERY =query(XX!A2:A;" select A where A>'50' ") and =query(YY!A2:A;" select A where A>'50' ") how do I combine these two data sources so I don't have to have two ...
2
votes
2answers
80 views

Combining numbers based on a separate cell

I'm trying to make a sheet that records every 'player's' balance. Transactions come from a form, and I was wondering if I could run a script that Reviews each incoming data set Adds an amount [what ...
0
votes
2answers
16 views

Query function problem

When using a query function that selects two columns from another sheet, I get two results in one cell. It looks like it doesn't respect the initial structure of the selected data. Any idea, please?
2
votes
2answers
77 views

Processing data from multiple cells and comparing results with another cell

For most orders, I have multiple deliveries under the same recipient ID and I need to compare whether everything ordered is really delivered. All products are divided by a comma and have always the ...
0
votes
3answers
345 views

How to return what is missing from a 2 column list comparing against all possible combinations of 2 single column lists?

Sample data: A B C D E F 1 name handed in students assignments name missing 2 a 1 a 1 b 2 3 b 1 b 2 ...
1
vote
1answer
138 views

How to return all values (horizontal array) in B that has the same key stored in A?

Sample Sheet 1: A B 1 a x 2 a y 3 a z 4 b i 5 b h 6 b w Formula result, in Sheet 2, I am looking for: A B C D 1 a x y z 2 b i h w
2
votes
2answers
657 views

Query returning #VALUE error when the column has no values

The below query returns #VALUE error when the column H is completely empty: =QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, ' ', ' ', ' ', G, ' ', ' ', ' ', '"&...
2
votes
1answer
72 views

Return first vertical value that matches given criterion

On Google Sheets, I would like to figure out a way to have a cell return the first value in column C where the same row in column D matches the criterion ("Blue" for example, as opposed to "Red"). I ...
1
vote
1answer
567 views

How to multiply a query column by a cell refference on Google Sheets?

Below is the query that I use, and it works: =QUERY(Input!A2:I33; "SELECT '000', A, B, C, D, E, ' ', F, ' ', ' ', ' ', G, ' ', ' ', ' ', '"&TEXT(Input!L8;"dd/mm/yyyy")&"'...
1
vote
1answer
203 views

How to return unique list of text pivot by Query

I'm trying to mimic a pivot table behaviour where you would grab all distinct values from Col B and, by each of those unique Col B values, you associate all each respective values, that must also be ...
1
vote
1answer
236 views

Query Formula excludes results with new line character

It seems there may be a bug in Google Sheets' Query formula where results with a new line character (char 10) are excluded when the column is filtered. Is there any way around this or am I doing ...
1
vote
1answer
65 views

Combing two ranges with identical column structure from two different sheets (in same spreadsheet)

I'm doing a simple ticketing system, where I have two sheets, "Form Responses" and "Email Responses" both of which are populated automatically as their names imply (one from a form, one from an email ...
1
vote
1answer
212 views

How to combine dynamic columns in Google Sheets while retaining data in other rows?

I have a Google Form that asks people to fill out their name, gender, and then their size preference for shirts and pants. The form automatically outputs the 2 sizes in separate columns. I want to ...
3
votes
3answers
3k views

Automatic currency conversion in Google Sheets

I have a personal accounting sheet that is mainly accounting in Euros, but for every manual Euro (EUR) entry I do, I want to have that entry converted to USD in the previous column next to it ...
1
vote
1answer
5k views

QUERY error NO_COLUMN:col1 [duplicate]

=query(importrange("12HAbnTv4g_6m1UeCOs9ZO0gAYvy8tlaXr-cc2ecPEic","Master!A:ba"), "select col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col53 where col53<> ...
1
vote
1answer
5k views

Pie chart with sub-slices per category in Google Sheets?

I have a sheet that contains a list of books, with metadata about those books. Two columns that exist are the library within which the book is stored, and the condition of the book. I'd like to create ...
0
votes
4answers
64 views

How to pull the page number and the number of images listed from one sheet and then populate cells in another sheet accordingly?

Sorry, I didn't know how to summarize it in the title. Basically, I have a spreadsheet where I'm keeping track of the number of images that need to be on each page of a document I'm working on. On ...
0
votes
2answers
886 views

Problem with QUERY(IMPORTRANGE()) to retrieve data

I’m having a problem with the =QUERY(IMPORTRANGE()) formula while retrieving a column data from one spreadsheet to another. =QUERY(IMPORTRANGE("spreedsheetURL","Sheet1!A2"),"select Col2 WHERE Col1 ...

1
5 6
7
8 9
13