Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
0
votes
1answer
5 views
SUMPRODUCT inside ArrayFormula
I have a fixed vector, say $A$1:$E$1
I also have a vector per row, say A2:E2, A3:E3, A4:E4...
Now I want output in Column F as
F2 = sumproduct(A1:E1,A2:E2)
F3 = sumproduct(A1:E1,A3:E3)
F4 = ...
0
votes
1answer
9 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.
...
1
vote
1answer
13 views
How to count cell with upper leter in array?
I need to count the upper letter if exist in a particular cell. So my results should be as on the image below. important is ignore in count empty cell.
I noticed that task is simple if I can use ...
0
votes
1answer
14 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 ...
1
vote
1answer
16 views
Сopy cells depending on values in another cell
In Google Sheets I try to copy cells B2:B4 depending on values in other cells C2:C4.
For example - example.xlsx made in Excel.
But stuck and can't find a solution on how to do it in Google Sheets.
...
0
votes
0answers
9 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://...
0
votes
1answer
10 views
Google Sheets populate X cells with formulas based on single cell containing value X?
Google sheets dynamically populate cells based on single cell value. I don't know what the term would be called and a bunch of searching hasn't resulted in any lead on what it would be called.
What I ...
1
vote
1answer
6 views
Set certain days of the week on repeat [on hold]
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?
1
vote
1answer
11 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 ...
1
vote
1answer
14 views
How can I track the time it takes for someone to fill out a Google form?
I am running a study for my thesis, and would very much like to track the time it takes to complete a Google form to compare against how long it takes to fill.
0
votes
1answer
8 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
6 views
How to paste a comma separated string into one column in Google Sheets?
For example, if I have (bob, china, bing, bong). I want to put bob on top of china on top of bing on top of bong.
1
vote
1answer
18 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
2answers
19 views
Reverse subtraction if less than zero (negative)
I have a simple Google spreadsheet that Takes the estimated cost for a service provided and compares it to the actual cost of the service. the equation I have in the Q column is =K4-P4. When the ...
0
votes
1answer
17 views
How can I use ARRAYFORMULA (or something similar) with GOOGLETRANSLATE?
I'm learning a new language and I recently discovered Google Sheets' GOOGLETRANSLATE function. I thought it'd be neat to have a personal dictionary of translations which I can even annotate. So I ...
0
votes
1answer
15 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
18 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 ...
0
votes
2answers
21 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
13 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
36 views
COUNT UNIQUE values by group ONLY IF NOT already counted in previous group
I hope to explain clearly what I'm trying to do.
I have some data in column B, grouped as shown in column A.
I'd like to count the unique values for each group present in column A, not taking into ...
1
vote
1answer
17 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 |
--------------...
0
votes
1answer
14 views
Increment number in array formula based on a condition
I have a data set with Sub-Total rows at various intervals. As the data can have rows inserted or deleted, I am trying to use an array formula to give each group of data an incremented integer as a ...
0
votes
1answer
9 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
1answer
31 views
How to use ARRAYFORMULA and UNIQUE together in Google Sheets
How can I expand the UNIQUE function using ARRAYFORMULA?
Example:
Here is my data set, also can be found here.
I want to see the unique values of each row in next columns.
CL1 | CL2 | CL3 | CL4
JAN |...
0
votes
1answer
12 views
How to auto-calculate without a need to drag down the formula?
How can I fix formula to a row so that when data is inputted in any cell within that column, it auto-computed in Google Sheets? the % doesn't work
Without having to drag the formula down.
Example:
...
-1
votes
2answers
17 views
Google form wrongly delete rows in second sheet
I copied a previous question here since it's exactly the same as what I want to ask:
I created a form in Google Docs. Then I created a second sheet to pick
and process data from the first sheet ...
1
vote
1answer
8 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
1answer
9 views
Auto-increment where cell values not NULL
I have a list of items purchased, like below.
A | B | C | D | E |
id | item | qty | unit price | subtotal |
I'd like to have the column id to ...
2
votes
1answer
18 views
Add missing dates to list of dates with values
Current Data:
9/20/2018 6
9/22/2018 3
9/23/2018 1
Desired Output:
9/20/2018 6
9/21/2018
9/22/2018 3
9/23/2018 1
I would like to have the missing dates added to Column A while the ...
1
vote
1answer
15 views
Box change depending on drop-down list validation
For this example, we will use the top line of data so...
Type = H6
Time Depart = I6
Time landed = J6
Single Engine In Command = K6
Single Engine Dual = L6
My plan is to add I6 and J6 into L6 if H6 ...
0
votes
1answer
21 views
Auto-populate cells based on previous cell values in Google Sheets
I have two columns with information. Values in Column B have been manually added to match the values in Column A.
Is it possible to have a function that auto-populates column B with the right values ...
1
vote
1answer
10 views
Blank Box If No Data Inputted
This might sound hugely amateurish but I have a spreadsheet for my Pilot Log Book as I am a student. I have boxes stating my Take Off time and Landing time. In this case:
13:40 (H5) to 14:40 (I5)
...
1
vote
1answer
17 views
Insert a weekday in Google Forms' date selector
We have created a Google form that works perfectly, there is only one issue, and that's the DATE selector, it sends the DATE, but we would like to receive the DATE and the WEEKDAY in the form we ...
2
votes
1answer
13 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
19 views
Remove all text before (and excluding) match
Hi I have a cell with data such as:
Dropped pin
Near Hughanden Village Hall, Pome Ln, Hughanden, Low Slokey, HT11 3AT
https://goo.gl/maps/dVtKw423RBA9fkGs7
I would like to use REGEXREPLACE (or one ...
1
vote
1answer
15 views
How to find the average of difference between two columns?
Imagine you have a Google sheet with two columns, each holding a time - let's say one is bus departure and the other is bus arrival.
Is there any elegant way to present the average of all the time ...
1
vote
1answer
21 views
Alternatives to dragging formula down?
I'm having trouble applying the following formulas to entire columns in Google Sheets. I think that an array is the best option because there are 5000+ rows, but any help is appreciated. I'm really ...
1
vote
1answer
18 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
2answers
29 views
ArrayFormula combined with ISBLANK
Kind of new here. Tried to look for a similar answer but not quite getting it to work.
I made a formula - it worked.
I turned it into an array formula - it worked.
But it was returning 0's in the ...
0
votes
2answers
31 views
Custom SHEETNAME function not working in Arrayformula
I have been successfully deploying the sheetname function outlined in Display sheet name in Google Spreadsheets for a couple of years. It has allowed me to have several sheets collecting information ...
0
votes
1answer
7 views
Percentage from subtraction
I currently have an issue with a statistics sheet for a sports team. If I have a total amount of practices, with the number of days missed subtracted for each player. How do I turn that into a ...
0
votes
1answer
20 views
How to use arrayformula to find and get data from only a certain, dynamic set of columns?
I have a table where my headers are dynamically generated from another source and can show up with any header values, in any order.
In the example below I've got the header values:
AA - BB - CC - ...
0
votes
2answers
5 views
Look for a value that might be in many columns
Say I have a value in $A, to match to another sheet of the same file. That value (in this case, a student id) might appear in many columns of the other sheet.
In the following example, I am looking ...
0
votes
1answer
22 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
23 views
Look up a list embedded in a Google Sheets from a Google Forms, and add attributes to the corresponding row?
I'm running an event for a large number of children and need a way to let them register quickly as they come in, to the event.
We're expecting about 400 kids who have pre-enrolled and we will have a ...
0
votes
1answer
22 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
2answers
17 views
Prevent shifting of rows in auto-updating sheet
I am struggling with an automatically updating spreadsheet that is connected to a Google Form.
Whenever there is a new accomplished answer in the Form it is adding a row in the spreadsheet, resulting ...
0
votes
1answer
5 views
Sorting column A with drop-down and column B with checkbox
I have a spreadsheet that I use to run a foosball tournament, and in column A I have a dropdown data validation that will let me select a players name. In Column B I have a checkbox that I check once ...
1
vote
2answers
16 views
Google Sheets' Duration Input issue
We have been using Apple Numbers which seems to have this duration thing figured out a little better than Google, (either that or I'm missing something). In Apple Numbers, if I type into a cell with a ...
0
votes
1answer
16 views
Subtract different percentage based on quantity range
I want to be able to provide a different percentage discount based on the quantity purchased.
The percentage should be based on a range, so for example:
QTY > 0 && <= 5, discount = 5%
QTY >...
