Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more

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
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
2 answers
25 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 ...
0 votes
1 answer
758 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
46 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
1 answer
321 views

sequential number as long as they are in the same category, but if the category changes, the numbers start over for each category

I have two columns in my Sheet, one for the serial numbers and the other for the category. I want the numbers to be sequential as long as they are in the same category, but if the category changes, I ...
2 votes
2 answers
677 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
2 answers
267 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
21 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
134 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
94 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
166 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
191 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
166 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
630 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
264 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
26 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
357 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 ...
2 votes
1 answer
53 views

Sum of Cells (supplementary question)

This question which specifically referred to summing a column of numbers until a break and then subtotalling on the next following line was answered very helpfully by this answer. Supplementary to ...
1 vote
1 answer
953 views

Using array formula in Google Sheet

Trying to make an ARRAYFORMULA that executes a query function which takes a reference to the current position of the row number and the specified column name. This is my version of formula: =...
1 vote
1 answer
96 views

Unable to create an ARRAYFORMULA that will AVERAGE the results of a QUERY

I have a sheet data that tracks client transactions. Each row is a transaction The columns are: LAST NAME PHONE NUMBER SUB TOTAL TIP PERCENTAGE The TIP and PERCENTAGE columns are calculated based ...
0 votes
3 answers
27 views

Finding strings from a list between 2 string conditions that are part of the list

In Google Sheets, I have a list of unique strings that I want to filter based on two conditions. These conditions are two strings within that list. Ultimately, I want to find all the strings, ...
1 vote
1 answer
97 views

Google Sheets formula to round up columns to nearest thousand and sum

So i have a column with numbers and want to sum the column, but i want them to be rounded up to the nearest thousand. =SUM(ARRAYFORMULA(ROUNDUP(J4:J ,-3))) I tried this however I am just getting a ...
0 votes
1 answer
175 views

How to assign a name to a membership number vice versa?

I would like to learn what formula I can use to make my timesheet more efficient. How to assign a membership number to a name vice versa? I need to add lesson hours to each membership daily and ...
0 votes
1 answer
365 views

Create a list of names of those who answered a question correctly

I have a finished Google Form connected to a Google Spreadsheet - I want to list the names of everyone who answered a specific question correctly. I've spent a while trying to figure out how to do ...
1 vote
1 answer
1k views

How to ignore certain strings when creating unique list of words in Google Sheets?

In Google Sheets, I am using the formula to create a unique list of words: =unique(transpose(split(ArrayFormula(concatenate(B2:E"+")),"+"))) But I want to filter out certain strings. My unique list ...
2 votes
2 answers
494 views

How do I get the count number of weekdays in a datetime column in Google Sheets?

I have a timestamp in mm/dd/yy hh:mm:ss format from January to March. How do I count "how many days are Monday" in the range of A1:A199? (Or How Many are Tuesday, Wednesday, Saturday? This gives me a ...
1 vote
1 answer
122 views

Fetch value of a cell based on dropdown menu items results on another sheet

I have 2 Sheets named as Sheet1 and Sheet2... Sheet1: Contains Drop down list on B4 Column. Based on the selection of an item, it pulls the result of the open price shown on D4 column. Sheet2: ...
0 votes
1 answer
668 views

How to use FLATTEN function in query function

Below in my query result: In the above table "Form Response" table is the response received in Google Sheets from Google Form. I want to convert "Form Response" table to "...
1 vote
2 answers
32 views

Finding preceding n results from a list based on conditions in Google Sheets

I have the following list in a Google Sheet: List Apple Banana Pear Grapes Kiwi Mandarin Papaya Orange Tomato Plum Watermelon Based on these two following conditions, I'm trying to get ...
0 votes
1 answer
795 views

Summing hours spent on a subject in Google Sheets' "Assignment Tracker"

Hi everyone! I love the idea of this Assignment Tracker in Google Sheets. But my goal for time management is to find out how much time I'm spending on each subject. Is there a way to automatically ...
0 votes
1 answer
60 views

Auto sorting / copying data based on inputted data?

Sorry if this is a bit confusing or if I’m not explaining this well enough. Using my provided picture as an example how would one auto copy data that gets inputted into that table based on estimated ...
0 votes
2 answers
22 views

I wanted to set the maximum value of each unique input as active

In the example data below, for each unique SYS Name, the Status column is marked Active in the row with that SYS Name's highest Serial Number. In Status, Column C, for each unique Sys Name the row ...
5 votes
4 answers
7k views

How to use max() with arrayformula()

This is my sheet : in column E, i want to find the max among col A,B,C using this simple formula in E1 : =max(A1:C1) But rather than pasting the formula to E2 .. E10, i want to see if possible to ...
1 vote
1 answer
79 views

SUM across other worksheets (without hardcoding their names)

I've got a spreadsheet for my organisation's management accounts. Each project has a worksheet, setting out income and expenditure by financial year. This is aggregated together in a few sheets ...
0 votes
1 answer
22 views

Connect Participant Scores(Column D) to their id Numbers(Column A) by sorting Columns B and C

I have 4 columns of data. I am trying to connect the Scores to the idNumbers. Two sets of data. Column A and B are idNumber and Member names. And Column C and D are Participants and their Scores. The ...
2 votes
1 answer
62 views

Can an Google Sheet Filter function base its include on a whole row?

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 ...
0 votes
2 answers
76 views

Google Sheets: Make the cell data the name of the link

Update: I have attached a pic for example. -------- I have ID numbers in each cell and I want that number to be a link to their file. I know how to make a separate hyperlink that will ref that cell to ...

1
2 3 4 5
17