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

551 questions
Filter by
Sorted by
Tagged with
77 views

### I am trying to obtain formula to increment cell by 11 rows for Google Sheets

Here is what I am doing: =IF('unique stock'!C11<=0,'unique stock'!C11,"") and this goes on... =IF('unique stock'!C22<=0,'unique stock'!C22,"") =IF('unique stock'!C33<=0,'unique stock'!C33,"...
683 views

### ArrayFormula syntax to reference the previous row for calculating the current row

Sample sheet here (in sheet3): https://docs.google.com/spreadsheets/d/1hXZzwRnYpso_Q4PHd6M818oE347yRBAh9AqTLhqX8TM/edit#gid=318977824 In part 3 of Arrayformula Alternatives series of question, I ...
94 views

### ArrayFormula for conditional consequential sum-up?

I have a table which looks like this: And formula in cell B3: =IF(A3>5, B2, B2+1) and on every next row a corresponding variation of this formula. I would like to know how to create ArrayFormula ...
79 views

### Picking the Total from the Next Week

I have the next year mapped out (one line for each day) and have the week subtotal at the end of each week (which is a Friday). I am wanting the Next weeks subtotal to show in another cell for that ...
55 views

### Check value even if it doesn't match validation

In my Google spreadsheet, I have a column G of dates that interviews are/have been scheduled on. There is a validation set on it to validate for dates with a warning. Every once in a while, when ...
49 views

### How do I add dates for the beginning and end of the week in one cell

I know very little about Google Sheets and all I want to do is create a cell that will display the beginning and end of the week, like this: 7/22-7/28. I'd like to be able to drag this across ...
52 views

### Create a row based on the sum of every column in a sheet

I have a spreadsheet with a data set containing hours:minutes:seconds. This dataset is a record of logged times per user (rows). The number of rows or columns isn't fixed. The number of columns is ...
45 views

### Confirm that values in a column are alternating

I have a column, call it A, of around 1500 cells. Each cell contains either "a" or "b". For the most part, the As and Bs are alternating, but sometimes there are repeating values. An example of A1:...
24 views

### QUERY for like-minded rows

Scenario Consider the following query: =QUERY(A15:E861, "select B, sum(D) group by B") This works for rows with col B's that have the SAME description eg. 1 | abc | n | 10 2 | abc | n | 10 3 | cba ...
149 views

### SUMIF by month different entry locations

Beforehand please check my sample sheet. I'm trying to use SUMIF or any specific formula that shows the total amount of income of each month over on the side (March, April, May...) As noticed the ...
683 views

Is it possible to add a column of numbers to the results of a query? It would be nice to have a number (from 1 to n) next to each record returned from a query.
30 views

### Blank Redundant Month

I have a large list of days and quantities that I wanted to chart so we could see how many of something we sold on a specific day. Give this goes back 600 days, there is a lot of data so I have been ...
30 views

### Perform operation on specific column in filtered range, right after filtering

I have this formula in E2: =FILTER(A1:C20,B1:B20=2) and need to perform substitute like: spreadsheet here I'm hoping to do the formula in a single cell, without additional columns.
178 views

### Formula for filtering all duplicate data pairs in Google Sheets

I'm making a table to check duplicate data. Where data that wants to be duplicated is in the "Sampel1" column and the duplicate checking results are in the "Status" column. The formula that I have ...
40 views

### Summing up amounts if one row contains a key within another sheet

I have two sheets in one Google Sheets document: | Key | Amount | |-----|---------| | ABC | 1 | | DEF | 2 | | GHI | 3 | | Keys | |-------| | ABC | | XYZ | | JKL | | GHI | ...
2k views

### Get URL from web page using IMPORTXML

Though I am new to web scraping, but I have a hand on experience of web scraping using VBA (Excel) I am facing the following problem: I have tried all methods (ImportXML, ImportHTMP, ImportData, etc.)...
379 views

### SUM columns from other sheets and group by month, but also show the individual sheets values before doing SUM

Currently, I have a spreadsheet which has a number of customer sheets, each with a pivot table showing the month in the row I and the total income for each month in row L. On a separate sheet I have ...
174 views

### ArrayFormula for consequential sum-up?

Here's a sample test spreadsheet - https://docs.google.com/spreadsheets/d/ What do I have to do in the ONE cell in column D such that I won't have to copy and paste the same formula in column C ...
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 ...
311 views

### How to round float to bigger integer?

In Google Sheet, we can use ROUND() to round float to the nearest. like 5.2 = 5 5.5 = 6. What if I want it: 5.2 = 6 5.5 = 6. If the number exceeds by a decimal, then rounded off to +1. Any idea?
409 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?
147 views

### Can results of ARRAYFORMULA be used as values in an annotated timeline?

I am trying to create an annotated timeline with the following data, which exists on another sheet: I am using an ARRAYFORMULA to retrieve the text. I want to display these values as an annotated ...
2k views

### How do I tie an enum to a set of numeric values?

I created a list of items Data Validation in a sheet corresponding to time-frequency: weekly,monthly,annually I want to multiply values in a numeric column with the corresponding weekly values to ...
34 views

### Find cell that contains some phrase

I'm looking for replacement of Excel formula: =if.error(lookup(1000;search(\$B\$1:\$B\$2;A1);\$B\$1:\$B\$2); "NONE") The sheet looks like this: So in column A, I have a long product index, in column B, I ...
687 views

### How to use an ArrayFormula containing a partial range

I have a Google Sheet that is populated by a linked Google Form. I've added some extra columns to calculate extra values for each row that gets added. I want these columns to be automatically ...
129 views

### Splitting numbers of a cell and adding them together in an ArrayFormula on Google Sheets

My question is how can I have digits of a cell added together in an array formula format, in that 12 | -> 3 //(result: 3 = 1 + 2) 14 | -> 5 46 | -> 10 11 | -> 2 53 | -> 8 And let’...
437 views

### How to autofill value on Google Sheets

I have the following example here. I have a random value that is no spaced exactly, (some have 10 empty between them other 30). I would like to have some value equally added in the empty spaces. I ...
545 views

### Remove the false operator in Google Sheets

Here I am trying to add a random hexadecimal value every time a name is entered. It’s working but the other empty cells get filled with false. I want to completely delete the false part as it’s ...
335 views

### Set formula into a cell based on content of another cell?

Google Sheets: I would like to set a formula into a cell, but this should depend on content of another cell. Example Google Sheet (anybody can edit): https://docs.google.com/spreadsheets/d/...
1k views

### Inserting a row in Google Sheets does not copy the formula present in other rows

I am using Google sheets withe the following formula in one of the columns =ARRAYFORMULA(IF(ISBLANK(M2), "", ROUND(M2/G2, 2))) Whenever I add a new row anywhere in the sheet, the formula does not ...
35 views

### Sum a quantity list in an array

I have an array table using ARRAYFORMULA({Fruit Names, Quantity}) that looks like | Apples | 2 | | Oranges | 3 | | Bananas | 5 | | Oranges | -2 | | Bananas | -1 | | Apples | 1 | I'd like to ...
998 views

### SUM a range of cells (sometimes string, sometimes numbers), parsing the cell if it is a string

I am trying to get the sum of a column of cells, which sometimes contains numbers and sometimes contains a string with numbers in it. I've found that the formula =arrayformula(sum(0+iferror(...
36 views

### How to create a user defined function that maps one value to another in Google Sheets

I would like to create a mapping function that takes as an input 3 arrays of the same length and a double between 0 and 1. Array #1: Each element is the minimum value of the nth bin. Array #2: Each ...
1k views

### ARRAYFORMULA for a column, but only for blank cells

I'm trying to fill a formula down a column using ARRAYFORMULA. However, there are certain cells in this column which I would like to overwrite, but all the other cells should use the ARRAYFORMULA. Is ...
11k views

2k 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 ...
10k 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. ...
25 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 ...
37 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 ...
284 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, ...
562 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 / ...
413 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 ...
76 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 ...
168 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 ...
1k 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 ...
6k 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, ...
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 ...
9k 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 ...