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.

1
vote
3answers
55 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 ...
0
votes
2answers
17 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?
1
vote
1answer
19 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 ...
1
vote
2answers
23 views

How to check if the 1st letter in a cell is UPPERCASE

I'm trying to find out if the 1st letter in a cell is UPPERCASE. I know how to convert case using PROPER(), UPPER(), and LOWER(), but not how to detect case. The following checks every word in the ...
1
vote
1answer
23 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 ...
1
vote
1answer
40 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 ...
0
votes
2answers
56 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 ...
0
votes
2answers
30 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 ...
0
votes
1answer
21 views

Autofill a cell when dates are entered in 3 other cells

I'm trying to create a way to keep track of our jobs by creating a Google spreadsheet. We can only order our final product when the digital and hard copy proof is approved and we have an order from ...
0
votes
1answer
19 views

Create sequential dates with each date repeated multiple times

For every day of the year, I need the day repeated in 10 rows. How can I accomplish this efficiently? e.g. 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/...
0
votes
2answers
46 views

Match word in a cell with a column of words

I need to match a word to a list of words and then insert the category name in the cell next to it. Eg. I will in Column A type a list of words like Apple, Red Apple, Coke, Carrot The formula must ...
2
votes
1answer
24 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
32 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 ...
0
votes
4answers
89 views

multiple IF statements with between number ranges alternative

How to efectively deal with multiple IF-statements and with multiple "between number" ranges in Google Sheets' spreadsheet (e.g. C column based on B column) with a rule set as follows: 01-10 11-20 ...
2
votes
1answer
24 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
20 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
16 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 ...
0
votes
1answer
80 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
3answers
33 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 | ...
0
votes
0answers
34 views

Change an entire google sheets to UPPER case, not with an add-on or onEdit(e)

I need to change all the text in a google sheets to upper case, the problem is it's filled in by Zapier which doesn't register as an edit for onEdit functions. It's also automatically converted to ....
0
votes
2answers
30 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 ...
1
vote
2answers
37 views

Add numbers to results of Google Sheets query

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

Is there a way that I can flag in Google Sheets when multiple cells contain the same values?

I'm wondering if someone can help me in my battle with Google Sheets. I'm creating a rota and I want it to flag if all senior members of staff are either off or all on the same shift (either Early or ...
0
votes
0answers
54 views

How do I interleave rows of data in Google Sheets or Excel

I have data on several different worksheets and I want to combine into single sheet. The data is text, not numeric. I can do this work in either Google Sheets or Excel. I have: Sheet1 R1 Sheet1 ...
0
votes
1answer
70 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/...
0
votes
1answer
65 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
1answer
119 views

Arrayformula and Sum queries. Possible alternatives

I am using a sheet to list and summarise teachers absences and substitutions. Here's the worksheet Basically in the sheet "Substitutions" all absences and substituions are stored record by record: ...
0
votes
2answers
41 views

A formula for importing specific data into another sheet

I need a function for column K of Sheet1, that says: "If the row of Sheet2 contains the same RID number as a row in Sheet1, then fill Sheet1 with the email address located in column K of Sheet2" ...
0
votes
1answer
85 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 ...
0
votes
0answers
46 views

Use arrayformula to allow vertical formulas to expand horizontally?

The following sheet (here) is using a vertical-range formula on some cells to calculate averages (vertically) every 3 rows. Once the end user will insert a new column, the newly created cells will ...
0
votes
1answer
25 views

Count unique tags

I’m trying to list and count all tags in my table. For some reason, anything after comma, is getting a white space before the word. So if the tag is found on the beginning of a record, it is seen as ...
0
votes
1answer
16 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 ...
0
votes
0answers
46 views

Google Sheets Combining multiple IF statements into one formula (as an Array)

Hope someone can help, got the formula below but I want to modify it so that the first IF formula, IF(F3:F="Delay","Delay", returns a “Delay” if it finds a match (not if it finds "Delay") and if it ...
0
votes
1answer
544 views

How to concatenate columns for multiple rows simultaneously in Google Sheets?

I have the following 3 rows of data in cells A1:A3 in Google Sheets: Doe, Jane Doe, Joe John Doe I want the following in B1:B3: Jane Doe Joe Doe John Doe Essentially, I'm creating a new column ...
2
votes
1answer
171 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(...
1
vote
0answers
26 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 ...
0
votes
0answers
25 views

Convert column with multiple subheadings to sortable table in Google Sheets

How can I convert a column with variable data like this: | <h2>TITLE 1</h2> | | <input ...>Item A</input> | | <input ...>Item B</input> | | &...
0
votes
1answer
249 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 ...
1
vote
1answer
564 views

Dynamic updating referenced cell from another workbook after adding/deleting rows using IMPORTRANGE - use ARRAY or VLOOKUP?

I add/deleted/move rows frequently in my workbooks. When I add/remove rows from Spreadsheet#1 ("S1"), I need the referenced cell in Spreadsheet#2 ("S2") to dynamically update so it still references ...
0
votes
1answer
30 views

Split rows dynamically based on row length (COUNTA) and transpose to 2D array

I have data in this format: And I need it to be reformatted into this: Basically, each row in the initial format is composed of 4 concatenated sections of equal lengths: Part(s), Product(s), Quant(s)...
1
vote
2answers
3k views

Google sheets function IF, AND conditional formatting

EDIT 2: link to test sheet: https://docs.google.com/spreadsheets/d/1IWQWDK9piw_xFVm_48XZ8YAS_LQqXi6sd2GITLIfoAQ/edit#gid=903221057 EDIT 1: added a column, so now J=K, K=L, L=M | updated formulas to ...
1
vote
2answers
35 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
1answer
42 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 ...
1
vote
1answer
19 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 ...
1
vote
2answers
37 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:...
0
votes
0answers
109 views

Array Formula Auto-Calculation in Google Forms

I have a google form which is returning data into Column A and Column B and I need to perform calculations on that in Column C, D & E. I have been using Array Formula so that C & D auto-...
1
vote
1answer
19 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 ...
0
votes
2answers
48 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 ...
0
votes
1answer
176 views

Count consecutive columns with the same values?

Before I get started, here is a link to the sheet example: spreadsheet sample Essentially what I want to do is Count the number of times the value in C5 occurs consecutively in its first occurrence. ...
1
vote
1answer
326 views

Compare two lists and produce a list of names not duplicated

I have one list of total available people and the second list of people who have been assigned. I would like to auto-populate a third list of the people (from the first list) who have not been ...