The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
1answer
43 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
127 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
33 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
89 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
77 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
570 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
27 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 ...
2
votes
1answer
765 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
2answers
567 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
23 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 ...
1
vote
4answers
425 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
3answers
4k views

Convert Text String into Date/Time Value

I'm fetching data into a Google Sheet. The date column contains multiple cells with the following text string pattern: Mon Mar 11 19:05:32 +0000 2019 The pattern always repeat itself: [day] [month] ...
1
vote
2answers
40 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
3k 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
3answers
9k views

Google Sheets QUERY won't display cell text if other cells have numbers

I'm not sure what is happening in my Google Sheet query and could really use some help. I have a quick screencast that demonstrates and explains the issue. Basically, I'm using a query to pull data ...
1
vote
2answers
28 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
3answers
147 views

SUM columns from other sheets and group by month, but also show the individual sheets values before doing SUM

Currently, I have a spreadsheet which has a number of customer sheets, each with a pivot table showing the month in the row I and the total income for each month in row L. On a separate sheet I have ...
1
vote
3answers
38 views

Summing up amounts if one row contains a key within another sheet

I have two sheets in one Google Sheets document: | Key | Amount | |-----|---------| | ABC | 1 | | DEF | 2 | | GHI | 3 | | Keys | |-------| | ABC | | XYZ | | JKL | | GHI | ...
1
vote
1answer
20 views

How to calculate the following mathematical expression in Google Sheets?

I am trying to calculate the total money that can be made from dollar cost averaging every year using Google Sheets. For example: how would I evaluate this?
1
vote
2answers
17 views

Use Google Sheets to create column C by deleting any phrases that contain word found in column B in column A

I have a list in column A and I want to remove any cells that contain any word.
1
vote
2answers
146 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 ...
1
vote
1answer
18 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
2answers
141 views

How to get a Sample Standard Deviation values that match condition?

I am trying to get the standard deviation of a subset of data. I have two sheets Name Value a 1 a 2 a 1 b 5 b 5 In a separate sheet I have: Name StdDev a b GOAL: I am trying ...
1
vote
1answer
89 views

How to return all values (horizontal array) in B that has the same key stored in A?

Sample Sheet 1: A B 1 a x 2 a y 3 a z 4 b i 5 b h 6 b w Formula result, in Sheet 2, I am looking for: A B C D 1 a x y z 2 b i h w
1
vote
2answers
115 views

Conditional formatting rule that compares against a range of values

I have a list of 2019 dates (col A) and holiday dates (col G). Wherever there's a date in col A that matches any date in the range G2:G20, I would like to highlight it in grey. See this sample ...
1
vote
2answers
809 views

Google Sheets Query with Date and Time

I need help with a query. I need to filter on some Google Form data and only show the results that have been submitted AFTER a specific timestamp. Timestamp Cell = J2 - This cell is in the following ...
1
vote
1answer
131 views

Consecutive numbering skiping empty cells with ArrayFormula

I am trying to auto-number only the existing names on a cell using an arrayformula: I have two cells one for the names and the other for consecutive numbers. Here I have an example of what I want to ...
1
vote
1answer
2k views

Using SUMPRODUCT with ARRAYFORMULA

I have the following random data in sheet one A orange D yellow C yellow B orange B orange C blue A yellow D blue C orange C blue In sheet two I use the following query function: A1=...
1
vote
1answer
1k views

ArrayFormula to compute running average for groups of rows

Is it possible to write an array formula that calculates the running average of the Amount column for each of the Name groups in the following sheet? The rows are sorted by Name. In Column C, I have ...
1
vote
2answers
37 views

Can I use arrayformula in Google Sheets to count things over a variable range?

Is it possible to create a single arrayformula that does the same thing as the following column of formula: A1: =COUNTA(B1:B1) A2: =COUNTA(B1:B2) A3: =COUNTA(B1:B3) ... A9: =COUNTA(B1:B9) My best ...
1
vote
1answer
64 views

Adding missing dates to Google Sheets

I'm pulling data from HubSpot to Google Sheets via 3rd party connector. I have a raw data set on Sheet 1 and then I'm pulling certain data from Sheet 1 to other Sheets with QUERY function. For ...
1
vote
1answer
20 views

Output counted list with extra qualifier

I currently have a table with the following columns: Comm ID Item Name Months Orichalcum Tempest Jade Green Iron Weltsilver Ambergelt Beggar's Lye Dragonbone Iridescent Gloaming ...
1
vote
1answer
57 views

How to automatically fill missing data in Google Spreadsheet

I'm searching for the exact same thing as Google Spreadsheet: How to fill in missing values (inbetween filled values) automatically I made a spreadsheet for tracking weight, where each row is one day,...
1
vote
2answers
53 views

Remove Count column by repeating rows in Google Sheets

I have a worksheet, 'wi', in a Google Sheet similar to the following example but with an undetermined number of rows: Count | Entry | Description -------|-------|----------------- 2 | ...
1
vote
1answer
23 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 ...
1
vote
1answer
29 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
33 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
34 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
30 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
44 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
1answer
20 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
255 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 ...
1
vote
1answer
43 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 ...
1
vote
1answer
22 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
285 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. =...
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
34 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 ...
1
vote
1answer
19 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 | ----------------------...
1
vote
1answer
17 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 ...