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.

6
votes
2answers
10k 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 ...
6
votes
1answer
2k 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, ...
5
votes
2answers
530 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 / ...
4
votes
1answer
5k 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 ...
4
votes
4answers
60 views

Joining two rows of data with alternating delimiters

I'm working with two rows of values that are related to each other and trying to build a formula that will compile the data into one cell, with syntax that uses two delimiters. If a cell in the ...
4
votes
1answer
4k 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. ...
4
votes
2answers
5k views

Query Multiple Ranges with Results in Row

I am trying to query five ranges with the results displayed across a single row. To do this, I was using { } with commas separating each range, but it's only giving me the first range in return. ...
4
votes
2answers
512 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
100 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 ...
2
votes
2answers
32k views

Google Spreadsheet formula, MINUS?

I have a spreadsheet with 3 columns: | ITEM | COST | SUBTOTAL | Subtotal, for example, is 1000 to start, like: -------------------------- | ITEM | COST | SUBTOTAL | -------------------------- | ...
2
votes
1answer
327 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 / ...
2
votes
2answers
65 views

Query returning #VALUE error when the column has no values

The below query returns #VALUE error when the column H is completely empty: =QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, ' ', ' ', ' ', G, ' ', ' ', ' ', '"&...
2
votes
1answer
45 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
198 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, ...
2
votes
2answers
52 views

Processing data from multiple cells and comparing results with another cell

For most orders, I have multiple deliveries under the same recipient ID and I need to compare whether everything ordered is really delivered. All products are divided by a comma and have always the ...
2
votes
2answers
146 views

ARRAYFORMULA with IF, DETECTLANGUAGE and LEN

I'm trying to make an array formula to detect the number of English characters in a range. When I use the following for individual cells: =IF(DETECTLANGUAGE(ARRAYFORMULA(A5:A))="en",LEN(ARRAYFORMULA(...
2
votes
1answer
33 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 ...
2
votes
1answer
456 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 ...
2
votes
1answer
282 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 ...
2
votes
1answer
161 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?
2
votes
1answer
18 views

Add missing dates to list of dates with values

Current Data: 9/20/2018 6 9/22/2018 3 9/23/2018 1 Desired Output: 9/20/2018 6 9/21/2018 9/22/2018 3 9/23/2018 1 I would like to have the missing dates added to Column A while the ...
2
votes
2answers
13 views

How do I automatically enter sequential numbers without using the mouse in Google Sheets?

I would like to create a series of number in Google Sheets, without dragging with the mouse. When using the Shortcut Cmd+R (Fill Right) it just copies the value of the first cell. Initial State: ...
2
votes
1answer
35 views

Sum value in cells that contain both number and text

I need to sum a range of cells with text in them. Example: a1= "25,00 market" b1="24,00 credit card" c1="75,11 food" a2= "10,00 sweets" b2="9,00 market" c2="24,00 keyboard" I want to sum ...
2
votes
3answers
49 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,", ")...
2
votes
1answer
25 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 ...
2
votes
1answer
44 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 ...
2
votes
1answer
40 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’...
2
votes
1answer
24 views

What's wrong with this arrayformula?

=arrayformula(if(LEN(A:A),if(ROW(A:A)=1,"Outstanding amount Payment", sum(QUERY(daily_fee_collection!A:D,"select D where B="&A:A&"",-1))))) I am trying to cross-check for payments made in ...
2
votes
1answer
332 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(...
2
votes
1answer
23 views

How to summary the multiple entry into a single sheet by name and by date?

Names are in rows and Dates are in columns. Sheet 1: Name 1-Jun-18 2-Jun-18 3-Jun-18....... KB CL KB EL AJ SL SD SL SD CL I need ...
2
votes
1answer
318 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
2answers
409 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 ...
1
vote
2answers
38 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 ...
1
vote
3answers
74 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 ...
1
vote
3answers
34 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 | ...
1
vote
2answers
2k 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/...
1
vote
3answers
473 views

Convert Text String into Date/Time Value

I'm fetching data into a Google Sheet. The date column contains multiple cells with the following text string pattern: Mon Mar 11 19:05:32 +0000 2019 The pattern always repeat itself: [day] [month] ...
1
vote
1answer
40 views

How to return all values (horizontal array) in B that has the same key stored in A?

Sample Sheet 1: A B 1 a x 2 a y 3 a z 4 b i 5 b h 6 b w Formula result, in Sheet 2, I am looking for: A B C D 1 a x y z 2 b i h w
1
vote
2answers
37 views

Conditional formatting rule that compares against a range of values

I have a list of 2019 dates (col A) and holiday dates (col G). Wherever there's a date in col A that matches any date in the range G2:G20, I would like to highlight it in grey. See this sample ...
1
vote
2answers
257 views

Google Sheets Query with Date and Time

I need help with a query. I need to filter on some Google Form data and only show the results that have been submitted AFTER a specific timestamp. Timestamp Cell = J2 - This cell is in the following ...
1
vote
1answer
2k views

How can I create a unique list of data across multiple rows AND columns?

Google Sheets has a UNIQUE() function which when given a column of values, will output a column of all unique values. This works well for column-specific data, however, you have multiple columns and ...
1
vote
1answer
103 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 ...
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 ...
1
vote
1answer
10 views

Rotate and duplicate row values in Google Sheets

I'm trying to rotate values in a sheet and then duplicate the first value in the row as a header. For example: a | b | c | d would become: a | b ----- a | c ----- a | d Is there any way to do this ...
1
vote
1answer
18 views

How can I have Google Sheets automatically parse and calculate text that follows a certain pattern?

I have the following sheet: https://docs.google.com/spreadsheets/d/1iEMjWVEwyuqbn_imW1UZ-AF2NlE__IbRZdHQifKJjko/edit?usp=sharing I would like to calculate "Total Work" by deriving it from my own ...
1
vote
1answer
36 views

COUNT UNIQUE values by group ONLY IF NOT already counted in previous group

I hope to explain clearly what I'm trying to do. I have some data in column B, grouped as shown in column A. I'd like to count the unique values for each group present in column A, not taking into ...
1
vote
1answer
15 views

How to find the average of difference between two columns?

Imagine you have a Google sheet with two columns, each holding a time - let's say one is bus departure and the other is bus arrival. Is there any elegant way to present the average of all the time ...
1
vote
1answer
21 views

Alternatives to dragging formula down?

I'm having trouble applying the following formulas to entire columns in Google Sheets. I think that an array is the best option because there are 5000+ rows, but any help is appreciated. I'm really ...
1
vote
1answer
18 views

Shortening this Formula, and Future Proofing it

I am creating a Google Sheet to use as an employee schedule. On this sheet, I have Cells which will contain the Total number of Hours worked per day by all employees. At the moment the formula I ...
1
vote
1answer
11 views

COUNTIFS issue, output is always zero

I have no idea what I'm doing, considering there's plenty of topics on COUNTIFS. However, each solution I've tried, as well as my own solutions, which I shall list, end up in the same thing... ZERO. ...