Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

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 votes
1 answer
148 views

How to combine formulatext() and arrayformula()?

I want to use formulatext() with arrayformula() : =arrayformula(formulatext(A1:A10)) But The arrayformula doesn't work. It should output 10 rows but it only returns a single row output. Is there ...
user avatar
  • 195
0 votes
0 answers
59 views

Array Forumlas and Sumifs in Google Sheets

I have a inventory spreadsheet that I am using to keep track of my inventory and I want to add a new location to track. I added the location into my forms and they populated on the spreadsheet fine. ...
user avatar
0 votes
0 answers
20 views

Sort function in Google sheets shows empty output when sorting on a column with dates

I have a form response sheet with an ArrayFormula to either show the date from a form field or, when this is left empty, the timestamp date. This column is the master datetime column I want to sort by....
user avatar
  • 1
0 votes
2 answers
39 views

Print True or False in calculated column using ARRAYFORMULA

I need to output either TRUE or FALSE in column E, based on the following criteria: If A2 = "stackexchange" then (always, regardless of other data) print TRUE in E2 (otherwise print FALSE) ...
user avatar
  • 223
0 votes
2 answers
2k views

How to use max() with arrayformula()

This is my sheet : in column E, i want to find the max among col A,B,C using this simple formula in E1 : =max(A1:C1) But rather than pasting the formula to E2 .. E10, i want to see if possible to ...
user avatar
  • 195
0 votes
1 answer
39 views

Google Sheets, use arrayformula to get average score for each classroom from a list of all students in all classrooms?

Whilst not actually referring to students and test scores, the following hypothetical is the easiest was for me to explain the question. Sheet A contains a list of all classrooms, sheet B contains a ...
user avatar
  • 23
1 vote
1 answer
184 views

Apply custom formula across all columns using Array Formula

I have trouble applying my custom formula across all columns using Array Formula. My custom formula is named getCountry where it could get the country based on an address. The address components e.g. ...
user avatar
0 votes
1 answer
113 views

Using Cell Value as a Worksheet Name in Formula

I wanted to use a cell's content as a sheet name, i.e. A1 A2 A3 A4 South North East West and use the cell values (South, North, East, West) to reference cells from sheets called South, North, East, ...
user avatar
  • 3
0 votes
1 answer
45 views

Combine data from multiple sheets to create a stacked line chart

I have multiple sheets with three columns each: Date, Invested Amt, and Return Amt. I am trying to merge the values and get a Result dataset to create a single stacked line chart of Invested vs Return ...
user avatar
0 votes
1 answer
13 views

Google Sheets pulling more data than the criteria specified

I have a tab in my Google Sheet for my raw data. I have another tab which has the report form which includes to criteria fields which I want to be able use to pull the appropriate data from the raw ...
user avatar
0 votes
1 answer
19 views

Trying to combine two (2) statements together

I am trying to pull data based off of selections made in a dropdown list, and would like to combine two (2) statements/formulas into one. Separately, they both work well and achieve what I want them ...
user avatar
0 votes
2 answers
143 views

Can't format hardkey date data as a date in Google sheets?

I've got some date data in a Google drive sheet which is imported from a CSV. The date data is formatted as either dd/mm/yy or dd.mm.yy as hard key data (eg. not formatted as a date) I got to format &...
user avatar
  • 6,143
1 vote
1 answer
80 views

Google Sheets, using VLOOKUP and ARRAYFORMULA to search multi-line cells

I'm trying to use VLOOKUP to find the batch number of a product based on its serial number. The problem is that the serial numbers for each batch are stored in one cell per batch, separated by ...
user avatar
  • 23
0 votes
2 answers
73 views

How to make variables dynamic in ARRAYFORMULA/QUERY Combination

I'm trying to self populate the first column of the "Main" sheet with a query that returns the first column of the "Finance Office" sheet that matches all three names. I came up ...
user avatar
0 votes
0 answers
35 views

What is the Google Sheet query for getting rows excluding non matching rows

The tables will have multiple entries for amount debited and credited details. It also has 5% debit and credit and 1% debit and credit entries as well. Data includes unique flag for districts, Date ...
user avatar
-1 votes
1 answer
15 views

How to apply formula to new rows that are generated by script [duplicate]

I have a spreadsheet where new data into rows is added every hour. I tried using arrayformula but couldn't figure out how. Example spreadsheet: https://docs.google.com/spreadsheets/d/...
user avatar
  • 99
0 votes
1 answer
24 views

Google Sheet: Array formula

I made this formula: ={filter(Sheet1!B1:AA22;Sheet1!B23:AA23=SAND);filter(Sheet1!B26:AA47;Sheet1!B48:AA48=SAND)} (The "=sand" is just "=true" in my language) But I keep getting &...
user avatar
  • 3
0 votes
1 answer
44 views

Google Sheets SUMIFS - apply REGEXTRACT to sum_range?

I have the following Google Sheets table and want to calculate the total sums of various medications prescribed: A B C 1 Type Amount (in g) Daily dosage (in g) 2 A 60 2 3 B 25 mg/capsule 30 pieces,...
user avatar
  • 1
0 votes
1 answer
22 views

Return different value for condition in ARRAYFORMULA

I have an ARRAYFORMULA that combines text with a number. A B 1 textA 0 2 textB 1 3 textC 2 =ARRAYFORMULA(A1:A3&" "&B1:B3) But if the value in the B column is 0, I want the ...
user avatar
0 votes
1 answer
15 views

Moving average up until every listed week

I'm trying to automatically calculate a new average every week, so I can see the evolution of the average over the past weeks. I have one column with the week numbers. The second column contains an ...
user avatar
0 votes
1 answer
14 views

Array Formula Issue

So I have ArrayFormula checking Column C Price to see what range it falls in. Then it assigns the number based on that range it puts it in Column M. It works perfectly as a formula but when I make it ...
user avatar
0 votes
1 answer
422 views

How to have a query wrapped in an arrayformula auto-populate many rows

I have a query that queries over a set of rows. I want to run this query, itself, for each row in another set of rows. The one value that changes in the query for each row is something in the where ...
user avatar
0 votes
1 answer
95 views

Can I implement this moving average with missing periods using ARRAYFORMULA?

I have these columns: A person_id B date C cupcakes_eaten D date-35 (35 days prior) There can be multiple rows with the same person_id and same date. There can be days with zero rows for a ...
user avatar
3 votes
2 answers
156 views

cannot drag query across multiple rows (overwrites data)

Is it possible to click and drag queries? I describe my problem below and also have a reference sheet. cells A1:B5 Category Withdrawal CatA 4 CatB 2 CatA 6 CatB 11 cells C1:C3 Check For CatA ...
user avatar
0 votes
1 answer
235 views

Arrayformula with large function for multiple columns in Google Sheets

I was wondering if anyone had a method for finding the second or third largest numbers in a row across multiple columns using the large function in Google Sheets using an arrayformula to account for a ...
user avatar
2 votes
1 answer
142 views

How to convert text to a number in Google sheets

I've built a form that dumps data to a Google spreadsheet and that data, studio reads it... and turns that into a dashboard.. only problem is the text doesn't convert very well for building a dash ...
user avatar
0 votes
1 answer
37 views

How to create a IF formula that searches for multiple (more than three) text options

I'm trying to create a Google Sheet to track pitches (I'm a baseball coach). I have a current sheet that I have a check box to click if the pitch was a strike or not, and then you have to choose a &...
user avatar
  • 17
1 vote
1 answer
153 views

Google Sheets: VLOOKUP formula for a multi line cells range

I have here a sample project, where columns A to C are in FORM RESPONSES TAB/SHEET and columns D and E are in RECORDS TAB/SHEET. In the RECORDS I have a list of names, in alphabetical order, and a ...
user avatar
0 votes
1 answer
23 views

attempting to compile an output List, into a single cell in one Sheet, based on matching criteria in a range on another Sheet

I found a proposed solution on Stack, but I can't get it to work. https://webapps.stackexchange.com/q/120111/270308 Starting formula: =IF(G3<>""; JOIN(CHAR(10); FILTER(ARRAYFORMULA(...
user avatar
0 votes
1 answer
15 views

How to add arrayformula function to this formula with IF function?

I made a table that has two columns (namd & State). The 4th column(check) makes conditional formatting possible for any of these combinations: (if name is either "Ali" or "John"...
user avatar
0 votes
1 answer
25 views

Formula for Google Sheet

I need to do a plan for my team. Name, time of working, and I need a formula that can assign randomly in the slot of 1 hour: chat, call break, and task. It is a team of 15 people, I can't find any ...
user avatar
  • 11
0 votes
0 answers
26 views

Max sum held over a month

I need help with a formula that needs to calculate the max sum held at one time for each month. In this example it would be 54 for March (12+13+14+15, held on the 29/03/2021). Month is determined by &...
user avatar
  • 1
1 vote
1 answer
16 views

How can I list column names with rows that only contain value

How can I list column names (in one cell) with rows that only contain value (blank not included)? If possible, separated by a line break.
user avatar
1 vote
1 answer
48 views

Create a Unique Order Number From Existing column of values

I need to create a unique ID from an existing list of order numbers. Pls see the image below: What I want is for the existing duplicate order numbers to be made unique by adding an extra digit to the ...
user avatar
  • 145
0 votes
1 answer
31 views

Sum column value if any of several criteria is met in the adjacent column

I have a sheet in which I have 2441 words (hashtags) on their own rows in the first column and in the next (adjacent) column, a number related to them (occurrences of the hashtag in our data). I want ...
user avatar
  • 3
1 vote
0 answers
36 views

Multiple columns hierarchical view

I’m trying to display my subcategories in a hierarchical view but can’t seem to figure it out with a formula rather than with pivot tables or scripts What I have: A |B |C Equity|...
user avatar
1 vote
2 answers
216 views

Complex VLOOKUP, Checkboxes and Pivots

I have a situation where room reservations can be managed by checkboxes. You can see an example in the screenshot below. I've consolidated several sheets into one to provide visualization but the ...
user avatar
  • 113
0 votes
1 answer
50 views

Bulk Search Or Find a bunch of values at once

I have a list of order numbers that I get from one sheet & cross check whether they are there in another sheet (they shouldn't be, they would be the orders that we don't need so we delete them ...
user avatar
  • 145
1 vote
1 answer
40 views

countif but not empty cells

Can I make this formula not count empty cells? ={"Score"; INDEX(if(len(A2:A), countif(if(REGEXEXTRACT(C2:BE,"(?i)yes")="yes", row(C2:C)), row(C2:C)),))}
user avatar
  • 15
1 vote
1 answer
262 views

Google Sheet split cell to rows - data manipulation

I have a sheet, where there is a list of times in Col. C. What I want is for it to be split into different rows & also the relevant order number & name to be copied with the rows. Resulting ...
user avatar
  • 145
1 vote
1 answer
84 views

Finding all word pairs from range of strings in Google Sheets

Let's say I have a column of values, each of which is a string. COL A This is a string This is longer than that My favorite is short But sometimes short is longer How is a short longer I would like to ...
user avatar
1 vote
1 answer
1k views

Google Sheet - filter non-zero or #N/A using custom formula

Say my data is in range A6:Z with the headers in row 5. I want to display rows with value in column C that is non-zero or #N/A. I tried OR(C6:C <> 0, IFNA(C6:C, true)) and OR(C6:C <> 0, ...
user avatar
  • 113
0 votes
1 answer
106 views

Running Average in google sheets

I'm new to data analytics. This might be a simple solution but I could not find it after 2 weeks of trying. I have a column of numbers that represent wins and losses. Losses are represented as "0&...
user avatar
  • 3
0 votes
1 answer
99 views

Count if contains yes using array formula for Google Form entries

I want to count every yes per submission of each Google Form, so the row. Some of the responses have comments after the yes and it won't count those. This is the formula I am using: ={"Score"...
user avatar
  • 15
2 votes
1 answer
45 views

Using ARRAYFORMULA to do two functions

I have a spreadsheet like this: 4.5 5.0 4.5 5.1 5.1 4.5 And I want to achieve a range like this: 4.5 | 3 5.0 | 1 5.1 | 2 I can achieve this with two formulas UNIQUE and COUNTIF, but - as far as I ...
user avatar
1 vote
0 answers
30 views

How can I use a dropdown menu in Google Sheets filter out data and populate cells for a an expense tracker?

I have created a personal expense tracker (budget) that uses google form responses and categorizes them by fortnights and expenditure categories. This has been successful on a sheet called 'Yearly ...
user avatar
0 votes
1 answer
55 views

is there a formula that will allow me to pull data from one google sheet tab to another based on a if contains conditional

I have a list of start dates in one tab in Google sheets. I want to pull only the start dates that reflect 2021 into a new tab, as well as pull the corresponding info for that entry from the other ...
user avatar
2 votes
1 answer
71 views

Adding together multiple Sum Filter or Sumifs with multiple conditions across multiple sheets?

So I have a budgeting spread sheet. I have an overview page that I need to link to a "Checkings" sheet and a "CC-Chase" sheet. To calculate the amount spent on a specific category ...
user avatar
  • 23
1 vote
1 answer
68 views

Combine multiple IF statement/condition into one Google Sheet column

How can I achieve in Google Sheet so that the column will auto populate the correct "vendor" based on the selection on "model" and "size". Refer below: There will be 2 ...
user avatar
  • 13
1 vote
1 answer
70 views

How to use a query to find a row of same name but last entry, then copy a value next to it (update request)

(This is an updated version of this question - I am trying my best to make myself clearer this time.) I am trying to use a query to copy values from one column to another. Both source and destination ...
user avatar
  • 137

1 2
3
4 5
15