2022 Developer Survey is open! Take survey.

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
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 | ...
user avatar
  • 1
0 votes
1 answer
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(...
user avatar
  • 3
1 vote
0 answers
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 ...
user avatar
  • 11
1 vote
1 answer
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
user avatar
0 votes
1 answer
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 ...
user avatar
1 vote
1 answer
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?
user avatar
0 votes
1 answer
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>...
user avatar
  • 1
0 votes
1 answer
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 ...
user avatar
  • 1
0 votes
1 answer
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. ...
user avatar
0 votes
0 answers
7 views

Subtraction between SUM IF array

https://docs.google.com/spreadsheets/d/1GFS6J1vX88vsOCsKzrMB7Zf82m9QOHqu6oAmO4eJwH8/edit?fbclid=IwAR3Ek51w0QKx5t1bQ6T6YDy8FsKEfOHlh_UEee0MfuyPf0Fpl-ateVHVasQ#gid=0 In the above file, in column "G&...
user avatar
0 votes
1 answer
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. =...
user avatar
  • 1
0 votes
1 answer
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 ...
user avatar
0 votes
1 answer
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)...
user avatar
  • 1
0 votes
0 answers
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 ...
user avatar
0 votes
1 answer
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 ...
user avatar
4 votes
1 answer
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, ...
user avatar
  • 45
0 votes
0 answers
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 ...
user avatar
0 votes
3 answers
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 ...
user avatar
  • 45
0 votes
1 answer
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 ...
user avatar
  • 1
0 votes
2 answers
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 [...
user avatar
  • 1
1 vote
2 answers
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 ...
user avatar
  • 582
0 votes
0 answers
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 ...
user avatar
0 votes
0 answers
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 ...
user avatar
  • 1
0 votes
0 answers
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 '...
user avatar
0 votes
0 answers
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 ...
user avatar
0 votes
1 answer
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 ...
user avatar
  • 195
0 votes
1 answer
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 ...
user avatar
0 votes
3 answers
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 ...
user avatar
0 votes
2 answers
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 ...
user avatar
0 votes
1 answer
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 ...
user avatar
0 votes
1 answer
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 || ...
user avatar
0 votes
1 answer
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-...
user avatar
  • 21
0 votes
1 answer
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 ...
user avatar
  • 199
0 votes
1 answer
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, ...
user avatar
  • 1
0 votes
0 answers
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 ...
user avatar
0 votes
1 answer
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 ...
user avatar
  • 1
-2 votes
1 answer
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 ...
user avatar
  • 1
0 votes
0 answers
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 ...
user avatar
0 votes
1 answer
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 (...
user avatar
0 votes
2 answers
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 ...
user avatar
0 votes
1 answer
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 ...
user avatar
0 votes
2 answers
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 ...
user avatar
1 vote
0 answers
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. ...
user avatar
  • 11
0 votes
1 answer
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 & ...
user avatar
0 votes
1 answer
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=&...
user avatar
0 votes
1 answer
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 ...
user avatar
  • 1
1 vote
1 answer
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 ...
user avatar
  • 11
0 votes
0 answers
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 ...
user avatar
0 votes
1 answer
62 views

Arrayformula to get the max number of consecutive occurrence of a character inside a string

I have strings of different length like the one below: LWLLLLXWLWWWXWWWXWWLWLWLWWWWLWLLLXXWLLLLL From this string, I'd like to extract the maximum number of consecutive occurrence of L. Here it would ...
user avatar
-1 votes
1 answer
37 views

Get a single row with true and false if the string is contained in a range

First of, sorry for the title, I have no clue what to search for or how to name the question. I got an example sheet which might already explain everything: https://docs.google.com/spreadsheets/d/...
user avatar

1
2 3 4 5
15