Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
808
questions
1
vote
3
answers
55k
views
Google Sheets formula for “if contains” from range
I try to figure out how to get a value based on "if some word is in a range".
So formulas from (Google Sheets formula for "if contains") works:
=IF(ISTEXT(REGEXEXTRACT( A1, "sites")), 1,0)
...
0
votes
1
answer
2k
views
Google Sheets Arrayformula with exceptions?
I am using fantastic Arrayformula to populate a grid in a Google Sheet:
The following is being used in each column: =arrayformula(if(Z2:Z <> "","■",""))
Now there are some cases where the ...
- 529
1
vote
2
answers
14k
views
Google Sheets Arrayformula on both horizontal & vertical cells?
In a sheet, I make calculations horizontally, however I can't build the arrayformula to expand calculation vertically on each row.
See the following sheet:
https://docs.google.com/spreadsheets/d/...
- 529
1
vote
3
answers
1k
views
Remove duplicate items from array?
What would be the best way to remove duplicate items from cells in rows? For instance...
Category 1 | Item 1 | Item 2 | Item 2 | Item 3
Category 2 | Item 3 | Item 1 | Item 2 | Item 3
...
- 139
0
votes
2
answers
3k
views
Google Sheets - Fetch random value from array for each new line created with form
I have a Google Form that fills up a new line with values in Sheet1!, column A and column B.
I am trying to create an =ARRAYFORMULA function in column C, which will populate the C cell in each new ...
- 742
1
vote
1
answer
3k
views
Countif based on multiple criteria
For reference here's the sheet:
https://docs.google.com/spreadsheets/d/1CS-fYhqeKRmEiJC5XA6kRIwIjyBDlBcXj-K9FHjCZjA/edit?usp=sharing
I am trying to create a filter that shows me all the data for May ...
- 11
2
votes
2
answers
286
views
Is it possibile to concatenate with bidimensional arrayformula?
I have a table like this:
+----------+----------+----------+----------+
| Photo1-1 | Photo1-2 | Photo1-3 | |
+----------+----------+----------+----------+
| Photo2-1 | Photo2-2 | | ...
- 53
1
vote
1
answer
28
views
How to show which column has the first amount and count columns to reach a sum amount
I am working on a Google sheet that shows a sum that has been advanced to a customer and then shows the income per month the customer receives. We will retain 100% of the income until the amount that ...
- 13
0
votes
0
answers
54
views
Multiple IF/And as array in Google Sheets [duplicate]
I am attempting to create an array that will compare the contents of two cells and return a resultant text to a third cell. I am able to make it work as a formula in a single cell, but when I attempt ...
- 9
2
votes
1
answer
3k
views
Aggregate results by week and month for charts
Is there a way to convert daily values into weekly averages for charting?
Sample source sheet
---------------------------------------------------
| Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc......
- 43
2
votes
1
answer
1k
views
Refer to the cell directly to the left of the cell with the maximal value in a range
I've got a spreadsheet with about 5,000 rows and roughly 50 columns. The columns are alternating between terms and numeric values associated with the terms (example: baseball / 1000 / football / 500 / ...
- 165
2
votes
1
answer
1k
views
Range notation and current row
I wonder how does work the following and is it documented somewhere?
I have the sheet:
A B
-----------------
1
2
3 =$A:$A
4
5 =$A:$A
For column B (row 3 and 5) I see ...
- 121
0
votes
1
answer
440
views
Vlookup across multiple columns
This question is related to the same project in this question, but I'm now at a later step.
I have a Google Sheet with a variety of German translations of a large set of English words. Using a ...
- 165
10
votes
1
answer
20k
views
Google Sheets: Is there kind of map function?
I don't like expanding formulas. First, when I modify them, I have to copy them again, it is like copy&paste programming. Second, the I'd like them to match the original range, which might change. ...
- 229
1
vote
1
answer
231
views
Consecutive numbering skiping empty cells with ArrayFormula
I am trying to auto-number only the existing names on a cell using an arrayformula:
I have two cells one for the names and the other for consecutive numbers.
Here I have an example of what I want to ...
- 135
2
votes
1
answer
3k
views
Does the ArrayFormula formula work with Concatenate?
I am trying to make an ArrayFormula that see if column D is empty or not
If it is not empty it will then take a number and concatenate to generate an url to show an img from a URL.
My current ...
- 135
5
votes
2
answers
1k
views
CountIf in upper cells with ArrayFormula
I have some values in the column A and in the column B want to calculate concatenation of the value and how many times it was used in rows above.
In the usual formula, I would've written in B2
=A2 &...
- 153
3
votes
2
answers
328
views
Google Sheets Array Generation
Is it possible to generate an array of numbers between two provided numbers
eg =sequence(1,10) results in {1,2,3,4,5,6,7,8,9,10}
My end goals is to generate a list of days between two dates, but have ...
- 433
11
votes
2
answers
12k
views
How to use AVERAGE inside ARRAYFORMULA in Google Sheets
I want to use the ARRAYFORMULA version of
=AVERAGE(Linda!B3, Nandi!B3, David!B3, Remmelt!B3)
I want the cell to calculate the average value of the entries in that cell from the other tabs (Linda, ...
- 111
2
votes
2
answers
2k
views
How to sum all positive values in Google Sheets based on whether the associated data in another column is unique
I have a spreadsheet that my company is trying to transfer into Google Sheets from Excel. I've used an array formula to accomplish this result in Excel, but it's not translating to Sheets and I know ...
- 39
0
votes
1
answer
2k
views
Using Conditional Formatting to highlight data in new table that matches data in separate table
Sheets 1 thru 6 all have an Array Formula pulling data from other sheets. The Array Formula runs multiple Query formulas that skip over rows with missing cell data and then goes back to those rows and ...
- 135
7
votes
2
answers
33k
views
Stacking multiple query formulas throws "an Array Literal was missing values for one or more rows"
I am attempting to run multiple queries where it adds rows where if F is blank, then it skips those rows, then runs the query again and replaces row F with row G.
I have a much more complicated ...
- 135
4
votes
1
answer
12k
views
QUERY where column IS NOT EQUAL to ANY cell in column A of a second sheet
I want it to pull the max value from column F where the corresponding row has C matching the number 14, and A does not have any exact text matches in any cell in column A of a separate sheet.
I feel ...
- 135
7
votes
3
answers
3k
views
How to SUM a row of numbers after extracting numbers with REGEX?
I have columns of data that are of the form:
A1: "IDjohn / 35.00 / california"
A2: "IDmike / 25.00 / oregon"
A3: "IDrebecca / 40.00 / ohio"
B1: "IDchang / 20.00 / washington"
B2: "IDwill / 25.00 / ...
- 81
2
votes
1
answer
176
views
Arrayformula using cells as parameters
I would like to use the ARRAYFORMULA combined with two cells as range. For example:
Cell A1 value: A3
Cell A2 value: B7
Resulting formula: ARRAYFORMULA(A3:B7)
Is it possible? How can I do?
- 21
4
votes
5
answers
10k
views
Auto-fill date function changes the YEAR and not the day
I'm trying to get Google Sheets to auto-fill dates in the beneath cells after I have manually filled one myself. The strange issue is that when I hold Command key and drag down, the cells auto-...
2
votes
1
answer
548
views
Array formula with running total that restarts based on values in another column
I have tried to create a running total with some kind of filter as it were and have been unable to figure out how to do it. I have seen people do a running total using MMULT but not sure how to adapt ...
0
votes
1
answer
603
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
649
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?
- 303
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
21k
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
3k
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/...
- 30
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
372
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
31k
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,807
4
votes
2
answers
13k
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
3k
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 "...
- 43.1k
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 ...
13
votes
3
answers
34k
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 ...
- 177
1
vote
4
answers
13k
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 ...
- 986
2
votes
1
answer
350
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,743
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 ...
142
votes
3
answers
132k
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,537
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 ...
