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

How to sum all positive values in Google Sheets based on whether the associated data in another column is unique

I have a spreadsheet that my company is trying to transfer into Google Sheets from Excel. I've used an array formula to accomplish this result in Excel, but it's not translating to Sheets and I know ...
2
votes
1answer
111 views

Arrayformula using cells as parameters

I would like to use the ARRAYFORMULA combined with two cells as range. For example: Cell A1 value: A3 Cell A2 value: B7 Resulting formula: ARRAYFORMULA(A3:B7) Is it possible? How can I do?
2
votes
2answers
49 views

Calculate Total DateDif Average Between 2 Columns (start, end) according to year expressed in days, hours, minutes

I'm having trouble getting a formula to work to calculate the total average time between times (start and end). Sheet: https://docs.google.com/spreadsheets/d/...
2
votes
1answer
21 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 | ...
2
votes
1answer
42 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 ...
2
votes
1answer
51 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
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/...
2
votes
1answer
70 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 ...
2
votes
1answer
30 views

If “text” then, how to put a conditional formula

There are three columns. A, B and results. I need to make a formula that gives the following: If the text in both columns A and B is WIN, then in the results we should see: Winner. If the text in ...
2
votes
1answer
46 views

How to automatically calculate averages from ID in Google Sheet?

I've been looking around for an answer but couldn't find what I was looking for. The problem is that I want to use an arrayformula, but can't figure out a dynamic formula that: Looks at the unique ...
2
votes
1answer
78 views

ArrayFormula to create a table of data from 1 column

I have the below formula and want to turn it into an array. =INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1)) I have tried it with the below attempt but only get 1 single cell result. =arrayformula(INDEX($A:$A,...
2
votes
2answers
124 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
2answers
2k views

Escape single quote in cell content in a QUERY

My Problem I have a Google Spreadsheet with two tabs - one containing regexes and values: And the other containing phrases which matches regex in the first tab, and a QUERY Formula that picks the ...
2
votes
1answer
51 views

How to aggregate data from multiple Google sheets with a tag

I am importing data (using IMPORTRANGE) from 3 different sheets to 3 different tabs of a google sheet. Then I am aggregating those three sheets into one and also filter them using a query. Ex: =...
2
votes
1answer
1k views

Google Finance Historical Data with Multiple Attributes?

=GOOGLEFINANCE("NSE:"&A2, "all",(TODAY()-($N$2)),TODAY(),"DAILY") The above code returns OHLC data for specified days with the date column. What I want are only high, low and close columns. =...
2
votes
2answers
948 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
2answers
58 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 ...
2
votes
2answers
79 views

Processing data from multiple cells and comparing results with another cell

For most orders, I have multiple deliveries under the same recipient ID and I need to compare whether everything ordered is really delivered. All products are divided by a comma and have always the ...
2
votes
2answers
530 views

ARRAYFORMULA with IF, DETECTLANGUAGE and LEN

I'm trying to make an array formula to detect the number of English characters in a range. When I use the following for individual cells: =IF(DETECTLANGUAGE(ARRAYFORMULA(A5:A))="en",LEN(ARRAYFORMULA(...
2
votes
1answer
545 views

Remove the false operator in Google Sheets

Here I am trying to add a random hexadecimal value every time a name is entered. It’s working but the other empty cells get filled with false. I want to completely delete the false part as it’s ...
2
votes
1answer
2k views

Aggregate results by week and month for charts

Is there a way to convert daily values into weekly averages for charting? Sample source sheet --------------------------------------------------- | Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc......
2
votes
1answer
1k views

Does the ArrayFormula formula work with Concatenate?

I am trying to make an ArrayFormula that see if column D is empty or not If it is not empty it will then take a number and concatenate to generate an url to show an img from a URL. My current ...
2
votes
1answer
428 views

Array formula with running total that restarts based on values in another column

I have tried to create a running total with some kind of filter as it were and have been unable to figure out how to do it. I have seen people do a running total using MMULT but not sure how to adapt ...
2
votes
1answer
31 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 ...
2
votes
0answers
112 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
2answers
50 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
333 views

Convert ARRAYFORMULA output from 2D to 1D range

tl;dr How to create an 1D range from a 2D range output of an ARRAYFORMULA. Intro I have a Google Sheet that uses a very complex ARRAYFORMULA to perform data validation on about fift cells in a row. ...
2
votes
1answer
28 views

Dynamically creating column names based on data in another tab

I have a Google sheet that logs all submissions on my website of various contact forms. I need to know how many times each contact form has been submitted, and in which week they were submitted. The ...
2
votes
2answers
61 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 ...
2
votes
1answer
52 views

Google Sheets sort question

I used to know this a few years ago and for the life of me can't figure it out and can't find an answer in Google help. I know how to sort columns, but only by the default A-Z format, how can I sort ...
2
votes
1answer
52 views

Comparing Unique Values to a Range

I have the following formula =CONCATENATE (FILTER(boxmod!AC2:AC, boxmod!I2:AB=concatboxmod!C2 )) The intention of this formula is to take a range and compare it to a list of unique identifiers on ...
2
votes
1answer
2k views

How can I track the time it takes for someone to fill out a Google form?

I am running a study for my thesis, and would very much like to track the time it takes to complete a Google form to compare against how long it takes to fill.
2
votes
1answer
82 views

How to SUMIFS text beginning with apostrophe

I am receiving a list of products from an external data source. One of the product names begins with an apostrophe. When I attempt to produce a summary of the products and total quantity using UNIQUE()...
2
votes
2answers
4k 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 ...
2
votes
1answer
66 views

Sum value in cells that contain both number and text

I need to sum a range of cells with text in them. Example: a1= "25,00 market" b1="24,00 credit card" c1="75,11 food" a2= "10,00 sweets" b2="9,00 market" c2="24,00 keyboard" I want to sum ...
2
votes
3answers
367 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,", ")...
2
votes
1answer
2k views

Get URL from web page using IMPORTXML

Though I am new to web scraping, but I have a hand on experience of web scraping using VBA (Excel) I am facing the following problem: I have tried all methods (ImportXML, ImportHTMP, ImportData, etc.)...
2
votes
1answer
34 views

Find cell that contains some phrase

I'm looking for replacement of Excel formula: =if.error(lookup(1000;search($B$1:$B$2;A1);$B$1:$B$2); "NONE") The sheet looks like this: So in column A, I have a long product index, in column B, I ...
2
votes
1answer
437 views

How to autofill value on Google Sheets

I have the following example here. I have a random value that is no spaced exactly, (some have 10 empty between them other 30). I would like to have some value equally added in the empty spaces. I ...
2
votes
1answer
129 views

Splitting numbers of a cell and adding them together in an ArrayFormula on Google Sheets

My question is how can I have digits of a cell added together in an array formula format, in that 12 | -> 3 //(result: 3 = 1 + 2) 14 | -> 5 46 | -> 10 11 | -> 2 53 | -> 8 And let’...
2
votes
1answer
998 views

SUM a range of cells (sometimes string, sometimes numbers), parsing the cell if it is a string

I am trying to get the sum of a column of cells, which sometimes contains numbers and sometimes contains a string with numbers in it. I've found that the formula =arrayformula(sum(0+iferror(...
2
votes
1answer
290 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 ...
2
votes
2answers
200 views

Google Sheets' Duration Input issue

We have been using Apple Numbers which seems to have this duration thing figured out a little better than Google, (either that or I'm missing something). In Apple Numbers, if I type into a cell with a ...
1
vote
4answers
856 views

multiple IF statements with between number ranges alternative

How to efectively deal with multiple IF-statements and with multiple "between number" ranges in Google Sheets' spreadsheet (e.g. C column based on B column) with a rule set as follows: 01-10 11-20 ...
1
vote
1answer
3k views

How to use custom function with arrayformula?

I'd like to use a Google Apps Script custom function in conjunction with arrayFormula so this may be applied to a range. How do I achieve this? Let's say I have wrote a function that will return ...
1
vote
4answers
2k 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
2answers
49 views

How do I add dates for the beginning and end of the week in one cell

I know very little about Google Sheets and all I want to do is create a cell that will display the beginning and end of the week, like this: 7/22-7/28. I'd like to be able to drag this across ...
1
vote
2answers
7k views

Google Sheets Arrayformula on both horizontal & vertical cells?

In a sheet, I make calculations horizontally, however I can't build the arrayformula to expand calculation vertically on each row. See the following sheet: https://docs.google.com/spreadsheets/d/...
1
vote
1answer
2k views

How to block automatic row and column insertion in Google Sheets?

While playing around with array formulas, "sometimes" when the result has more rows and columns than the previously available in the sheet, Google Sheets automatically adds the row needed. I said "...
1
vote
2answers
45 views

Google Sheets IF and 2 x ANDS

I have three columns in my google sheet. COL A - will hold an invoice number (text field) or be empty COL B - will display the word EXEPNSE COL C - this will be my formula column I need to detect if ...

1
2
3 4 5
12