Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [google-sheets-arrayformula]

Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.

0
votes
2answers
5k 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
1answer
280 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 ...
1
vote
1answer
969 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/...
0
votes
0answers
107 views

Arrayformula concatenate both vertical and horizontal

I'm stuck on trying to solve this google spreadsheet issue. I have an table with for example countries and then details. What I would like to do is create a table automatically that concatenates all ...
0
votes
1answer
223 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 ...
0
votes
1answer
404 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 ...
0
votes
0answers
29 views

Google sheets: convert ragged array for to 2D array

I have multiple columns in a single row in a ragged array and want to convert this to a 2d array. This example can be found here I realize that similar questions have been asked for multiple entries ...
1
vote
2answers
111 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 | | ...
1
vote
1answer
23 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 ...
1
vote
0answers
16 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 ...
2
votes
1answer
198 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......
2
votes
1answer
275 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 / ...
1
vote
1answer
179 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 ...
0
votes
1answer
364 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 ...
4
votes
1answer
3k 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. ...
1
vote
1answer
84 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 ...
2
votes
1answer
348 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 ...
0
votes
0answers
10 views

Get all states from a country in Google Analytics Addon using Google Spreadsheets

I need to get all states from Brazil in Google Spreadsheets and display them in a chart showing the unique visitors for each state. I know how to display data in the chart, I just don't know how to ...
0
votes
0answers
247 views

DATEDIF of latest date of a range (Google Spreadsheet)

I'm trying to get the latest date for a range of cells and Calculates the number of days between two dates using the function DATEDIF. As you can see from my screenshot example, my calculation should ...
0
votes
0answers
1k views

How can I make a multiple criteria index,match array formula work?

I've been using array formulas in my google Sheets a lot, of the type: ={"Heading";arrayformula(if(len(A2:A)>0,FORMULA HERE,""))} in order to "auto drag down" a formula when the A column is ...
4
votes
2answers
341 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 &...
3
votes
1answer
87 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 ...
6
votes
1answer
1k 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, ...
2
votes
2answers
318 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 ...
0
votes
1answer
1k 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 ...
5
votes
2answers
7k 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 ...
4
votes
1answer
4k 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 ...
5
votes
1answer
402 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 / ...
2
votes
1answer
38 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?
2
votes
1answer
209 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
1answer
274 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 ...
2
votes
1answer
104 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?
1
vote
0answers
62 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 ...
0
votes
2answers
161 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-...
2
votes
1answer
186 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, ...
0
votes
2answers
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
vote
1answer
1k 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 ...