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
3 answers
225 views

How to use counta function with two columns (or)

I would like to use counta function to count rows that are not empty based on column A or B. In other words.. if either column A or B not empty, it should be counted.
2 votes
1 answer
84 views

How to split two-line rows into separate rows

Editable spreadsheet here: https://docs.google.com/spreadsheets/d/1cHf9zC00LymZgb17J746ZUoD-YRG1UvIUQQXVlovBrE/edit?usp=sharing You can see each non-blank row contains two lines. I'm trying to find a ...
1 vote
1 answer
40 views

Calculating a total value based on X periods, that takes the previous period into account, without intermediate calculations?

I'm trying to create a simple Customer Lifetime Value calculation in Google Sheets that takes 4 variables: Income first year Retention rate (% chance of being a customer the year after) Discount rate ...
  • 115
1 vote
1 answer
88 views

Count number of rows with a specific MONTH in the date of a specific cell and a Boolean condition in another cell

Let's say I have a number of dates and whether they occupy full or half a day: DATE | WHAT | HALF? ... 2021-4-1 | Something Something | false 2021-4-2 | Something Something | false ...
  • 123
1 vote
0 answers
19 views

Adding Button to add points to another sheet - Basketball Live Scorebook

I have created a scorebook sheet in google sheet for tracking live basketball stats. One sheet with the scorebook table for the team with each player one a row. I want to have a separate tab for data ...
  • 11
2 votes
1 answer
34 views

How to shrink lists of strings so that all consecutive values are represented by "A to D" instead of as "A, B, C, D"

Quite tricky to phrase this question but I'm asking getting times ranges of when people are available in 3 hours chunks. They can answer with any combination of "00:00 to 03:00", "03:00 ...
  • 33
2 votes
1 answer
372 views

How do I apply arrayformula on a hyperlink so that I can reference another cell in a different sheet?

I have a column on sheet2 where each cell has a hyperlink redirecting to cells of a column of sheet1. I have manually gotten the "Link to this cell" and put in under hyperlink to be able to ...
2 votes
2 answers
158 views

Copy/multiply cell contents based on number in another cell

I have a list of items, Col. B & numbers in Col. A. I want to multiply/copy the text in cell B based on the number in their relative cell A. I.e. multiply 'B' by the number of times in 'A' Here's ...
  • 191
4 votes
2 answers
4k views

Check of range is blank in Google Sheets

I want to show the average of a range if any cell is populated. However, my formula shows the average only if the first cell is populated. I'm using the below formula: =IF(ARRAYFORMULA(ISBLANK(A2:C2)),...
  • 143
0 votes
1 answer
46 views

Reorganizing data submission on Google Sheets [duplicate]

I currently work at a college and we working to make the process of asking student clubs for information easier. We have created a google form for them to submit their member information, but each ...
  • 1
2 votes
2 answers
50 views

Spreadsheet formula to calculate most common items in ordered items lists

I am searching for a correct google spreadsheet formula to go through the list of ordered items and summarise the resulting winning items. Like is you have an Oscar ceremony and you get votes from ...
  • 163
1 vote
1 answer
228 views

How to filter Queried Data in an Array

Sample Spreadsheet: https://docs.google.com/spreadsheets/d/16p4M5UBODyBhmWFXYrA0FdLsA1FmwSmbrF12VUHlGaw/edit?usp=sharing I have this formula which works great: =ARRAYFORMULA(QUERY({Projects!A:C, (...
  • 155
0 votes
1 answer
94 views

Copy data based on date to another cell but other column date is not exact match

I want to match the dates in Column A to the Closest date in Column E (if a date in column A doesn't match to dates in Column E then I want to return the next available date in Column E and I've done ...
0 votes
1 answer
72 views

ArrayFormula to calculate stock position by date

I am creating a stock transaction tracking sheet where I enter buys and sells etc. I have a formula that can calculate a running position total but I cannot figure out how to convert it to an array ...
1 vote
1 answer
31 views

Trying to calculate how frequent of the shift happen in certain criteria

I work on schedule, would like to calculate how many are morning shift and how many are afternoon shift base on market. I've tried if with countifs, arrayformula with countifs, it doesn't seem to work....
  • 13
2 votes
1 answer
264 views

How do I extract specific value from a single cell on Google Sheets

I'd like to get respective data into each cell without the key and punctuations. Each cell must display only the value extracted from the properties column. I've tried to go to Data>split text to ...
  • 21
0 votes
3 answers
593 views

=IMPORTHTML forces content to date format

I am a beginner with code and formulas here, so I was wondering if anyone could do exactly what was done on this question: Trying to use Google Sheets importHTML() to import a table. It forces content ...
1 vote
1 answer
29 views

Replacing items in comma delimited cell with same string each time

I'm trying to create a formula that will replace any contents of comma delimited cell with single string. The number of items in delimited cell will vary... Specifically: 524352435,2345235523,...
  • 153
2 votes
2 answers
130 views

How do I find the max value in a column only if it contains certain texts in google sheets?

The data has numbers in text format and texts with numbers. How do I find the max of the numbers in text format without converting to numbers? How do I find the max if it contains certain texts (ie, ...
0 votes
1 answer
234 views

Is there away on google sheets to paste text and automaticly create new rows with every line break or etc?

Is there away on google sheets to paste text and automaticly create new rows with every line break or ect? I will paste a lot of text by this format in the example below (700 lines in total): ...
0 votes
1 answer
47 views

How do I include blank cells when using an Array Formula?

I have a Google form that I'm using to collect info for user account creations. I wanted to make it so that the person filling out could just let me know about multiple users at an office in one entry ...
1 vote
2 answers
72 views

Google sheets, convert value from 1 column to another based on particular data in it

Let's say I have those values in the column: w 1 w 2 w 3 I want to convert those values to 1 2 3 in another column. So basically I need to convert the value "w x" to "x". How ...
  • 13
1 vote
1 answer
565 views

How do you perform a bulk find & replace using a list of search phrases and their replacements?

This is not my wheelhouse and I'm realizing I'm in a bit over my head. Any help would be greatly appreciated. What I'm trying to accomplish https://docs.google.com/spreadsheets/d/...
  • 13
1 vote
1 answer
124 views

Search multiple columns for one value

This is roughly what my table looks like: A B C D E F Name |Ingredient 1 |Ingredient 2 |Ingredient 3 |Ingredient 4 |...
  • 191
0 votes
2 answers
287 views

Google sheets array formula for taking average every Sunday and output is in same row corresponding column

I would like to take the average using an array of every 7 days on a Sunday and place the output on the same row but corresponding column of that Sunday. If a cell in B is left blank then it should ...
  • 3
1 vote
1 answer
46 views

Is it possible arrayformula combined with many if, and, isblank?

Is it possible arrayformula combined with many if, and, isblank ?? The code below works fine =IF(AND(ISBLANK(F8)=TRUE,ISBLANK(H8)=FALSE), "Not shipped", IF((AND(ISBLANK(F8)=FALSE,ISBLANK(H8)=...
  • 13
0 votes
1 answer
90 views

Calculating monthly average from daily average (grouped by month)

I have a spreadsheet with three columns: one for dates, one for daily income and one for daily change rates. I want to have a sum of all the daily income grouped by month as seen above. For that, on ...
  • 163
0 votes
1 answer
2k views

Removing values in array without filter() in Google Sheets

I want to remove specific (or any) values in dynamic array/range and return the result of the resized array without those values without using filter() as I need to use arrayformula. For example, {&...
0 votes
1 answer
229 views

How to apply DATEVALUE to column

I have two columns with extracted date values. I want to add those two values together and apply to the whole column. The data looks like this: AM AN AO AP AQ date time ...
0 votes
1 answer
34 views

Stacking IF Statements

I am trying to pull data from another sheet based on selections made from multiple dropdown boxes. For example... If column 1 text reads RAZA pull data, if column 1 reads Standard pull data. So on and ...
1 vote
1 answer
115 views

Expand Countifs Arrayformula in rows and columns

In this google sheet I have an array of Types and floors, The criteria rows and headers are auto populated by unique formulas. I'm trying to create an array formula which will expand in the width (...
  • 13
1 vote
1 answer
2k views

Remove blanks in import range of multiple arrays

I have an importrange formula as below which references multiple ranges, calling the sheet URL's using the 'L Links'!Bx, reference, each 'book' having an identical sheet set. (There are actually 90 ...
0 votes
1 answer
129 views

Referencing a calculated value in another calculated value

Here's the general structure of my spreadsheet: Start End Count Duration Hours Count/Hour 09:00 10:00 10 01:00 1.00 10 Duration = End - Start Hours = TIMEVALUE(Duration) * 24 Count/...
1 vote
1 answer
144 views

How to create a link with content bound to other cell's content in Google sheets?

As stated in the tittle I'm trying to make the content of a cell that links to an other cell have it's display text be based on the other cell's content. Before I was able to do it by right clicking ...
  • 123
0 votes
1 answer
152 views

Using REGEXEXTRACT to retrieve specific content from two separate rows in the same column

I Included a Google Sheets link =REGEXEXTRACT(A2:A3, "19..") To me, it would seem as if this would check both cells for any date starting with 19 and extract that into the cell But, it ...
1 vote
0 answers
42 views

Array formula to extract N non blank cell in Row 1-16

I have a google form where team coaches input which players played on a given day. They also input if they were Man of the Match, Yellow/Red Cards Received, and how many Goals they scored. When the ...
0 votes
1 answer
93 views

How do I automatically match text responses to numbers and sum those numbers in Google Sheets/Forms?

I am using a google form to collect responses from students. For each student I would like to match those text responses to specific numbers. Never = 0, Almost Never = 1, etc. Then sum the results for ...
  • 3
1 vote
2 answers
88 views

In Google Sheets, analyze cell content, and if certain values are present, return a response in the next column

In the below example, I want to analyze each cell in column A, and if the cell's contents match certain parameters, return a value in column B accordingly. So if the value in a col A cell = apple, ...
0 votes
1 answer
77 views

How to assign country based off list of either provinces or states?

I have a list of names and locations. Most of the locations include the name of the province or state. Some only include country names, some include state abbreviations. I also have a list of ...
0 votes
1 answer
77 views

Google Sheet - Count Random Average

Number Of Invoice | From | To | Total 10 | $25 | $100 | ? I want the Total column to fill with the average number of 10 invoice with each invoice random between the ...
  • 11
1 vote
2 answers
71 views

G sheet How to jump over no value cells with SUMIF function

I would to get the sum of the duration of the column B +---+----------+ | A | 14:45:00 | +---+----------+ | A | #Value! | +---+----------+ | A | 13:34:50 | +---+----------+ | B | 23:41:00 | +---+-----...
  • 141
0 votes
1 answer
808 views

Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col7

I need to join two worksheets from different files in another one that already exist in Google Sheets I tried to do this: =QUERY({IMPORTRANGE("1GoKFiJjBKcGQGGi6JMvk9xD5TIypsNSmpLCTeadWQnU",&...
1 vote
2 answers
1k views

Restructure (flatten) data of Google Sheets table in a new table

Column A of my Google Spreadsheet contains dates, columns B to G contain numeric values, line 1 contains the header names. Due to automatic updates the line count varies, but the column count is ...
  • 13
1 vote
2 answers
825 views

Extracting text from a capture group while using an arrayformula to consider multiple criteria

I have a page which contains a list of regular expressions in the format "some text (thing I want) but only if (thing is matched here)" that are fairly varied. There are 29 of them so far, ...
  • 111
2 votes
2 answers
405 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/...
0 votes
1 answer
45 views

Average of cell range from string

In Google Sheets I have a string like this: "G4;G15;G26;G37;G48". How do I get the average of the values in those cells? I'm sure there's some arrayformula and indirect to be used, but I can'...
2 votes
1 answer
526 views

Average the last N values in a Sheets column using Arrayformula

Experts, I need your help... I'm looking to create a formula (not necessarily an arrayformula), that will automatically calculate an average. This sounds easy, but surprisingly, it isn't. Let me ...
  • 21
0 votes
1 answer
33 views

Show image if rank equals number

I have a row of 5 cells. I want to show an image above each cell depending on the rank of the cell. So if the rank of the cell equals 1, show a specific image (from a link), if the rank is 2, show a ...
0 votes
1 answer
47 views

Create Columns via Formula for two dates

How do you do this with columns instead of rows? =ARRAYFORMULA(TEXT(ROW(INDIRECT("A"&DATEVALUE("2018-4-13")& ":A"&DATEVALUE(&...
  • 1
1 vote
0 answers
231 views

How do I calculate subtotals by using ArrayFormula and without formula in the cell itself in a Google Sheet

I'm working on an estimate sheet, got it all worked out pretty neatly and trying to tackle the formulae, make them work from the header rows using ArrayFormula instead of having each cell holding ...
  • 111

1
3 4
5
6 7
16