Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [google-sheets-arrayformula]

Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.

2
votes
1answer
17 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 ...
0
votes
2answers
10 views

Combining ARRAYFORMULA with functions whose arguments can be rectangular ranges

I mean to concatenate several columns, so use of CONCATENATE is preferred over & (here I post an example with only 3 columns). For instance, D1 would contain =CONCATENATE(A1:C1). Then I would copy ...
1
vote
1answer
18 views

Conditional IMPORTRANGE

In Google Sheets, I'm using IMPORTRANGE to pull in data from to a large sheet. One field is a dollar amount. I would like to import the dollar amount and replace any zero amount with a specific ...
2
votes
1answer
24 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 ...
1
vote
1answer
20 views

Formula for combining text if there are duplicate numbers

I have a table to combine text with conditions if in column A there is a duplicate number, then in the text in column B combined. The formula that I have made like this: =IF(COUNTIF($A:$A,A2)>1,...
1
vote
1answer
9 views

How to SUMIF depending on another column's value and REGEXEXTRACT

I'm trying to use SUMIF and REGEXEXTRACT, i want to get a total sum of the description only where paid out is not blank and the description value needs to be multiplied by value in paid out so on the ...
1
vote
3answers
48 views
1
vote
1answer
22 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
0
votes
1answer
32 views

Getting data from another Google sheet that isn't in the same order

I have a very basic knowledge of deeper functions of Google sheets and solving this has been a little bit of pain in the butt so far. I have a spreadsheet A column A contains ID column I that has ...
2
votes
2answers
37 views

Query returning #VALUE error when the column has no values

The below query returns #VALUE error when the column H is completely empty: =QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, ' ', ' ', ' ', G, ' ', ' ', ' ', '"&...
1
vote
1answer
22 views

Creating running total chart

I want to create a chart in Google Sheets with cumulative Profit (H) column but without creating the second column (I). Are there any solutions?
1
vote
2answers
21 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 ...
0
votes
2answers
20 views

How to scan rows for match with ArrayFormula?

Trying to search a row for a given value, but want to make an ARRAYFORMULA so that every time a new row is added the search will be performed on that row. Currently using =IF(COUNTIF($A2:$EI2,"="&...
1
vote
1answer
26 views

JOIN two columns in one cell

What should I do if I want every time I fill out column C, to all data from B6 to C14 would appear on the notes?
1
vote
1answer
14 views

Combing two ranges with identical column structure from two different sheets (in same spreadsheet)

I'm doing a simple ticketing system, where I have two sheets, "Form Responses" and "Email Responses" both of which are populated automatically as their names imply (one from a form, one from an email ...
1
vote
1answer
9 views

How to combine dynamic columns in Google Sheets while retaining data in other rows?

I have a Google Form that asks people to fill out their name, gender, and then their size preference for shirts and pants. The form automatically outputs the 2 sizes in separate columns. I want to ...
1
vote
1answer
18 views

Creating a parent ID cell in Google Sheets

I've got a spreadsheet of various categories and I'd like to create a column which gets the parent name of that particular category. They are currently broken down as follows: 01.01 Category 1 01.02 ...
1
vote
1answer
23 views

Pie chart with sub-slices per category in Google Sheets?

I have a sheet that contains a list of books, with metadata about those books. Two columns that exist are the library within which the book is stored, and the condition of the book. I'd like to create ...
0
votes
4answers
24 views

How to pull the page number and the number of images listed from one sheet and then populate cells in another sheet accordingly?

Sorry, I didn't know how to summarize it in the title. Basically, I have a spreadsheet where I'm keeping track of the number of images that need to be on each page of a document I'm working on. On ...
1
vote
2answers
24 views

Query using date range doesn't work for specific data

I have a spreadsheet (URL below). I've set up a query to make sure I understand how to do a query with a date range. Then I tried to create a similar query on the "real" data. The "Week 1" worksheet ...
1
vote
2answers
60 views

Cartesian Product with constraints

I have a really big database that I have to sort and divide as the example below: F1 F2 F3 C1 C2 C3 C4 results P1 X X X X X P1 ...
0
votes
1answer
14 views

Number in a cell linked with a text

I would like to ask if there is a solution to my problem: I have 200 numbers from 1 to 200 which are linked with text. For example: 1 = car, 2 = bicycle and so on. I would like to write a number (1) ...
1
vote
1answer
14 views

Apply a formula to each cell in column before computing a sum of the cells

How could I apply a formula to each cell in a column before computing a sum of the cells in Google spreadsheet? In other words, I have a column of cells with numbers. And what I want is to iterate ...
1
vote
1answer
12 views

How to concatenate cells using ARRAYFORMULA across rows and columns?

I have a document that's a recurring CSV import/export. I'm trying to concatenate images separated by ", " and using ARRAYFORMULA to make it all the way down. The problem is, I don't know how many ...
1
vote
1answer
18 views

Populating alternatives in question 2 based on answers in question 1

I have ten people who sell something to companies, and I want to track what they do IN GOOGLE FORMS. Every agent has his own 10 companies that he contacts. So, in Question 1, I will have a drop-down ...
-1
votes
3answers
56 views

Finding closest value within a range of cells spread across the rows

Attached the Google Drive link and we have to refer to scenario 2 sheet of the spreadsheet. https://drive.google.com/drive/folders/1HKePsd4SPSNdPujnwKLvq2i8xeg5sQgy Here some value is given in rows ...
0
votes
2answers
9 views

Import range from 3 sheets/tabs into 4th

I currently have 3 sheets (tabs) in a Google Sheets workbook. Each column header (Row 1) in the 3 sheets are identical. I'd like to create a 4th sheet that combines ALL the contents in the 3 sheets ...
1
vote
2answers
48 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 ...
0
votes
1answer
18 views

Automatically (protect or update or retain) a cell's formula when a row is added from “Zapier and Twilio”

When a row is added in this case from a (Zapier into a Google sheet, the formula in a cell not used by the incoming data is deleted. How can I retain/protect the original formula in column AE being =...
0
votes
3answers
44 views

Finding row number from value which is in between ranges

I have attached a picture containing two column B and C. If my input to this table is 2.3, it should return me row number where my input value is greater than or equal to B column value and less than ...
2
votes
2answers
47 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(...
1
vote
3answers
137 views

Find the closest value within a range of cells and output cell reference

There are values from F column to AJ column in row 30. My input would be available in C column in row 30. As given in the picture, my input would be 118.45, I would like to find the closest value to ...
-1
votes
1answer
31 views

ArrayFormula syntax to reference the previous row for calculating the current row

Sample sheet here (in sheet3): https://docs.google.com/spreadsheets/d/1hXZzwRnYpso_Q4PHd6M818oE347yRBAh9AqTLhqX8TM/edit#gid=318977824 In part 3 of Arrayformula Alternatives series of question, I ...
1
vote
2answers
29 views

Create random pairs without duplicates

I try to randomly pair people into unique pairs. I have two columns one with helping values created via Rand() and one that ranks upon these values with =INDEX($A$2:$A$164,RANK(S2,$S$2:$S$164)) ...
1
vote
1answer
18 views

Is it possible to return a range from an IFS or SWITCH?

I am trying to simplify some 'if else if else' code by replacing it with a single IFS or SWITCH expressions. I have simplified all the code to be trivial but show the problem. This code works and two ...
1
vote
2answers
27 views

Google Sheets' Query or Filter solution

I have a simple table with columns: A (Donor ID) B (Donation date) C (Donation Amount) I need to extract the following report: For each donor ID most recent donation date and amount donated ...
0
votes
3answers
20 views

Picking the Total from the Next Week

I have the next year mapped out (one line for each day) and have the week subtotal at the end of each week (which is a Friday). I am wanting the Next weeks subtotal to show in another cell for that ...
0
votes
3answers
23 views

Average formula for Google Sheets with substitution over some figures

I have a row of figures from 0 to 2, some cells are empty. I need to count an average for them, but with the rule that would count 2 as 1. Thanks
0
votes
3answers
46 views

LEFT JOIN two and more tables in Google Sheets

Say I have two tables. Table A (material requirements): product_id | material | required 1 | A | 0.5 1 | B | 0.7 2 | A | 0.2 3 | A | 0....
0
votes
2answers
29 views

Create a row based on the sum of every column in a sheet

I have a spreadsheet with a data set containing hours:minutes:seconds. This dataset is a record of logged times per user (rows). The number of rows or columns isn't fixed. The number of columns is ...
1
vote
2answers
7 views

I am trying to obtain formula to increment cell by 11 rows for Google Sheets

Here is what I am doing: =IF('unique stock'!C11<=0,'unique stock'!C11,"") and this goes on... =IF('unique stock'!C22<=0,'unique stock'!C22,"") =IF('unique stock'!C33<=0,'unique stock'!C33,"...
1
vote
1answer
16 views

How to stack ranges horizontally?

If {range1; range2; range3} (I believe this is called embedded arrays) outputs the combined data in a column - top to down, what puts them side by side next to each other? The 3 ranges may or may ...
2
votes
3answers
28 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,", ")...
0
votes
1answer
24 views

Blank Redundant Month

I have a large list of days and quantities that I wanted to chart so we could see how many of something we sold on a specific day. Give this goes back 600 days, there is a lot of data so I have been ...
1
vote
2answers
25 views

Perform operation on specific column in filtered range, right after filtering

I have this formula in E2: =FILTER(A1:C20,B1:B20=2) and need to perform substitute like: spreadsheet here I'm hoping to do the formula in a single cell, without additional columns.
1
vote
3answers
33 views

Identify max value, divide others by half, then total all

This may be too specific of a formula, but I'm hoping there is a function or script that will accomplish it. I need a Google Sheet to identify the largest number in a row and divide all of the OTHER ...
0
votes
2answers
67 views

ArrayFormula for conditional consequential sum-up?

I have a table which looks like this: And formula in cell B3: =IF(A3>5, B2, B2+1) and on every next row a corresponding variation of this formula. I would like to know how to create ArrayFormula ...
1
vote
1answer
25 views

Formula for filtering all duplicate data pairs in Google Sheets

I'm making a table to check duplicate data. Where data that wants to be duplicated is in the "Sampel1" column and the duplicate checking results are in the "Status" column. The formula that I have ...
1
vote
1answer
29 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.)...
1
vote
1answer
11 views

LOOKUP for data from other sheet

So I have a drop-down menu in order sheet and I have an info sheet. In the info sheet, I have 2 columns, column A has the customer name, column B has the customer e-mail. So what I'd like to do is, ...