Questions tagged [google-sheets-arrayformula]

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

175 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3 votes
1 answer
351 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 ...
Emma's user avatar
  • 31
3 votes
1 answer
402 views

Apply custom formula across all columns using Array Formula

I have trouble applying my custom formula across all columns using Array Formula. My custom formula is named getCountry where it could get the country based on an address. The address components e.g. ...
Carmad94's user avatar
3 votes
1 answer
323 views

Count number of rows with a specific MONTH in the date of a specific cell and a Boolean condition in another cell

Let's say I have a number of dates and whether they occupy full or half a day: DATE | WHAT | HALF? ... 2021-4-1 | Something Something | false 2021-4-2 | Something Something | false ...
User1291's user avatar
  • 143
3 votes
1 answer
6k views

Repeat formula n number of times in Google Sheets

Apologies, I'm very new to Google Sheets and spreadsheets in general. I'm trying to fill some cells with repeating data, and then analyze it. The data is pulled from another large spreadsheet and has ...
user avatar
3 votes
2 answers
373 views

How to make sequential list of months?

I want to fill column names with a sequential list of the last day of each month. For example, 3/31/2018, 4/30/2018, 5/31/2018, etc. Is there a formula that can do this? If cell B1 = 3/31/2018, I ...
Elliott B's user avatar
  • 266
3 votes
2 answers
6k views

How to pull last value in a Google Sheets column and populate it across another worksheet column?

This is my first post here- I hope this is the right place for it, apologies in advance if it isn't. Context: I have a password protected presentation that I need to give users access to, but only ...
user avatar
3 votes
3 answers
661 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,", ")...
Max Pacini's user avatar
2 votes
2 answers
191 views

Can I avoid copying this formula in new rows by using ARRAYFORMULA

Suppose I have this sheet showing which group (1, 2 or 3) each student joined on each day of class. Column B indicates in which group he/she last showed up, which is obtained by using the following ...
jmichel's user avatar
  • 43
2 votes
0 answers
34 views

How to link 2 sheets, so when a cell on sheet1 is modified, sheet2 got updated automatically and viceversa

I am trying to have the same updated values in two differents sheets of google spreadsheet. So when I edit sheet1, the the values in sheet2 got updated. Also the opposite: when I edit something in ...
Mario Márquez's user avatar
2 votes
0 answers
168 views

How to count cells and blank cells after up until another cell in separate columns

The title is a bit confusing, but essentially I am creating a calendar/tracker where a user inputs values into the "Work" column and the spreadsheet should count those values and blank cells ...
cbcb's user avatar
  • 21
2 votes
0 answers
127 views

If there anyway i can check if the cell contains the result of a arrayformula()?

For example i have this in A1 : =arrayformula(B1:B100) If in A10 i accidentally enter a value (which will overlap with arrayformula result) , it will produce error in the A1 because of existing value....
andio's user avatar
  • 397
2 votes
1 answer
618 views

How to combine formulatext() and arrayformula()?

I want to use formulatext() with arrayformula() : =arrayformula(formulatext(A1:A10)) But The arrayformula doesn't work. It should output 10 rows but it only returns a single row output. Is there ...
andio's user avatar
  • 397
2 votes
2 answers
176 views

Spreadsheet formula to calculate most common items in ordered items lists

I am searching for a correct google spreadsheet formula to go through the list of ordered items and summarise the resulting winning items. Like is you have an Oscar ceremony and you get votes from ...
shershen's user avatar
  • 163
2 votes
1 answer
664 views

Average the last N values in a Sheets column using Arrayformula

Experts, I need your help... I'm looking to create a formula (not necessarily an arrayformula), that will automatically calculate an average. This sounds easy, but surprisingly, it isn't. Let me ...
troyt's user avatar
  • 21
2 votes
1 answer
211 views

Trying to pull Duplicate data from multiple columns

https://docs.google.com/spreadsheets/d/1VSiKZbD_PoQTfbav7spalkyBgrt4T8juaLlKOM7LgLM/edit#gid=0 I have two columns, A and B. Throughout this project, the same name will appear in each column. I want ...
user247176's user avatar
2 votes
0 answers
196 views

Create a pivot table with a column for each assignee, containing rows of task descriptions

I have a two-column table with the first column being "task description" and second column being "assignee". This is something I'm unable to influence. Crude representation: I'd like to be able to ...
Puzomor Croatia's user avatar
2 votes
1 answer
3k views

Check for regexmatch in multiple ranges in Google Sheets

I have a sheet that auto-populates with a sub-set of articles (each a row with a column representing various information such as title, year of publication, author name, etc...) on the basis of a set ...
Shadow43375's user avatar
2 votes
1 answer
3k views

How can I change the formula of one cell based on the value in a different cell?

COLUMN D: Dropdown list of values COLUMN E: IF function returning different values depending on COLUMN D contents I have the functionality described above working properly. My problem is that I want ...
headroom's user avatar
2 votes
1 answer
38 views

Adding mm:ss times when expressed as TEXT

I have a series of numbers that, for a reason out of my control I can not change, are expressed as plain text strings on google sheets. The numbers are supposed to be minute second. mm:ss. For the ...
Wish to remain Anonomous's user avatar
2 votes
2 answers
486 views

How do I get the count number of weekdays in a datetime column in Google Sheets?

I have a timestamp in mm/dd/yy hh:mm:ss format from January to March. How do I count "how many days are Monday" in the range of A1:A199? (Or How Many are Tuesday, Wednesday, Saturday? This gives me a ...
rosa's user avatar
  • 85
2 votes
3 answers
2k views

How to scan rows for match with ArrayFormula?

Trying to search a row for a given value, but want to make an ARRAYFORMULA so that every time a new row is added the search will be performed on that row. Currently using =IF(COUNTIF($A2:$EI2,"="&...
hartj's user avatar
  • 21
2 votes
1 answer
48 views

LOOKUP for data from other sheet

So I have a drop-down menu in order sheet and I have an info sheet. In the info sheet, I have 2 columns, column A has the customer name, column B has the customer e-mail. So what I'd like to do is, ...
Galen S's user avatar
  • 21
2 votes
1 answer
301 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 ...
Michael Thopmson's user avatar
2 votes
1 answer
2k 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 ...
que syrah sarah's user avatar
2 votes
2 answers
289 views

Is it possibile to concatenate with bidimensional arrayformula?

I have a table like this: +----------+----------+----------+----------+ | Photo1-1 | Photo1-2 | Photo1-3 | | +----------+----------+----------+----------+ | Photo2-1 | Photo2-2 | | ...
Kintaro's user avatar
  • 53
2 votes
1 answer
3k views

How to sum amount with a criteria separately by day in Google Sheets?

I am using Google Sheets to track income and expenses. I have a separate file for each month and a master file to summarize each month. The file for monthly account contains a couple of sheets - each ...
Pat's user avatar
  • 21
1 vote
1 answer
20 views

Formula to return a value when the adjacent column's text contains any value from an array of text strings

In Google Sheets I have an array named "Tech_issues" with the following values: Array name value 1 value 2 value 3 value 4 Tech_issues crash froze glitch error I'm looking to create a ...
flipskyrain's user avatar
1 vote
0 answers
33 views

Google Sheets Arrayformula for timestamps of checkboxes changed

I am looking for LAMBDA-based formula for hundreds of rows which will add timestamp based on checkbox changed for that particular row. Need it to be ARRAYFORMULA/BYROW, as number of rows grows ...
wass rubleff's user avatar
1 vote
1 answer
161 views

Multiple large GOOGLEFINANCE formulas make spreadsheet unresponsive

Problem In Google Sheets I have huge GOOGLEFINANCE formulas that return accurate results however the sheet is often unresponsive, and can take a few hours to recalculate when reloaded. Is there a way ...
Varun's user avatar
  • 11
1 vote
1 answer
58 views

Google Sheets formula to round up columns to nearest thousand and sum

So i have a column with numbers and want to sum the column, but i want them to be rounded up to the nearest thousand. =SUM(ARRAYFORMULA(ROUNDUP(J4:J ,-3))) I tried this however I am just getting a ...
JMad's user avatar
  • 11
1 vote
1 answer
91 views

Unable to create an ARRAYFORMULA that will AVERAGE the results of a QUERY

I have a sheet data that tracks client transactions. Each row is a transaction The columns are: LAST NAME PHONE NUMBER SUB TOTAL TIP PERCENTAGE The TIP and PERCENTAGE columns are calculated based ...
Jakov Grams's user avatar
1 vote
1 answer
19 views

Import values from others spreadsheets to a single one

I'm having some trouble to import data values from many spreadsheets to a single one. I need to import all the values from all columns "A" of each spreadsheets to the column "A" of ...
Axel Frey Guarenas's user avatar
1 vote
1 answer
39 views

How to use ARRAYFORMULA to remove values that contain brackets?

I am trying to write a formula that pulls all data from column A that doesn't contain brackets. Source Data Desired Result (no values containing brackets)
Colin Mills's user avatar
1 vote
1 answer
103 views

How to retrieve today and tomorrow's values in a specific tab of a dynamic calendar

Employees are selected in their corresponding shift on each month tab in a dynamic calendar See 092022 tab in my example: I want Pemanence tab will be able to retrieve automatically the values in the ...
Bazu's user avatar
  • 53
1 vote
1 answer
455 views

Split, Flatten, and Repeat Cells in Certain Columns Within an Array Formula for Google Sheets

I am looking for a formula in google sheets to to break down an existing data set at once in a format that's a little more versatile. Here is the link to the sample sheet which is also the source of ...
Tom-EH's user avatar
  • 11
1 vote
0 answers
30 views

Add more conditions to the formula

=query({IMPORTRANGE("1c6ihzJjzD_Nhbz5rB1s0485PS98V-Q4bfVAiIon5vSA","MC2!A11:S5000")}, "select Col19, Col1, Col2, Col3, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, ...
Anastasia's user avatar
1 vote
0 answers
50 views

How do I perform a VLOOKUP to the left without explicitly naming the left column range in Google Sheets?

I have an array of data that I want to look up a specific phrase from. Once the phrase is found I would like to output the value or phrase of the cell to the direct left of the cell that’s found. I ...
user3264110's user avatar
1 vote
1 answer
250 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>...
Maya's user avatar
  • 11
1 vote
1 answer
30 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. =...
Rusha's user avatar
  • 11
1 vote
1 answer
229 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 ...
Hisaam's user avatar
  • 11
1 vote
1 answer
378 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 ...
Patricia Beate Lulle's user avatar
1 vote
2 answers
49 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 ...
helpmepls's user avatar
1 vote
0 answers
160 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 ...
Exdefoot's user avatar
1 vote
1 answer
128 views

Why my Arrayformula doesn't work

I have this google sheet : link is here : https://docs.google.com/spreadsheets/d/1_-AcgJud2DclKtarcJj39DNB84CzthUzO_n5F8mBgKY/edit?usp=sharing On the top is my matrix table. It has key on the left ...
andio's user avatar
  • 397
1 vote
1 answer
117 views

Can I implement this moving average with missing periods using ARRAYFORMULA?

I have these columns: A person_id B date C cupcakes_eaten D date-35 (35 days prior) There can be multiple rows with the same person_id and same date. There can be days with zero rows for a ...
John Bachir's user avatar
1 vote
1 answer
24 views

How to add arrayformula function to this formula with IF function?

I made a table that has two columns (namd & State). The 4th column(check) makes conditional formatting possible for any of these combinations: (if name is either "Ali" or "John"...
Hassan Almaateeq's user avatar
1 vote
0 answers
37 views

Multiple columns hierarchical view

I’m trying to display my subcategories in a hierarchical view but can’t seem to figure it out with a formula rather than with pivot tables or scripts What I have: A |B |C Equity|...
Winston Van's user avatar
1 vote
2 answers
164 views

Finding all word pairs from range of strings in Google Sheets

Let's say I have a column of values, each of which is a string. A This is a string This is longer than that My favorite is short But sometimes short is longer How is a short longer I would like ...
Marston Gould's user avatar
1 vote
0 answers
62 views

How can I use a dropdown menu in Google Sheets filter out data and populate cells for a an expense tracker?

I have created a personal expense tracker (budget) that uses google form responses and categorizes them by fortnights and expenditure categories. This has been successful on a sheet called 'Yearly ...
Sarah Harris's user avatar
1 vote
2 answers
1k views

How to autofill date but skip Friday of every week?

I was trying to skip Friday of every week when I am trying to autofill the date in Google sheet. There are formulas to skip weekends but I couldn't find any formula to skip any specific day of the ...
Tawhid Khan's user avatar