Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
731
questions
0
votes
1
answer
595
views
SUM of multiple values/variables in Google Sheets using "IFERROR"
I have a Google Sheets (titled: ALLORDERS) that lists orders from my online store. These are listed as one single product sale per line. The output looks like:
Invoice Number | Invoice Date | Buyer ...
1
vote
1
answer
604
views
Multiple IMPORTHTML requests in a custom array, or in separate cells - same speed?
I have a spreadsheet with 62 IMPORTHTML requests. Does it make any difference in speed if I put all of these inside a custom array rather than having them in individual cells?
- 193
7
votes
2
answers
12k
views
Query Multiple Ranges with Results in Row
I am trying to query five ranges with the results displayed across a single row. To do this, I was using { } with commas separating each range, but it's only giving me the first range in return.
...
- 307
4
votes
2
answers
19k
views
COUNTIFS with multiple OR
I have two sheets, one is a dump sheet of values like below:
and the second sheet is using values from this to the total.
I am trying to get the total count for the corresponding zone and test name ...
- 41
1
vote
1
answer
2k
views
Append the cell content as a URL parameter and create a link
Here's a sample form:
https://docs.google.com/forms/d/e/1FAIpQLSfiPSHacKKNEfe-Jq82DBScbCBRkwtm8rq2Jq7y-ceLgGIC3Q/viewform
And here's the linked spreadsheet:
https://docs.google.com/spreadsheets/d/...
- 118
1
vote
0
answers
80
views
Function always parses column in first row but would like it to repeat
I am trying to make a function that repeats in each row of a column that fetches the URL in the first column, does an xpath query and gets an image.
Currently its all working other than the fact it ...
- 111
0
votes
2
answers
351
views
How to calculate the average of values in Col. B, if Col. A contains a unique timevalue of Col. C?
This is how my table looks:
(in column A) (in Column B) (in Column C should be) (in Column D should be)
2016-01-01 08:00:00 80 08:00:00 240
2016-01-...
- 131
3
votes
3
answers
28k
views
Google Sheets QUERY won't display cell text if other cells have numbers
I'm not sure what is happening in my Google Sheet query and could really use some help. I have a quick screencast that demonstrates and explains the issue.
Basically, I'm using a query to pull data ...
- 2,757
4
votes
2
answers
12k
views
Summing top three values in a column from a Google sheet
I have a Google Sheet with a variable number of data rows. I would like to sum the three highest values from a certain column, but they won't necessarily be in the top three cells. An empty cell ...
- 1,222
0
votes
1
answer
2k
views
ARRAYFORMULA() won't iterate over range
I'm trying to get all rows of data into a sheet where column headers match. Please have a look at this sample sheet. I need to achieve this dynamically so I'm using ARRAYFORMULA().
Source data
+----...
- 161
6
votes
2
answers
2k
views
Fill zeros for the dates when no data was recorded
I have a table with dates (in increasing order) and corresponding values. Some dates are missing, like 3/27 below. I'd like to insert them in Date column, and have 0 entered in the Value column for ...
user79865
1
vote
1
answer
3k
views
How to block automatic row and column insertion in Google Sheets?
While playing around with array formulas, "sometimes" when the result has more rows and columns than the previously available in the sheet, Google Sheets automatically adds the row needed. I said "...
- 41.7k
12
votes
4
answers
4k
views
Average of last 7 non-empty non-blank cells in Google Sheets
I have numbers in range D7:D and there are times when some cells are empty. I want the average of the last 7 numbers but to skip blanks. So if in the last 7 there's only 3 that are full, I want it to ...
12
votes
3
answers
28k
views
Can you do a UNION SELECT in a Google Sheet?
I've got a Google Sheet linked to a form which collects replies from three different people on the same subject using the same form and editing three times. This creates a sheet which contains three ...
- 167
1
vote
4
answers
11k
views
Numeric before query but query results are not a numbers
I have a column of values, let's say column A, that if I do =isnumber on any of the column data it returns TRUE.
Now after I =query and select A, the query gives me the right values, but the type ...
- 966
2
votes
1
answer
335
views
Formula breaks subtly in ARRAYFORMULA in Google Sheets
I have a spreadsheet document with a formula comparing a list of URLs to lists on two other sheets within the document.
I desire to know if each URL on the first list is present on the blocklist, ...
- 23
0
votes
2
answers
1k
views
Query reference to several sheets
I have this formula:
=ArrayFormula({query(query({A3:D,value(B3:B)},
"select Col1, sum(Col5), count(Col4), sum(Col3)
where Col1<>'' group by Col1 ...
- 1,723
1
vote
1
answer
4k
views
Using SUMPRODUCT with ARRAYFORMULA
I have the following random data in sheet one
A orange
D yellow
C yellow
B orange
B orange
C blue
A yellow
D blue
C orange
C blue
In sheet two I use the following query function:
A1=...
- 11
1
vote
1
answer
3k
views
ArrayFormula to compute running average for groups of rows
Is it possible to write an array formula that calculates the running average of the Amount column for each of the Name groups in the following sheet?
The rows are sorted by Name. In Column C, I have ...
- 13
2
votes
2
answers
2k
views
Appending text to each column in Google Sheets
I have a Google Sheets that has about 200 columns. Each one is a name, and to each one, I want to append -banner.jpg. For example:
abcde
fghij
klmno
will all be updated to:
abcde-banner.jpg
fghij-...
- 530
2
votes
2
answers
3k
views
How can I fill the diagonal of a matrix with zeros?
I have an empty matrix:
I would like to fill the diagonal of a matrix with zeros:
Is there any clever way to do it?
The matrix can be pretty large and it's tedious to do this manually. I could use ...
- 14.8k
135
votes
3
answers
122k
views
How to paste a comma separated string over multiple cells in Google Sheets?
Say I have a string of: l,f,x,a,s,f
I would want to paste this every comma-separated value over 6 adjacent cells. How would I do it?
- 1,467
3
votes
1
answer
2k
views
Unique Values From a Matrix
I have a spreadsheet that looks like this:
Column A | Column B
| An unimportant value
1 | Value 1, Value 2
2 |
3 | Value 3
4 | Value 3, Value 1
| Don't ...
9
votes
5
answers
44k
views
Count the number of words in a string in Google Sheets
I have a simple Google spreadsheet with a number of text strings. I just want the number of words contained in each cell. Is there an in-built Google spreadsheet function I may use?
Nishant
4
votes
5
answers
21k
views
How automatically to add spaces inbetween each character in a text string?
Teachers I work with, enter four scores for a student’s assessment in a single cell, like this: 2113, with no breaks or spaces.
How can I separate those characters so as to be able to create a chart ...
- 41
1
vote
2
answers
3k
views
How can I set the increment value for Google Spreadsheets to auto-increment?
OP Edit: I think I want Sheets to behave in a way it's not intended.
I expected Sheets to recognize the incremented value in my formula the way I believe it recognizes the increment value in a ...
- 113
5
votes
2
answers
9k
views
Filter() - referencing source array in criteria
The filter() function looks through a source array, and filters it according to condition
=FILTER(A:A,ISNUMBER(A:A)).
My source array is a rather long formula, so I end up repeating the same formula ...
- 73
9
votes
6
answers
27k
views
Split content of one row into multiple rows?
I have a table where each row contains data like this:
Team name | First member | Second member | Third member | Team description
Would it be possible to transform/break this data so that each row ...
- 1,833
40
votes
5
answers
35k
views
Cumulative Sum without Script
For this question, I've created a Google Apps Script function, that calculates the cumulative sum of a range. Martin Hawksey on Google+ made a comment about a more efficient way of calculating the ...
34
votes
9
answers
219k
views
How to Fill a Column with Sequential Dates in Google Sheets
In Excel, you can right-click drag a date downwards to fill a column with the next dates in the sequence. For example, if you enter 9/1/2014, select that cell, then right-click drag down over the ...
- 501
2
votes
2
answers
40k
views
Google Spreadsheet formula, MINUS?
I have a spreadsheet with 3 columns:
| ITEM | COST | SUBTOTAL |
Subtotal, for example, is 1000 to start, like:
--------------------------
| ITEM | COST | SUBTOTAL |
--------------------------
| ...
- 289