Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

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

731 questions
Filter by
Sorted by
Tagged with
1 vote
40 views

### Array formula to extract N non blank cell in Row 1-16

I have a google form where team coaches input which players played on a given day. They also input if they were Man of the Match, Yellow/Red Cards Received, and how many Goals they scored. When the ...
• 11
89 views

### How do I automatically match text responses to numbers and sum those numbers in Google Sheets/Forms?

I am using a google form to collect responses from students. For each student I would like to match those text responses to specific numbers. Never = 0, Almost Never = 1, etc. Then sum the results for ...
79 views

### In Google Sheets, analyze cell content, and if certain values are present, return a response in the next column

In the below example, I want to analyze each cell in column A, and if the cell's contents match certain parameters, return a value in column B accordingly. So if the value in a col A cell = apple, ...
45 views

### How to assign country based off list of either provinces or states?

I have a list of names and locations. Most of the locations include the name of the province or state. Some only include country names, some include state abbreviations. I also have a list of ...
68 views

### Google Sheet - Count Random Average

Number Of Invoice | From | To | Total 10 | \$25 | \$100 | ? I want the Total column to fill with the average number of 10 invoice with each invoice random between the ...
• 11
1 vote
67 views

### G sheet How to jump over no value cells with SUMIF function

I would to get the sum of the duration of the column B +---+----------+ | A | 14:45:00 | +---+----------+ | A | #Value! | +---+----------+ | A | 13:34:50 | +---+----------+ | B | 23:41:00 | +---+-----...
• 141
578 views

### Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col7

I need to join two worksheets from different files in another one that already exist in Google Sheets I tried to do this: =QUERY({IMPORTRANGE("1GoKFiJjBKcGQGGi6JMvk9xD5TIypsNSmpLCTeadWQnU",&...
1 vote
1k views

### Restructure (flatten) data of Google Sheets table in a new table

Column A of my Google Spreadsheet contains dates, columns B to G contain numeric values, line 1 contains the header names. Due to automatic updates the line count varies, but the column count is ...
• 13
1 vote
726 views

### Extracting text from a capture group while using an arrayformula to consider multiple criteria

I have a page which contains a list of regular expressions in the format "some text (thing I want) but only if (thing is matched here)" that are fairly varied. There are 29 of them so far, ...
• 111
351 views

### Calculate Total DateDif Average Between 2 Columns (start, end) according to year expressed in days, hours, minutes

I'm having trouble getting a formula to work to calculate the total average time between times (start and end). Sheet: https://docs.google.com/spreadsheets/d/...
• 23
43 views

### Average of cell range from string

In Google Sheets I have a string like this: "G4;G15;G26;G37;G48". How do I get the average of the values in those cells? I'm sure there's some arrayformula and indirect to be used, but I can'...
475 views

### Average the last N values in a Sheets column using Arrayformula

Experts, I need your help... I'm looking to create a formula (not necessarily an arrayformula), that will automatically calculate an average. This sounds easy, but surprisingly, it isn't. Let me ...
• 21
25 views

### Show image if rank equals number

I have a row of 5 cells. I want to show an image above each cell depending on the rank of the cell. So if the rank of the cell equals 1, show a specific image (from a link), if the rank is 2, show a ...
35 views

### Create Columns via Formula for two dates

How do you do this with columns instead of rows? =ARRAYFORMULA(TEXT(ROW(INDIRECT("A"&DATEVALUE("2018-4-13")& ":A"&DATEVALUE(&...
• 1
1 vote
175 views

### How do I calculate subtotals by using ArrayFormula and without formula in the cell itself in a Google Sheet

I'm working on an estimate sheet, got it all worked out pretty neatly and trying to tackle the formulae, make them work from the header rows using ArrayFormula instead of having each cell holding ...
• 111
30 views

### How can I include parameters in Google Sheet?

I have a query string to extract data from my master sheet, which looks like this: =QUERY(IMPORTRANGE("1ABCsxIDRXU-k6YXWJ47Awjk4757VsMFBmye7rkBC123","Master!\$A\$1:\$H"),"SELECT * **WHERE Col2 = 'abc123'...
1 vote
85 views

### Listing and formatting tagged data

I'm creating a list of texts with some tags associated to them in Google Sheets, each text has a "tags" column where the tags are separated by commas. I want to have a second sheet where the ...
• 21
358 views

### ARRAYFORMULA and FILTER

The following sheet is an example of what I'm trying to do : https://docs.google.com/spreadsheets/d/1B9a8OUvwciCAYURLZq-Jd2KeSUZsYlalAFNsANzRzNw/edit?usp=sharing In the first sheet ("Matrix"), I have ...
• 111
1 vote
308 views

### Convert string to real date time value?

Here is my current input: 14.05.20 12.37.01 And yes, as it shows, there is a line break in the cell... Here is example sheet: https://docs.google.com/spreadsheets/d/...
• 608
1 vote
445 views

### Find the last instance of multiple values

I really need some help with this. I need to search for the value of A2: B2 in another tab in google sheets and it needs to return the row number of last occurrence/entry of the values in A2: B2 in ...
• 35
50 views

### How to replace or substitute one column of data with their corresponding value from a list

I'm trying to replace a column of data (Prodct SKU) on one sheet (a) with the corresponding value taken from 2 columns on sheet (b) (Product SKU & Product ID). I've attached the spreadsheet to ...
39 views

### How to autofill specific cell when any next cell is filled

+------------+---------+---------+ | Date | Col1 | Col2 | +------------+---------+---------+ | 2018-01-01 | Value 1 | Value 1 | | 2018-01-01 | Value 2 | Value 2 | | 2018-01-01 | Value 3 | ...
• 143
1 vote
160 views

### Trying to pull Duplicate data from multiple columns

https://docs.google.com/spreadsheets/d/1VSiKZbD_PoQTfbav7spalkyBgrt4T8juaLlKOM7LgLM/edit#gid=0 I have two columns, A and B. Throughout this project, the same name will appear in each column. I want ...
64 views

### How do I get a formula copied to all cells below it in the entire column and increment by x instead of manually dragging it down to eternity?

If in Cell A2 I have the formula, =IF(MOD(ROW(),1)=0,A2+1,A2) how do I get it copied to all the cells below in the entire column (e.g. 900'000 cells) instead of manually dragging it down to ...
• 57
1 vote
71 views

### Formula to find the cell in column C where a value in column A = a value in column E

I'm trying to create a formula in a google sheet to find the value in the C column where the value in the A column is equal to the value in the E column. E.g. Column E = 1 in Row 9-16 and Column A = ...
• 57
167 views

### Array formula to show a leading zero

I have a Google Sheet where an array formula in Column I creates a video name based on the content entered into columns A+B+C+D. Column C is the number of the video. For some videos, I need a leading ...
• 59
678 views

### How to SUM LARGE 5 largest elements out of 8 numbers in a row

In Excel I am able to get the output using this formula: =SUM(LARGE(C5:L5,{1,2,3,4,5})) But in Google Sheets I am unable to get the right answer. Can anyone please help? In the image below, ...
1 vote
156 views

### Array formula with concantenate not working

I have a Google Sheet where I am wanting to use an array formula so it auto copies down the column rather than the current formula which needs to be copied down manually. =(IF(\$E2="Yes", \$D2, ...
• 59
500 views

### Exponential Moving Average

What is the formula of Exponential Moving Average (EMA) in Google sheet ? How it can be used to calculate Share market stock EMA of different period. Eg 5, 21, 50 Days. Can anyone share Demo sheet.
276 views

### Conditional Find and Replace in Google Sheets

Example data: A B ------ 1 HELLO 2 HELLO 2 HELLO 3 HELLO I want to replace \$ with , WORLD! but only if A is equal to 2. Such that the final result would be: A B ------ 1 HELLO ...
• 111
57 views

### Add fields and update array and scripts

I want to add some questions to my Google form but my response sheet has a script and an array, which do not like that. Here is what it should look like: The URL is the link for the form. Here is ...
1 vote
75 views

### How to populate a cell based on specific name and specific date using a query function?

Hello new to the forum here and relatively new to all this data business. I feel pretty confident in my skills but this one has me stumped. Below you will see my initial query of dates, ditch_name(...
38 views

I hope you are all well and healthy! I am looking for a solution in Google Sheets to automate something like this: and it could auto generate something like this Thank you in advance
1 vote
27 views

### Computing the Stock Value based on FIFO (First in Fist Out)

I have as sheet in which I'm storing inventory purchase date, quantity and rate. In a separate sheet I'm tracking the export of the inventory. Hence, I'm able to compute the total purchases made till ...
• 11
57 views

### Vlookup with values from another tab

Link to my sheet: https://docs.google.com/spreadsheets/d/1E3gH661qnZDUvP48Ubg6epuHwpDH70gANE8wW968dYg/edit?usp=sharing I'm having trouble understanding what I'm doing wrong. I'd like to have this ...
• 23
233 views

### Currency Conversion, Array Formula, Google Sheets

I was hoping to use array formula in conjunction with Google Finance to convert currencies on specific dates. I've been using a google form to enter basic expense information and the form adds a new ...
1 vote
1k views

• 13
1k views

### Formula to find the second to last (or nth to last) cell with value ignoring empty rows/cells

I have a spreadsheet that I used to run in Excel but due to other family members needing access to it I've moved it over to Sheets. Anyway, I have the following formula that was running in Excel that ...
7k views

### Google sheet, images disappear randomly

Using Google Sheets I'm trying to populate a list of countries with (among others) names, ISO-alfa2 code, and flags. I use this IMAGE() and ArrayFormula() to get the flags from URL in my private ...
1 vote
33 views

### How to clear a cell if there is no data in it

How to clear a cell if there is no data in it? =ArrayFormula(REPLACE(IMPORTRANGE("1I1MK51uYT_WJDh0fiiHxLjnYMoQOVoZi41XnHM", "SetstudentMatrik!C2:C"),1,6,"******"))
• 11
1 vote
28 views

### get value left of max value

I have a sheet that looks like: Date Value Highest Lowest ---------------------------------------------------------------- 1-1-2020 23 26 20 2-1-2020 20 3-...
57 views

### Issue trying to sum different columns

I am trying to sum 3 different columns and I'm not being successful. My function is: =ARRAYFORMULA(SUMIF(MONTH(D:D), 2, A:C)) Where D is a column with dates, and A,B and C columns with currency (A-...
• 21
149 views

### Create a pivot table with a column for each assignee, containing rows of task descriptions

I have a two-column table with the first column being "task description" and second column being "assignee". This is something I'm unable to influence. Crude representation: I'd like to be able to ...
16 views

### SumIf OR COunt if

I want to count dates monthwise with person name. This is my data: Mandeep 1/2/2020 Mandeep 1/2/2020 Mandeep 1/2/2020 Mandeep 1/2/2020 Mandeep 1/2/2020 Mandeep 1/2/2020 Mandeep 2/2/2020 Mandeep 2/2/...
126 views

### How can I SUMIF a row, but have the formula automatically applied to all new rows below it in Google Sheets?

I've made an inventory tracking spreadsheet that gets raw data from Google Forms. Forms inputs data into Sheets in a less than ideal way, but there's nothing I can do about that, so I'm stuck post-...
336 views

### Print incremental numbers in Google Sheets

Cell A1 holds a numerical value (e.g. 10), and I'd like to output the list of numbers from 1 to that number in B1: What's the formula I need for B1? It seems trivial, and I know it probably requires ...
• 133
731 views

### Array formula with index match to return array of matches

I've been working at this for a bit. I have tried many times different formula combos, using VLOOKUP to using INDEX & MATCH, even with an ARRAYFORMULA. What I'm attempting to do is match a ...
1 vote
56 views

• 11