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
1 vote
1 answer
40 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
0 votes
1 answer
120 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
1 answer
58 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?
1 vote
1 answer
118 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>...
  • 11
0 votes
1 answer
46 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
0 votes
1 answer
21 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. ...
0 votes
0 answers
12 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&...
1 vote
1 answer
24 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. =...
  • 11
-1 votes
1 answer
25 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 ...
0 votes
1 answer
23 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
0 votes
0 answers
15 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 ...
0 votes
1 answer
133 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 ...
4 votes
1 answer
170 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, ...
  • 55
0 votes
0 answers
21 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 ...
0 votes
3 answers
67 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 ...
  • 55
1 vote
1 answer
121 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 ...
  • 11
0 votes
2 answers
36 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
1 vote
2 answers
63 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 ...
  • 672
0 votes
1 answer
2k 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 ...
  • 255
0 votes
1 answer
20 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 ...
0 votes
3 answers
36 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 ...
0 votes
3 answers
168 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 ...
1 vote
1 answer
120 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 ...
1 vote
1 answer
702 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 || ...
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-...
  • 65
0 votes
1 answer
242 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
0 votes
1 answer
40 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, ...
  • 1
1 vote
1 answer
41 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 ...
  • 9
-2 votes
1 answer
48 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 ...
  • 1
0 votes
1 answer
72 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 (...
0 votes
2 answers
36 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 ...
0 votes
1 answer
75 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 ...
0 votes
2 answers
89 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
0 answers
9 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
0 votes
1 answer
545 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 & ...
0 votes
1 answer
78 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=&...
0 votes
1 answer
70 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
1 vote
1 answer
32 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
0 votes
0 answers
47 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 ...
0 votes
1 answer
169 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 ...
-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/...
0 votes
1 answer
126 views

Splitting a list contained within a single cell and want to associate/add text from a different cell in the same row to the new separated list

I am using Google Forms to collect information. In google sheet, I have rows of data and one of the cells contains lists that needs to be separated out. =query(FLATTEN(ArrayFormula(IFERROR(SPLIT(G2:G,...
  • 3
0 votes
2 answers
30 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
0 votes
1 answer
25 views

Google Sheet how to combine data from same sheet to other sheet

I have several tables on the same sheet with different numbers of rows and columns but basically have the same structure, now I want data from these different tables to be united in one master ...
  • 3
1 vote
1 answer
32 views

Need To Randomize Selections From A List But Ensure Uniqueness

I'm building a tool to randomize setup for a very large modular card game. There are several lists from which I need to select 2 to 6 non-duplicate entries, and each list is on a separate tab from the ...
1 vote
1 answer
54 views

Arrayformula to get closest non-numeric value above

I have Column A with mixed data types (numbers and strings) and in Column B I want for each numbers in Column A to display the string that is the closest above. Example: Title| 1|Title 2|Title Next ...
1 vote
1 answer
351 views

How to copy a cell from Google Sheets without getting double quotes (" becoming ""), BUT keeping the \n (char(10)) line breaks

I have a couple of Google Spreadsheets I use to generate config files for things. The config file data ends up in a single cell, which is copied and pasted. Problem is Google Sheets has this odd ...
  • 131
0 votes
1 answer
127 views

Google sheets reference Text in Cell in Array Formula & Countif formula

So, I have a Array Formula and Countif formula in place because I need to know the total of entries in a combination of ranges. I need to know the sum of the number of cells that have "X" ...
0 votes
0 answers
54 views

Array Formula Problem - How to Fill In Blank Rows with the Same Value before a Breakpoint which advances to the next letter

I have a Google Sheet which is grouped into segments of a TV show. So there's an A segment and then a B segment and so on ... I have allocated two columns to provide two different timing functions (...
  • 5
1 vote
0 answers
116 views

Use ArrayFormula to automatically update the column with a formula already containing an ArrayFormula

I am using the below formula to concatenate some data into one string: =TEXTJOIN("",FALSE,ARRAYFORMULA(IF((Data!$F$3:$F=$C2)*(Data!$G$3:$G=$D2),Data!$L$3:$L,""))) I however have ...

1 2
3
4 5
17