Skip to main content

Questions tagged [google-sheets-arrayformula]

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

Filter by
Sorted by
Tagged with
2 votes
2 answers
680 views

Running average of the last [n] values in a column

I'm looking to create a formula in Google Sheets that will automatically calculate the running average for values in G2:G, based on the most recent n rows. I want to calculate this across three ...
0 votes
1 answer
19 views

Sum multiple currencies by looking up conversion rates based on multiple cell values

I'm trying to sum values that are in different currencies. The currency for each value is specified in the previous column and the final result should be in Australian dollars AUD. I use a fixed ...
0 votes
2 answers
80 views

How to create Google Sheet app that retrieves a mix of random words

I have a Google Sheet with a lot of columns, each of them containing words from a category. I would love to create a formula that can retrieve random words and put them together. For example: Colum 1 [...
3 votes
3 answers
666 views

How to remove duplicate words in a cell only if longer than a certain number of characters?

I need to remove a duplicate text in a cell but only if it is longer than 4 characters. I have this formula that only works for any types of duplicate words: =join(" ",unique(transpose(split(A1,", ")...
0 votes
2 answers
2k views

Import Range of cells as per today date in another sheet

There are three columns A, B, C AND today date is in the first row of the column A means A1. I need to import these three columns date excluding date row. A2:A100, B2:B100, C2:C100 in another sheet. ...
2 votes
1 answer
213 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 ...
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 ...
2 votes
2 answers
289 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 | | ...
0 votes
1 answer
32 views

Moving average up until every listed week

I'm trying to automatically calculate a new average every week, so I can see the evolution of the average over the past weeks. I have one column with the week numbers. The second column contains an ...
0 votes
2 answers
27 views

How for each cell in a row return and concatenate multiple matches without dragging down formulas?

I have a list of items and in another place I have list that covers some of these items each time with a different key. How to fill in column B all matching results next to the original item (column A)...
0 votes
1 answer
36 views

How to use Array with Sumproduct or other formula

I am trying to use an array formula like SUMPRODUCT to perform a lookup with 2 criteria. I have a sheet named data that stores customer-specific pricing for products. A B C             D 1 ...
1 vote
1 answer
40 views

How to use ARRAYFORMULA to remove values that contain brackets?

I am trying to write a formula that pulls all data from column A that doesn't contain brackets. Source Data Desired Result (no values containing brackets)
3 votes
1 answer
356 views

Count number of rows with a specific MONTH in the date of a specific cell and a Boolean condition in another cell

Let's say I have a number of dates and whether they occupy full or half a day: DATE | WHAT | HALF? ... 2021-4-1 | Something Something | false 2021-4-2 | Something Something | false ...
2 votes
1 answer
67 views

How to concatenate all further columns from duplicated items of a first column?

In Google Sheets I have two or more columns of data: Column 1 is a list of items that may include duplicates. Column 2 (and any additional columns), contains keys each associated with the item from ...
1 vote
2 answers
51 views

Is there a way to remove entries based on condition in =filter?

I probably did not phrase my question accurately. Is there a way to remove the result 'a' because it fulfils the '<>1' condition? I am pretty sure a mixture of query and arrayformula is needed ...
1 vote
1 answer
342 views

Generate independent number sequences tied to specific item categories

I have two columns in my Sheet, one labeled serial number and the other category. The serial number for each item is based on its category as well as its order in the table, and is numbered using a ...
1 vote
1 answer
31 views

I can not make a query work, I need another eyes to look it

I have 3 columns and what I need to do is a query where I can see Lab name, Sum Job count only N, Sum redos only Y. I tried with array, but not let me get the conditional Col2=N, Please help me. =...
1 vote
2 answers
32 views

I'm looking for a way to return multiple values in an array formula on Google Sheets - haven't been able to find a solution

Scenario: I have a roster of dog owners who have had dogs involved in various training classes. I have a list of the dogs and their respective classes, and I have a list of the owners. I want to ...
1 vote
1 answer
258 views

Use IFS in a SPARKLINE CHART

I'm at beginner level and trying to modify a template using a SPARKLINE chart. Now I would like the chart color to change depending on the cell value. I have done some research and thought IFS(CELL>...
3 votes
1 answer
60 views

LOOKUP for data from other sheet

So I have a drop-down menu in order sheet and I have an info sheet. In the info sheet, I have 2 columns, column A has the customer name, column B has the customer e-mail. So what I'd like to do is, ...
2 votes
1 answer
301 views

How to join VLOOKUP results of multiple sheets into one cell

I am trying to use TEXTJOIN across multiple sheets, IF a cell is is the same I would like it to return the value from another cell in all sheets that are associated with it. In this example, I am ...
2 votes
2 answers
77 views

Add columns based on rows

I'm currently working on setting up a sheet, but I want to add columns in one worksheet based on rows in another worksheet. I have no idea if this is even possible, I've tried googling it. Might be ...
1 vote
1 answer
124 views

Can I implement this moving average with missing periods using ARRAYFORMULA?

I have these columns: A person_id B date C cupcakes_eaten D date-35 (35 days prior) There can be multiple rows with the same person_id and same date. There can be days with zero rows for a ...
0 votes
1 answer
2k views

Increment number in array formula based on a condition

I have a data set with Sub-Total rows at various intervals. As the data can have rows inserted or deleted, I am trying to use an array formula to give each group of data an incremented integer as a ...
1 vote
1 answer
14 views

Sorting a range of data based on a column within that data

I have a set of data spanning columns A through P in Google Sheets. I would like to add a column in Q that will return the values of A based on the top values of column P. I assume this can be with ...
0 votes
1 answer
783 views

ARRAYFORMULA - Return of Text if cell is not blank and DATE earlier than TODAY

After some unsuccessfull workings hours, I definitely need your support. I am trying to create an ARRAYFORMULA, so that new rows can be added manually with the wished formulas already implemented in ...
-2 votes
1 answer
147 views

Solving ERROR: Array result was not expanded because it would overwrite data in B6 when using IMPORTRANGE

I am trying to make a Google Sheets book to keep track of my schedule. When importing data across sheets, I used IMPORTRANGE(url, range) to import data from Sheet1 to Sheet3. This worked at first; ...
0 votes
1 answer
22 views

Return Average of a column if

Looking to return average if criteria is met, but I can't get the formula to work when one of my criteria exists in multiple columns. Does anyone have an efficient solution? =AVERAGEIFS(Sheet2!D:D,...
0 votes
0 answers
10 views

MORE DETAILED explanation of ERROR: Array result was not expanded because it would overwrite data in B6 [duplicate]

Here are screenshots from my spreadsheet that has the issues I discussed in the previous question: IMPORTRANGE(,) works in a single cell, however, when adding the same formula to the next cell in the ...
1 vote
1 answer
71 views

How to auto-calculate without a need to drag down the formula?

How can I fix formula to a row so that when data is inputted in any cell within that column, it auto-computed in Google Sheets? the % doesn't work Without having to drag the formula down. Example: ...
1 vote
2 answers
1k views

How to remove duplicate rows based on a column (leaving first one out) in Google Spreadsheets

I am trying to remove duplicates from my Google spreadsheet file. Here is an initial table example A B C D HelloWorld1 2 6 41 HelloWorld1 4 3 5 HelloWorld2 6 46 6 ...
0 votes
1 answer
104 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 ...
0 votes
2 answers
268 views

How to filter after parsing CSV values contained in Google Sheets cells

I would like to be able to match all cells in column A (titles) that have one of the comma-separated values in column E (Key Search Terms). This will involve at least two steps. 1) Parse each of the ...
1 vote
1 answer
1k views

Pull unique values from other sheets as new sheets keep getting added

I'm trying to get a unique list of values in a master sheet based on a list of values that appear in column A of other sheets. The tricky part is that every time a new column is added in the master ...
1 vote
1 answer
24 views

Formula to return a value when the adjacent column's text contains any value from an array of text strings

In Google Sheets I have an array named "Tech_issues" with the following values: Array name value 1 value 2 value 3 value 4 Tech_issues crash froze glitch error I'm looking to create a ...
0 votes
2 answers
397 views

Having trouble outputting GOOGLEFINANCE data

My wish is to create a table in Google sheets looking something like this: price day A B C D ... Z 1997-01-02 132.54 . . . . 1997-01-...
1 vote
1 answer
25 views

How to add arrayformula function to this formula with IF function?

I made a table that has two columns (namd & State). The 4th column(check) makes conditional formatting possible for any of these combinations: (if name is either "Ali" or "John"...
0 votes
2 answers
3k views

How do I "bin" results in Google Sheets?

I am trying to bin my results so my groupings have lower granularity - going from a 1-10 scale to a 1-5 scale. For example, any result 1 or 2 would be renamed to 1 in the next column, any result 3 or ...
1 vote
1 answer
137 views

How to use arrayformula to find and get data from only a certain, dynamic set of columns?

I have a table where my headers are dynamically generated from another source and can show up with any header values, in any order. In the example below I've got the header values: AA - BB - CC - ...
3 votes
2 answers
6k views

How to pull last value in a Google Sheets column and populate it across another worksheet column?

This is my first post here- I hope this is the right place for it, apologies in advance if it isn't. Context: I have a password protected presentation that I need to give users access to, but only ...
1 vote
1 answer
95 views

"Multiply" (Concatenate) Text Columns in Google Sheets

Simple question that I can't seem to find a solution for. I have text data in two columns like Name Num a 1 b 2 and I would like to produce a single formula if possible to output a 3rd column ...
1 vote
2 answers
171 views

Finding all word pairs from range of strings in Google Sheets

Let's say I have a column of values, each of which is a string. A This is a string This is longer than that My favorite is short But sometimes short is longer How is a short longer I would like ...
2 votes
2 answers
201 views

Spreadsheet formula to calculate most common items in ordered items lists

I am searching for a correct google spreadsheet formula to go through the list of ordered items and summarise the resulting winning items. Like is you have an Oscar ceremony and you get votes from ...
1 vote
2 answers
168 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, ...
0 votes
1 answer
29 views

Multiple substitutions in a table

Suppose we have a table like the following : A B C D E F G 1 17-01 DO 22-06 DO 17-01 22-06 22-06 2 22-06 22-06 17-01 DO 22-06 09-17 09-17 3 09-17 DO 22-06 22-06 DO 17-01 17-01 4 17-01 22-06 17-01 ...
0 votes
1 answer
644 views

How to solve Error message in Google Sheet array formula: The resulting array was too large

I tried to use this formula below on my Google Sheet for around 10,000 row data: =ARRAYFORMULA(IF(LEN(L3:L),M3:M&" - "&MMULT((M3:M=TRANSPOSE(M3:M))*(ROW(M3:M)>=TRANSPOSE(ROW(M3:M))...
0 votes
1 answer
270 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 ...
1 vote
1 answer
3k views

Remove blanks in import range of multiple arrays

I have an importrange formula as below which references multiple ranges, calling the sheet URL's using the 'L Links'!Bx, reference, each 'book' having an identical sheet set. (There are actually 90 ...
0 votes
1 answer
27 views

Cumulative SUM formula

I want a cumulative formula that will return the running total for each value in column B. For example, in the rows in column A: =SUM(B$1:B1) =SUM(B$1:B2) =SUM(B$1:B3) =SUM(B$1:B4) etc. I would like ...
3 votes
1 answer
373 views

Filter date, and add blank rows between different time slots

I'm trying to build a reservations spreadsheet for my friend's restaurant where she can input on monthly sheets as resos come in, then see a filtered, sorted summary of any selected day. Here is a ...

1
2 3 4 5
17