2022 Developer Survey is open! Take survey.

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

726 questions
Filter by
Sorted by
Tagged with
32 views

### Creating a dynamic 2d array

I have to create variable length sequences from a column along its rows. Eg- Suppose the column is as below 2 4 3 I want to create a sequence like this 2 | 1 | 2 4 | 1 | 2 | 3 | 4 3 | 1 | 2 | ...
29 views

### How do I simplify this formula to be expandable?

The Example sheet The formula: For all x from 1 to 10, if (Sell <= Buy * x) then x, else ">=10" I thought I might be able to cheat a little with the sequence function: =ARRAYFORMULA(...
1 vote
24 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 ...
• 11
1 vote
31 views

### Text input if cell > than 1

I would only like the values in Column A to return if there is a value of >1 in Column B - I am unsure on a formula to do this
26 views

### Match value of cell if in between range and text match

Desired outcome: I would like to return a value in column C (column D is the values I'd like the formula to return) - the value in column C should return on the criteria it matches the product name in ...
1 vote
39 views

### Return matching text value in cell

I have a sheet of products and skus - I would like to have a formula that searches the entire column for matching text strings and returns it in a separate cell as the text string Is this possible?
16 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>...
• 1
21 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
19 views

### How can i make a formula to determine payment types

I am having trouble nesting and using multiple criteria. I want a column to determine if payment type was CASH, CHECK or WAITING depending on if the cells for Check# and Payment are containing data. ...
7 views

18 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. =...
16 views

### [SUM IF CELL ABOVE EQUALS CELL BELOW]

[SUM IF CELL ABOVE EQUALS CELL BELOW]. * link to the file https://docs.google.com/spreadsheets/d/1GFS6J1vX88vsOCsKzrMB7Zf82m9QOHqu6oAmO4eJwH8/edit#gid=0 * in column f of the file I'd like to get the ...
17 views

### What is wrong with the formular?

I am bloody new to this forum - so if I am doing something wrong, please let me know. Here is my question: I want to build a URL in Google Sheets where you have to fill out some parts (it for tracking)...
• 1
10 views

### Using a SUMIF(ARRAYFORMULA) to return from a column with IF(ISBLANK) Formula

I have a google sheet where there are two entries for each date, but I want them to count as 1 entry so I can average the total for a month. I have a Column "Month/Year" pulled from the Date ...
24 views

### In Google Sheets, how do I make an ARRAYFORMULA be the Boolean combination of two other columns?

I.e., if I have: A B TRUE TRUE TRUE FALSE FALSE TRUE FALSE FALSE say I want to create a column C that is A OR B: A B C TRUE TRUE TRUE TRUE FALSE TRUE FALSE TRUE TRUE FALSE FALSE FALSE Is ...
130 views

### Rearrange Name Format in Google Sheets using one formula only

Is there a simple way to do this using only one arrayed formula? Here are the names listed in the above image: NAME dela Cruz, Antonio Karl Jr. Woods, Oliver Hasegawa, Ken Sr. Sta. Maria, ...
• 45
17 views

### Running a script on select tabs in Google Sheets [duplicate]

I am not a coder, but I can normally do enough trial and error to get my script to function in the way I want my google sheet to work. The goal of my script: I want cell A to time stamp anytime that ...
41 views

### Need Help! Google Sheets Formula

Is there a simple formula for this? Where it will look for a Name with "- Leader" in Column A and its corresponding Value in Column B, and it will return the Name of the Leader with the same ...
• 45
57 views

### ArrayFormula COUNTUNIQUE

How would I turn this formula into an array formula so it automatically populated when a new row is created? =countunique(Z23:AC23) I need the formula to count the unique properties in a row between ...
23 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 [...
1 vote
55 views

### How Google Sheets ArrayFormula consider other column

In Row 1 of this sheet I have formulas like =ArrayFormula(sum(if(and(not(ISBLANK(C\$4:C\$49)),\$A\$4:\$A\$49>=\$A\$1),1,0))). What am I doing wrong? How can I change the formula such that the values in ...
• 582
16 views

### Changing Future Monthly Sales Goals Based On Previous Monthly Sales

What I have is a spreadsheet which needs to calculate future monthly required sales goals, based on an annual sales goal, and previous monthly sales. EXAMPLE: My annual sales goal is \$1,200,000. This ...
12 views

### I would like to sum values by month and by category [duplicate]

I have a personal finance expense tracker and want to be able to change the month with my data validation (Cell A9 in first screenshot), and sum by the categories below it. I'm stumped on this one and ...
6 views

### Update a list based on data from corresponding cell

I've got a google form that updates a sheet for locker reservations, on the reservation sheet I have to manually add the locker number and assigned key status. I've got two columns on the main sheet '...
27 views

### In Google Sheets, grab data from various cells (non touching) from 1 tab and bring to tab 2

I'm not 100% sure what specifically I'm asking here, I think I'm needing a variety of different formulas all in 1. In Google Sheets, I need to pull the results of checkboxes in tab 2, and bring them ...
74 views

### USING INDEX() WITH ARRAYFORMULA

I have a list of data on A1:a7 (blue range) and i have a REF on D2:H2 (green range). I want to use the REF to search in the list where the row is equal to the REF. The example result is on D3:H3 I put ...
• 195
17 views

### How to combine cells of data in Googlesheet to a single/consolidate row and remove duplicates?

I'm trying to consolidate all the invoices that we get from the same contractor to a single row that will have the business name, all the memos/invoices for the week in one cell along with the added ...
32 views

### Sum above cells matching crtiteria via ARRAYFORMULA

I have a sheet where data is added from time to time. The data is rather simple - object name and its quantity. I want to have a column with sum of object's quantity up to mentioned row. This is ...
70 views

### Google Sheets: keep track of days spent in different countries

I have prepared a Google Sheet that lists all flights I have taken in recent years, alongside the departing and arriving country: Date & time From To Outbound country Inbound country Days in ...
18 views

### How to create an array that will look up a string value in a sentence and return an int value in Google Sheets

My data is stored in Column F, the raw data looks like this: The Images- had poor pixel quality. What I want to do is to create an array formula that will look for my key word which is a string and ...
143 views

### Repeat a whole row N times based on a cell value

I'm using Google Sheet and I am kind of new at it so I want to split test Column and repeating other columns in each row. Before Splitting FILE NUMBER || NUMBER OF TESTS || TEST || ...
18 views

### Flexible formula to match strings in 1 row to run a formula in matching columns in another row

I have a row where all strings have the identical format (XX-#####-##) in which Xs are 2 upper case alphabet letters and the #s are integers. Short example, see the doc for more KC-30000-00 | KC-30000-...
• 21
146 views

### Compare and remove duplicates in Arrays (Google sheets)

I have two arrays of many rows , in my Google sheets, which have numbers such as Active Recovered 904;1505;1706 406;504;1502;1503 202;401;602;1104;1501 202;1502;1602;1704 I have shown only two ...
• 199
23 views

### Calculate specific contents of a cell that contains a string of numbers and text

I have a sheet that contains cells that have lists of data within them, for example: One cell contains: 1xExample, 2xAnotherExample, 5xThirdExample The cell below contains: 1xAnotherExample, ...
8 views

### #REF error when changing Drive location

I have a Google Sheets template that locations are making a copy of. A small percentage make the copy successfully and are able to use then randomly the formula's return #REF. All formulas are ...
25 views

### Trying to get array formula to work on Sheets

I am trying to to use the Array formula that includes a minus and divide value. Here is my formula:- IF(J3:J="",,SUM(((B3:B+C3:C)+(E3:E+F3:F))-\$X\$3)/\$X\$3) I tried inserting MINUS( before the ...
34 views

### i can't find the problem to this formula

=UNIQUE(QUERY({IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP Directory!A2:U");IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP ...
20 views

### Advanced Array Formula Documentation / Multidimensional Arrays / Conditional Variable Ranges

I'm manually tracking games and wins every few days. Games goes up by 3-20 with each update, wins is about 50% of games. So data looks something like this: Games Wins New Games New Wins 2800 ...
27 views

### Google Sheets Combining Is Between and AND operator Conditional Formatting

my programming knowledge is not great and I need some help. I am trying to get conditional formatting to highlight any cell in a column that contains values between 13-16 AND in a different column (...
27 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 ...
43 views

### Reference "This Cell's Column" in Google Sheets

https://docs.google.com/spreadsheets/d/1LG7AgcumhfQ1ZV0Em_oVjdPdrLCP-5CWWrw0WV1oCnY/edit?usp=sharing (We have to track 3 separate point totals for everyone who has participated in each game, game ...
44 views

### How to show a report that has "numbers in thousands" as full numbers?

If I have a lot of numbers in thousands and I don't want to use regexp to append to them three zeros to see the real number how can I proceed? I didn't found yet a formatting solution or any other ...
1 vote
8 views

### Retrieve URLs from website tables [duplicate]

I'm trying to save table data from a website by using IMPORTXML and IMPORTHTML. These tables include text/numeric data, which I can retrieve with no problems, and hyperlinks wich I cannot retrieve. ...
• 11
96 views

### Google Sheets - Arrayformula with textjoin, and, if

I'm trying to figure out how to make my arrayformula with textjoin to work, but the formula does not copy to another row. I want the job no automatically generate when i write data in F3 cell & ...
49 views

### Google Sheets IFS Text result based on multiple conditions FIX & SORT

I'm very new to Google Sheets and I'm trying to make find the correct formula for a text result based on multiple conditions, here is my best crack at it: =arrayformula(IFS(B9:B="",V9:V=&...
9 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 ...
1 vote
25 views

### Google Sheets, Referring to a cell range

Here's what I am trying to do. In cell C15, I put the cell reference of another cell, e.g. B2. I another cell, cell C16, I want to check that IF the value in cell C15 contains the cell reference to ...
• 11
41 views

### How to create a google sheets formula to return a value based on % values in other cells & weighted average formula? (FIXED)

I've been trying to create a scoring system for expected stock market returns - Sheet I'm having problems creating a formula to firstly give stocks a score in multiple conditions for example SS I want ...