Questions tagged [google-sheets-arrayformula]

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

199 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3 votes
3 answers
618 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,", ")...
user avatar
2 votes
0 answers
28 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....
user avatar
  • 195
2 votes
1 answer
466 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 ...
user avatar
  • 21
2 votes
0 answers
149 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 ...
user avatar
2 votes
2 answers
74 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 ...
user avatar
  • 216
2 votes
2 answers
5k 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
2 votes
1 answer
2k 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 ...
user avatar
  • 21
1 vote
0 answers
52 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 ...
user avatar
1 vote
1 answer
176 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. ...
user avatar
1 vote
1 answer
136 views

How to convert text to a number in Google sheets

I've built a form that dumps data to a Google spreadsheet and that data, studio reads it... and turns that into a dashboard.. only problem is the text doesn't convert very well for building a dash ...
user avatar
1 vote
0 answers
35 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|...
user avatar
1 vote
1 answer
80 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. COL 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 to ...
user avatar
1 vote
0 answers
30 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 ...
user avatar
1 vote
2 answers
304 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 ...
user avatar
1 vote
1 answer
26 views

How to create an average value for every cell which contains a certain phrase?

I need to find the average value of cells containing a certain set of initials to create a scoreboard. The value of the cell being the score and the brackets containing the initials of the score ...
user avatar
1 vote
1 answer
28 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 ...
user avatar
  • 123
1 vote
0 answers
19 views

Adding Button to add points to another sheet - Basketball Live Scorebook

I have created a scorebook sheet in google sheet for tracking live basketball stats. One sheet with the scorebook table for the team with each player one a row. I want to have a separate tab for data ...
user avatar
  • 11
1 vote
2 answers
48 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 ...
user avatar
  • 153
1 vote
0 answers
40 views

Array formula to extract N non blank cell in Row 1-16

I have a google form where team coaches input which players played on a given day. They also input if they were Man of the Match, Yellow/Red Cards Received, and how many Goals they scored. When the ...
user avatar
1 vote
0 answers
173 views

How do I calculate subtotals by using ArrayFormula and without formula in the cell itself in a Google Sheet

I'm working on an estimate sheet, got it all worked out pretty neatly and trying to tackle the formulae, make them work from the header rows using ArrayFormula instead of having each cell holding ...
user avatar
  • 111
1 vote
1 answer
156 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 ...
user avatar
1 vote
0 answers
26 views

Computing the Stock Value based on FIFO (First in Fist Out)

I have as sheet in which I'm storing inventory purchase date, quantity and rate. In a separate sheet I'm tracking the export of the inventory. Hence, I'm able to compute the total purchases made till ...
user avatar
1 vote
1 answer
56 views

Help Sorting Data

I have a spreadsheet that I'm using to keep track of stats for players in a league. This is the link: https://docs.google.com/spreadsheets/d/193GxNzaX4-bGrNB8eT4vhqAOKhUrChRDgq-HYVU_Txk/edit#gid=...
user avatar
  • 11
1 vote
2 answers
94 views

Extracting range from of an array

From the following single cell array: 0, 1, 1, 2, 3, 5, 8, 13, 21, 34 I'd like to extract 3,5,8,13,21 into a single cell by specifying index 4 : index 8 of the split of that cell. The Challenge is ...
user avatar
  • 19
1 vote
0 answers
33 views

Google Sheets Area Graph(s)

I need (want) to create a Google Sheets Area Graph (Chart). The Graph/Chart will represent "Date"s. I need the Xaxis to be the dates (as days) between these start and end dates: 01/27/2020, 4/25/2020 ...
user avatar
1 vote
0 answers
18 views

Summarize all data from other tabs with same format and column

I have customer sheets which i must summarize into a single tab so i use this query; =QUERY({'ROC155'!B2:AB;'ROC152'!B2:AB;'ROC135'!B2:AB;'ROC002'!B2:AB;'ROC009'!B2:AB;'ROC145'!B2:AB;'ROC048'!B2:AB;'...
user avatar
1 vote
1 answer
1k views

How do you interpolate data in a horizontal range that's non-linear?

I've been told what I'm hoping to do is called 'spline interpolation'. I've imported monthly youtube channel viewership figures from google API. And I've layed out the dates and views in a horizontal ...
user avatar
1 vote
0 answers
109 views

Survey Data & Google Sheets: Excluding Missing Values?

I am using survey data in Google Sheets with rows for "before" and "after" responses, with the same survey administered to the same subjects at two different time periods. With each question, a number ...
user avatar
  • 11
1 vote
0 answers
79 views

why does this array formula on google sheets display no match when i add a new row?

hi and thank you for reading my post. please help me with my array formula. the goal of the formula is to display the status on the column of my work sheet (queue, working, done and blank). it works ...
user avatar
1 vote
0 answers
122 views

Arrayformula with iferror and vlookup does not update automatically

My spreadsheet is for inventory checking. I use several of arrayformula ,iferror and vlookup. The problem is I just noticed that sometimes the formula does not update. It might because I duplicated ...
user avatar
1 vote
1 answer
2k 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 ...
user avatar
1 vote
1 answer
1k 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 ...
user avatar
1 vote
1 answer
20 views

Issue with finding the averages of multiple rows at once

I am currently tasked with hundreds of rows of data for a project. I need to take the average of each row, but there are way too many rows for me to do by each individual row. Is there any way to take ...
user avatar
1 vote
1 answer
5k 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
1 vote
1 answer
24 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 ...
user avatar
1 vote
2 answers
309 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 ...
user avatar
  • 75
1 vote
1 answer
977 views

Arrayformula for running MAX of separate column

I'm trying to use ARRAYFORMULA in Google Sheets to get the max figures from a separate column only up to the row being calculated each time but can't for the life of me figure out how. It sounds more ...
user avatar
  • 111
1 vote
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,"="&...
user avatar
  • 11
1 vote
1 answer
257 views

Populating alternatives in question 2 based on answers in question 1

I have ten people who sell something to companies, and I want to track what they do IN GOOGLE FORMS. Every agent has his own 10 companies that he contacts. So, in Question 1, I will have a drop-down ...
user avatar
  • 11
1 vote
1 answer
43 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, ...
user avatar
  • 11
1 vote
1 answer
227 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 ...
user avatar
1 vote
0 answers
39 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 ...
user avatar
1 vote
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 ...
user avatar
1 vote
2 answers
278 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 | | ...
user avatar
  • 33
1 vote
0 answers
80 views

Function always parses column in first row but would like it to repeat

I am trying to make a function that repeats in each row of a column that fetches the URL in the first column, does an xpath query and gets an image. Currently its all working other than the fact it ...
user avatar
  • 111
1 vote
1 answer
44 views

How to Iterate between colunns and rows?

I am running the following forumula: =IF(INDEX(C$2:$2, 1, ROW(1:1)),CONCATENATE(B$2,"-",INDEX(C$1:$1, 1, ROW(1:1)))) On the following table Basically, If a value is present in column C2, I am ...
user avatar
1 vote
1 answer
131 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 ...
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
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