Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
666
questions
1
vote
1answer
76 views
Listing and formatting tagged data
I'm creating a list of texts with some tags associated to them in Google Sheets, each text has a "tags" column where the tags are separated by commas.
I want to have a second sheet where the ...
0
votes
1answer
219 views
ARRAYFORMULA and FILTER
The following sheet is an example of what I'm trying to do : https://docs.google.com/spreadsheets/d/1B9a8OUvwciCAYURLZq-Jd2KeSUZsYlalAFNsANzRzNw/edit?usp=sharing
In the first sheet ("Matrix"), I have ...
1
vote
1answer
139 views
Convert string to real date time value?
Here is my current input:
14.05.20
12.37.01
And yes, as it shows, there is a line break in the cell...
Here is example sheet: https://docs.google.com/spreadsheets/d/...
1
vote
1answer
304 views
Find the last instance of multiple values
I really need some help with this.
I need to search for the value of A2: B2 in another tab in google sheets and it needs to return the row number of last occurrence/entry of the values in A2: B2 in ...
0
votes
1answer
26 views
How to replace or substitute one column of data with their corresponding value from a list
I'm trying to replace a column of data (Prodct SKU) on one sheet (a) with the corresponding value taken from 2 columns on sheet (b) (Product SKU & Product ID).
I've attached the spreadsheet to ...
2
votes
1answer
33 views
How to autofill specific cell when any next cell is filled
+------------+---------+---------+
| Date | Col1 | Col2 |
+------------+---------+---------+
| 2018-01-01 | Value 1 | Value 1 |
| 2018-01-01 | Value 2 | Value 2 |
| 2018-01-01 | Value 3 | ...
1
vote
1answer
127 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 ...
2
votes
1answer
48 views
How do I get a formula copied to all cells below it in the entire column and increment by x instead of manually dragging it down to eternity?
If in Cell A2 I have the formula,
=IF(MOD(ROW(),1)=0,A2+1,A2)
how do I get it copied to all the cells below in the entire column (e.g. 900'000 cells) instead of manually dragging it down to ...
1
vote
1answer
60 views
Formula to find the cell in column C where a value in column A = a value in column E
I'm trying to create a formula in a google sheet to find the value in the C column where the value in the A column is equal to the value in the E column.
E.g. Column E = 1 in Row 9-16 and Column A = ...
2
votes
1answer
71 views
Array formula to show a leading zero
I have a Google Sheet where an array formula in Column I creates a video name based on the content entered into columns A+B+C+D.
Column C is the number of the video. For some videos, I need a leading ...
3
votes
1answer
226 views
How to SUM LARGE 5 largest elements out of 8 numbers in a row
In Excel I am able to get the output using this formula:
=SUM(LARGE(C5:L5,{1,2,3,4,5}))
But in Google Sheets I am unable to get the right answer. Can anyone please help?
In the image below, ...
1
vote
1answer
90 views
Array formula with concantenate not working
I have a Google Sheet where I am wanting to use an array formula so it auto copies down the column rather than the current formula which needs to be copied down manually.
=(IF($E2="Yes", $D2, ...
-2
votes
1answer
407 views
Exponential Moving Average
What is the formula of Exponential Moving Average (EMA) in Google sheet ?
How it can be used to calculate Share market stock EMA of different period. Eg 5, 21, 50 Days.
Can anyone share Demo sheet.
0
votes
1answer
143 views
Conditional Find and Replace in Google Sheets
Example data:
A B
------
1 HELLO
2 HELLO
2 HELLO
3 HELLO
I want to replace $ with , WORLD! but only if A is equal to 2. Such that the final result would be:
A B
------
1 HELLO
...
0
votes
1answer
46 views
Add fields and update array and scripts
I want to add some questions to my Google form but my response sheet has a script and an array, which do not like that.
Here is what it should look like:
The URL is the link for the form.
Here is ...
1
vote
1answer
71 views
How to populate a cell based on specific name and specific date using a query function?
Hello new to the forum here and relatively new to all this data business. I feel pretty confident in my skills but this one has me stumped.
Below you will see my initial query of dates, ditch_name(...
-2
votes
1answer
31 views
Google Sheets atomisation
I hope you are all well and healthy!
I am looking for a solution in Google Sheets to automate something like this:
and it could auto generate something like this
Thank you in advance
1
vote
0answers
19 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 ...
2
votes
1answer
51 views
Vlookup with values from another tab
Link to my sheet: https://docs.google.com/spreadsheets/d/1E3gH661qnZDUvP48Ubg6epuHwpDH70gANE8wW968dYg/edit?usp=sharing
I'm having trouble understanding what I'm doing wrong.
I'd like to have this ...
0
votes
0answers
174 views
Currency Conversion, Array Formula, Google Sheets
I was hoping to use array formula in conjunction with Google Finance to convert currencies on specific dates. I've been using a google form to enter basic expense information and the form adds a new ...
1
vote
1answer
820 views
Left Join in Google Sheets
I have A and B.
Google working example here:
https://docs.google.com/spreadsheets/d/157x9Yg9tG2pmV-q4wZVn0NW1xas4VSin3zASbrCV3t4/edit?usp=sharing
Sheet A stores data about patients.
Sheet B ...
2
votes
1answer
520 views
Formula to find the second to last (or nth to last) cell with value ignoring empty rows/cells
I have a spreadsheet that I used to run in Excel but due to other family members needing access to it I've moved it over to Sheets.
Anyway, I have the following formula that was running in Excel that ...
1
vote
2answers
5k views
Google sheet, images disappear randomly
Using Google Sheets I'm trying to populate a list of countries with (among others) names, ISO-alfa2 code, and flags. I use this IMAGE() and ArrayFormula() to get the flags from URL in my private ...
1
vote
1answer
33 views
How to clear a cell if there is no data in it
How to clear a cell if there is no data in it?
=ArrayFormula(REPLACE(IMPORTRANGE("1I1MK51uYT_WJDh0fiiHxLjnYMoQOVoZi41XnHM", "SetstudentMatrik!C2:C"),1,6,"******"))
1
vote
1answer
26 views
get value left of max value
I have a sheet that looks like:
Date Value Highest Lowest
----------------------------------------------------------------
1-1-2020 23 26 20
2-1-2020 20
3-...
2
votes
1answer
54 views
Issue trying to sum different columns
I am trying to sum 3 different columns and I'm not being successful.
My function is: =ARRAYFORMULA(SUMIF(MONTH(D:D), 2, A:C))
Where D is a column with dates, and A,B and C columns with currency (A-...
2
votes
0answers
136 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 ...
2
votes
1answer
15 views
SumIf OR COunt if
I want to count dates monthwise with person name. This is my data:
Mandeep 1/2/2020
Mandeep 1/2/2020
Mandeep 1/2/2020
Mandeep 1/2/2020
Mandeep 1/2/2020
Mandeep 1/2/2020
Mandeep 2/2/2020
Mandeep 2/2/...
0
votes
0answers
93 views
How can I SUMIF a row, but have the formula automatically applied to all new rows below it in Google Sheets?
I've made an inventory tracking spreadsheet that gets raw data from Google Forms. Forms inputs data into Sheets in a less than ideal way, but there's nothing I can do about that, so I'm stuck post-...
3
votes
1answer
233 views
Print incremental numbers in Google Sheets
Cell A1 holds a numerical value (e.g. 10), and I'd like to output the list of numbers from 1 to that number in B1:
What's the formula I need for B1? It seems trivial, and I know it probably requires ...
2
votes
2answers
364 views
Array formula with index match to return array of matches
I've been working at this for a bit. I have tried many times different formula combos, using VLOOKUP to using INDEX & MATCH, even with an ARRAYFORMULA.
What I'm attempting to do is match a ...
1
vote
1answer
52 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=...
0
votes
2answers
865 views
Make a query argument work with arrayformula in Google Sheets
Can you help please?
I have worked out how to get the result I need, but how do I make them work with arrayformula? Here is my google sheet
This works for me in Y2:
=COUNT(QUERY('Feed from Register'!...
0
votes
1answer
42 views
I need a direction for a dynamic array
I have a simple formula:
={ARRAYFORMULA(REGEXREPLACE(
IF('B-Prename5'!$B$2:$F$7<>""; 'B-Prename5'!$A$1&", "; )&
IF('C-Prename9'!$B$2:$F$7<>""; 'C-Prename9'!$A$1&", "; )&...
1
vote
1answer
23 views
How to use IF THAN statements on date but converting it to time
On B2, I'm trying to put in an IF timevalue statement on a datetimevalue statement with this formula
=IF (A2 < TIME (7,30,0), "PREM", IF (A2 >= TIME (7,30,0), "INTRA", "0"))
However it doesn'...
1
vote
2answers
58 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 ...
2
votes
1answer
5k views
Using dates as criterion for COUNTIFS in Google Sheets
So I'm currently making a recruiting data sheet for an online community. I'm trying to make it as automated as possible without actually making a bot to do it.
How can I make a COUNTIF(or similar) ...
1
vote
1answer
38 views
How do I find the sum of a specific row of values, but only for columns that contain a different specific value?
My sheet looks something like this:
I'm trying to create a formula for the Total profit column, which would be the sum of the winnings/losses for that person for that row. So for instance, for ...
2
votes
1answer
1k views
Google Sheets - Using IMPORTRANGE with a variable number of rows
Google Sheets - Using IMPORTRANGE with a variable number of rows
...is it possible to add the required number of blank rows to avoid the IMPORTRANGE's overwrite error?
Test Input: https://docs....
1
vote
0answers
20 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 ...
2
votes
1answer
135 views
How to create no pop-up Hyperlinks/Bookmarks for an array of cells within the same tab/sheet
I'm trying to create a formula that will allow all the cells in Column B to include a hyperlink to cells in Column J in their corresponding rows. The kicker is that I'm trying to keep the current ...
1
vote
1answer
57 views
How to automatically apply a hyperlink formula with relative reference to a whole column?
I have the following formula in every cell in my C column:
=IF(ISBLANK(B4),"",CONCATENATE("https://www.url.com/",B4))
If the cell in the B column has content, it takes that content and concatenates ...
0
votes
2answers
131 views
Does arrayformula help inside a Google sheets query?
Im making a query that includes a bunch of importranges and each search takes a while.
The importranges are put together using { array signs }.
If I were to use an arrayformula to make an array of ...
0
votes
0answers
20 views
(Left) Joining multiple Tables for Dashboard with several shared Conditions/Filters
Goal: Interactive dashboard with data from different sheets but for the same campaigns.
I got 3+ tables with social media Data in them. I want to combine them by unique ID. Additionally, the users ...
0
votes
0answers
23 views
Insert range using IF formula in SUMIF function with multiple criteria (using arrayformula)
I used an arrayformula with my sumif function which has several criteria and ranges to expand automatically across a set column (B). The formula below works perfectly:
=ARRAYFORMULA(SUMIF('DATA'!$N:$...
0
votes
1answer
340 views
More compact version of nested substitute function possible?
I want to make about a hundred changes in a column on a spreadsheet. These changes have to be remade each time a new copy of the original comes. The changes are spread over several thousand rows.
...
0
votes
1answer
53 views
Google Sheets Formula - Trying to Sum, based on multiple cell criteria
I am looking for help with Google Sheets formula.
The formula I need is -
If A:A=LG and F:F=Indexed then sum the total of P:P for all of those instances.
If A:A=PG and F:F=Indexed then sum the ...
1
vote
2answers
151 views
COUNTIF with relative cell range in condition
I want a Google sheets formula along the lines of
=countif(a4:a150;and(sum(e4:j4)<132;sum(e4:k4)>=132))
I.e., for each of the rows 4-150 (column A is not relevant), if the sum of columns E-J ...
1
vote
1answer
1k views
Google Sheets - Identify if one of many matches any in an array
I am trying to determine if an array of values in Google Sheets has any matches within another array of values. The data set is constantly expanding, so I want to use an Arrayformula to handle it.
...
1
vote
0answers
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;'...
