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
2 votes
2 answers
2k views

Change default values when Google Forms autofills a Google Sheets

There's a Google form that's linked with a Google sheet: whenever a response is posted to the forme, a new line is added to the sheet (here's a quick tutorial just in case). Is it possible to have ...
2 votes
1 answer
51 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 | ...
3 votes
1 answer
117 views

COUNTIF with Multiplicities

I am currently creating a histogram on Google Sheets to count how many of x-product in our inventory we have priced at less than $10k, between $10k to $20k, $20k to $30k, etc. The problem is that ...
2 votes
1 answer
105 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 ...
2 votes
1 answer
324 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 ...
1 vote
1 answer
82 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
1 answer
2k views

Find previous non-blank cell, but without a specified range in Google Sheets

I've come across plenty of formulas to find blank cells within a certain specified range, but can't seem to figure this out / find an answer. I have a spreadsheet kind of like this: . Where column ...
1 vote
1 answer
243 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
1 answer
772 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.
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 ...
0 votes
1 answer
645 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 ...
-2 votes
1 answer
45 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
1 answer
137 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
1 answer
364 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
1 answer
156 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 ...
1 vote
1 answer
3k views

How to generate a range of serial numbers by formula?

I want to produce an array of numbers from 1 to n, where n is a value in another cell. So if the cell value is 10, I want to generate an array of the numbers 1 to 10. I found this resource which ...
2 votes
4 answers
7k views

How to check if the 1st letter in a cell is UPPERCASE

I'm trying to find out if the 1st letter in a cell is UPPERCASE. I know how to convert case using PROPER(), UPPER(), and LOWER(), but not how to detect case. The following checks every word in the ...
1 vote
0 answers
87 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
1 answer
72 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 ...
-1 votes
1 answer
148 views

Convert string to URL in column

I just want to convert IDs as strings to URLs automatically. I tried this: =HYPERLINK(CONCATENATE("http://myjira.com/projects/BUWDI/issues/";A2);A2) I got an error... What do you think?
0 votes
0 answers
325 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 ...
2 votes
1 answer
2k 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 ...
4 votes
1 answer
2k 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
1 answer
37 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
2 answers
1k views

Sum a column at the bottom of it

In Google Sheets, how do I get - at the bottom-most cell of a column - the sum of each cell (except for row 1, the header) above in that column? Keep in mind, I may add rows in the future.
2 votes
0 answers
194 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 ...
0 votes
5 answers
78 views

Layperson's grid to normalized table

Introduction  (too long, don't read) I have a data-arrangement problem. I'm now up to three qualifying files from coworkers, and for that matter, I've run into a couple of questions here on SE ...
2 votes
1 answer
19 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
0 answers
231 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
1 answer
635 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 ...
3 votes
2 answers
334 views

Google Sheets Array Generation

Is it possible to generate an array of numbers between two provided numbers eg =sequence(1,10) results in {1,2,3,4,5,6,7,8,9,10} My end goals is to generate a list of days between two dates, but have ...
1 vote
4 answers
16k views

How Can I Sort a Data Range in Google Sheets Based on an Data Range?

I have been looking for a way to sort this information. I need to sort these items in this order: BK, BB, BV, BIV, BIII, BII, BR. I was trying to make a Google Script to get it, but I could only ...
0 votes
1 answer
45 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&", "; )&...
37 votes
9 answers
265k views

How to Fill a Column with Sequential Dates in Google Sheets

In Excel, you can right-click drag a date downwards to fill a column with the next dates in the sequence. For example, if you enter 9/1/2014, select that cell, then right-click drag down over the ...
1 vote
1 answer
32 views

How to summary the multiple entry into a single sheet by name and by date?

Names are in rows and Dates are in columns. Sheet 1: Name 1-Jun-18 2-Jun-18 3-Jun-18....... KB CL KB EL AJ SL SD SL SD CL I need ...
1 vote
1 answer
28 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
1 answer
4k views

QUERY with Week Number

I use query to extract two columns from another sheet with data. Column A has dates Column B - numbers I want to extract only rows that correspond to a specific calendar week number. For example 19. ...
2 votes
2 answers
3k views

How can I fill the diagonal of a matrix with zeros?

I have an empty matrix: I would like to fill the diagonal of a matrix with zeros: Is there any clever way to do it? The matrix can be pretty large and it's tedious to do this manually. I could use ...
1 vote
1 answer
264 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 ...
3 votes
1 answer
2k 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
0 answers
36 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 ...
0 votes
2 answers
209 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
0 answers
31 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
0 answers
35 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:$...
2 votes
2 answers
855 views

Boolean test of whether a time is before or after midnight in Google Sheets?

I'm having a job trying to create a Google sheet which calculates the difference between the time I fell asleep and the time I woke up. Here's what I want to happen: Sleep time Wake time ...
1 vote
1 answer
848 views

Delete Google Sheet rows matching multiple criteria with custom formula

I have a Google sheet where I am trying to remove multiple rows matching any one of a vector of values using (Filter by condition -> Custom formula). For this example, let's say I want to remove ...
0 votes
1 answer
262 views

Look up a list embedded in a Google Sheets from a Google Forms, and add attributes to the corresponding row?

I'm running an event for a large number of children and need a way to let them register quickly as they come in, to the event. We're expecting about 400 kids who have pre-enrolled and we will have a ...
0 votes
1 answer
735 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
1 answer
76 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
2 answers
319 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
8 9
10
11 12
17