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
1
vote
1answer
17 views

Google Sheets forumla help

With my sheet, I need it so that if the job is any type of "Repair" then it minuses 1000 from the business profit but if it's anything else it doesn't minus the 1000 from the business profit. I have ...
0
votes
1answer
113 views

How to merge data from multiple Google Sheets with formula?

I have 10 columns in 10 Google sheets and in all sheets are the data labeled like Month | Total number of winter/summer schools attended | Sr. No. | Title | Date | Venue | Organisation | Number of ...
1
vote
1answer
34 views

How to Update a exactly sheet tab row with a value from a imported data in another tab in Google Sheets?

I have this tab in Google spreadsheet [https://docs.google.com/spreadsheets/d/14ZBff8R6brLYWtfv70V-tyG0HE-sX_ejySvplQqZftM/edit#gid=1450402909] Sheet All Tasks, Candice Tasks, Others People Tasks ...
1
vote
1answer
14 views

Rank days of the month that occur most

I would like to know which days of the month customers have historically paid on the most. I would like to create a report that has days 1,2,3,4...31 all with a count next to each day for how many ...
2
votes
1answer
24 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,...
1
vote
1answer
30 views

Is there any way to dynamically modify dates based on number of days something would take?

Imagine you're creating a Google Sheet to track all of your tasks, how long they take, and their deadlines: Task |Number of days| Start Date | Date Done Buy plane tickets | ...
1
vote
1answer
19 views

How to get an array for a range per a column in Google Sheets?

I'm looking for creating a similar formula: =UNIQUE({A:A;B:B;C:C}) So, I need such kind of a range {A2:A;B2:B;C2:C;...} (each column separated with the semicolon) for several hundred columns. Seem, ...
1
vote
1answer
47 views

ImportXML With Line Breaks Google Sheets

I am using ImportXML to get some data from a website which looks like the following: =ImportXML("https://www.papercall.io/events?cfps-scope=&keywords=uk","//div[@class='panel panel-default']") ...
1
vote
1answer
67 views

GOOGLEFINANCE in Google Sheets to get high & low and timestamp of high/low

I'm using Google Finance calls in Google Sheets for some calculations. I've got 2 formulas, one to find the low price, and one to find the high price from a certain date: =MIN(INDEX(GOOGLEFINANCE("...
1
vote
1answer
36 views

Checking whether two columns contain the same string of numbers

Some time ago I have put together a perfectly working formula but now it's not working and I can't tell why (I may have changed something and don't remember that). Column B contains data that I need ...
1
vote
1answer
25 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.
1
vote
1answer
32 views

Query sum for unique pairs of rows

I have a spreadsheet which logs the duration an employee spends in a city. An employee can visit a city multiple times like Peter in this example. City Person Days London Tom ...
1
vote
1answer
48 views

ArrayFormula to extract part of the string

I have come across the below formula, and I am finding it quite useful, I want to turn it into an array shortly, but before I do, I was wondering if there was an easier way to do this? =if(len(LEFT(...
1
vote
2answers
613 views

Combining IMPORTRANGE, SUMIFS, QUERY with cells that contain numbers and text

I'm having an issue combining SUMIFS with ImportRange in Google Sheets. I found answers suggesting to nest it with QUERY. However, the issue is that one of my criteria is non-numerical. Formula in ...
17
votes
9answers
101k 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
1answer
21 views

Array formula for adding letters after duplicate numbers in order

The below formula adds a letter after a sequential number, A, B, C, D, etc for the same number until it finds a new number, then it starts again from A. I need help on turning this into an array ...
1
vote
1answer
426 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 ...
4
votes
4answers
2k views

Auto-fill date function changes the YEAR and not the day

I'm trying to get Google Sheets to auto-fill dates in the beneath cells after I have manually filled one myself. The strange issue is that when I hold Command key and drag down, the cells auto-...
11
votes
4answers
2k views

Average of last 7 non-empty non-blank cells in Google Sheets

I have numbers in range D7:D and there are times when some cells are empty. I want the average of the last 7 numbers but to skip blanks. So if in the last 7 there's only 3 that are full, I want it to ...
1
vote
1answer
11 views

How to create new cell from keyword of another cell?

I have a column that has job listings with extra info, so I only want to grab the job position and place it into a new cell. A1: Seeking Assistant to the marketing division... B1: Assistant (...
1
vote
1answer
28 views

How to extract data from a table dynamically?

I have the following table And I would like a formula that would produce the following result I would like to iterate through the table going from left to right top to bottom and if there is a ...
1
vote
1answer
33 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 ...
1
vote
1answer
29 views

How to search value in another column?

Using Google Sheets, I am trying to two compare two columns on Google Sheets, like the one below: Basically, I want to compare column A and B, basically, if a cell in column A is inside a cell in ...
1
vote
1answer
19 views

How can I return a string instead of a number when the number is 0

I have a formula that returns average lbs, waist and body fat % for the weeks I've been dieting. The problem is I didn't measure body fat % until a while after I started so some averages are 0. I'd ...
0
votes
3answers
2k views

Numeric before query but query results are not a numbers

I have a column of values, let's say column A, that if I do =isnumber on any of the column data it returns TRUE. Now after I =query and select A, the query gives me the right values, but the type ...
1
vote
1answer
60 views

Convert cell containing days of the week in first-letter format to day names in Google Sheets

I created a schedule of events in Google Sheets. I have a cell that contains MWF, indicating that the event is every Monday, Wednesday, and Friday. I have a different cell that reports the word ...
9
votes
5answers
37k views

Count the number of words in a string in Google Sheets

I have a simple Google spreadsheet with a number of text strings. I just want the number of words contained in each cell. Is there an in-built Google spreadsheet function I may use?
1
vote
1answer
77 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
2answers
1k views

How to concatenate columns for multiple rows simultaneously in Google Sheets?

I have the following 3 rows of data in cells A1:A3 in Google Sheets: Doe, Jane Doe, Joe John Doe I want the following in B1:B3: Jane Doe Joe Doe John Doe Essentially, I'm creating a new column ...
1
vote
1answer
36 views

How to programmatically drag new rows - running total?

I have the first few rows of my mortgage payments set up, and I want to know how to programmatically print the rest of the rows, as opposed to dragging the little corner box to output the rows. This ...
0
votes
1answer
21 views

How to convert an array of unique records into an array where records are repeated N times?

I have a Google sheet that records items and their quantities. In order to input them into our SalesForce system, I need to split each record into repeated rows, according to the quantity of the item. ...
1
vote
2answers
37 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
vote
1answer
21 views

Split data of a row in multiple rows

I've got some problem with data coming from a Google form. The result are: A|A|A|A|B|B|B|B|C|C|C|C| What I want to get is: A|A|A|A| B|B|B|B| C|C|C|C| Is there a way to get a similar result?
2
votes
1answer
16 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 ...
1
vote
1answer
19 views

Pull image or skip to next cell if empty

I am currently using Google Sheets to make a list of videos and thumbnails. I have various sheets titled: Frying, Stewing, Baking, etc. Within each sheet, there are only two columns which include a ...
0
votes
1answer
20 views

Pull data when cell value is selected

I am Trying to pull data from part of a sheet to another part depending on if dirt level 22 value is filled in. Please see attached Picture, basically, the bottom section pulls data from rows above ...
1
vote
1answer
41 views

Skip arrayformula if the result of that formula is blank

I have an array formula that looks like this: =ArrayFormula(IF(COUNTIF(B2:B, QUERY('Form Responses'!B2:G, "select G where B != ''"))=0, IFERROR(QUERY('Form Responses'!B2:G, "select B, G where B != ''"...
2
votes
2answers
6k views

COUNTIFS with multiple OR

I have two sheets, one is a dump sheet of values like below: and the second sheet is using values from this to the total. I am trying to get the total count for the corresponding zone and test name ...
1
vote
1answer
25 views

Conversion from LibreOffice to Google Sheets

I use LibreOffice to create xlsx files. There are some formulas to test conditions. Everything is ok under LibreOffice but when I open the file with Google Sheets formulas give the wrong result. e.g....
1
vote
1answer
619 views

In ARRAY_LITERAL, an Array Literal was missing values for one or more rows

I've been trying to get this array formula to work, but I get an error. It's just a vlookup array to display multiple columns. It works fine without the {""; bit, but I want it to drop down one row. =...
0
votes
1answer
61 views

Replacing data in Google Sheets

I am new to coding! I have data in Sheet 1 of a Larger Spreadsheet that looks like this 25.06.18 and wish to change it to look like 26/06/18 I have tried to write a macro: function Dated() { ...
1
vote
1answer
19 views

Outputing Array Formula in one row

I came across the below array formula, which works really well, I'm just struggling to tweak it, and barely understand it's inner workings, but it's very good. Instead of displaying a total of stock ...
1
vote
1answer
25 views

Solve errors in a series of FILTER functions within array brackets?

I have been working on a budget project on Google Sheets. My goal is for a range of data to be filtered by date (which is represented as an integer between 1 and 31 for the day of the month). All ...
2
votes
1answer
30 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
1answer
75 views

Array Formula with IF function and SUMIFS function

I am trying to convert the below formula into an array formula. I can make it work in an array, but it displays the wrong values, compared to the normal formula which I have to drag down. Normal ...
1
vote
1answer
25 views

Count unique values in second column corresponding to unique value in first column

I have a Google sheet with cities corresponding to countries like this. Notice that neither countries nor cities are unique and there can be duplicates. | A | B | ----------------------...
2
votes
2answers
1k views

Appending text to each column in Google Sheets

I have a Google Sheets that has about 200 columns. Each one is a name, and to each one, I want to append -banner.jpg. For example: abcde fghij klmno will all be updated to: abcde-banner.jpg fghij-...
-1
votes
1answer
24 views

How do I find latest invoice number & dates?

Trying to find a formula for the latest date and invoice number. Here is my sheet: https://docs.google.com/spreadsheets/d/1T_rFYDCQhqfrsU9claYPHQTrEz68zpUFKG9HZqqV4Z8/edit?usp=sharing
1
vote
1answer
23 views

How to refer to elements of a dynamic array in Google Sheets?

I'm trying to create teaching timetables dynamically, by searching through a large table with all of the information about what time a lesson takes place, who the tutor is, what age group is being ...
1
vote
1answer
14 views

ArrayFormula not showing 0s

I use the following formula to calculate plain text so to show leading zeros. =ARRAYFORMULA(SUM(S2:S17+0)) but it's only displaying the results without the leading zero. Example, if It is adding up ...