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
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

Questions tagged [google-sheets-arrayformula]

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

1
vote
1answer
17 views

Report the date/column header for the first and last non-zero entries for each client/row

I have a sheet with client names (Column A) and the number of visits to the business by mm-yyyy (Columns B - AC). I'm trying to find the first month that they visited the business and the last month ...
0
votes
1answer
15 views

MATCH a cell value, then return the value of the cell next to it

Is there a way to use a MATCH formula to look up where one value appears and then use that returned index as the point to reference to find another value? Right now I have a spreadsheet set up where ...
4
votes
4answers
59 views

Joining two rows of data with alternating delimiters

I'm working with two rows of values that are related to each other and trying to build a formula that will compile the data into one cell, with syntax that uses two delimiters. If a cell in the ...
0
votes
1answer
18 views

Concatenate using an array formula to result in last name.firstname

We have a form that asks for the students first name and last name. I am trying to using concatenate and an array formula to display "Lastname.Firstname". I used: =ARRAYFORMULA(CONCATENATE($B$52:$B,...
0
votes
1answer
22 views

Concatenating URL with a value to create link, but links only works partially

I have a column of cells in Google spreadsheet with values such as: b2397 b9504 b3333 b8655 bxxxx I would like to combine each value with a URL, for example: http://websitex.com?id= ...
0
votes
2answers
21 views

How to export CSV without commas in unpopulated cells?

I have a form converter which simply rearranges columns. I use a script to paste Values only into another sheet for export and uploading onto another website. However, when I go to export, it seems ...
0
votes
1answer
40 views

Convert Number and Text to Number

I have a spreadsheet in Google Sheets with data like this: In sheet 2, in column pricelist, the below data is there. I want the summary in sheet 1 to have the same. I have used index and match but it ...
0
votes
1answer
27 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 ...
0
votes
1answer
11 views

Using CHOOSE with ArrayFormula has different results from Excel

Function CHOOSE within an ArrayFormula seems to yield different results in Google Sheets, compared to Excel. Note how only the first column is shown. Should I report it as a bug, or am I missing ...
1
vote
4answers
35 views

How to add every Sunday in sequential order?

So I tried using the simple formula: =date(2019,3,10) =date(2019,3,10)+7 =date(2019,3,10)+14 =date(2019,3,10)+21 ... etc. But when I highlight the first top 3-4 cells to drag down and formulate ...
0
votes
1answer
26 views

Use of IMPORTRANGE to juxtapose variable-length lists

Let's say I have two Google sheets, Doc1 and Doc2, both having only one sheet featuring two lists with the same schema, say two columns Name and Age. The lists have an unknown number of entries, but ...
0
votes
1answer
21 views

How do I calculate weighted rates in Google Sheets?

I would like to calculate the cost on $D "Rent" with a weighted cost when the respective cell in $B="PB". The calculation would be based on the formula 7x+1.3x=1252 where 7 is dependent on COUNTIF(A2:...
0
votes
2answers
22 views

Insert column into Google sheet generated from a Google form to calculate the difference between two columns of data

I have a Google form where people track their company mileage and input "Mileage In" and "Mileage Out." I want to insert a column next to Mileage In and Mileage Out that shows the difference between ...
0
votes
2answers
16 views

Extract and graph information from a days-hours-minutes-seconds format [closed]

I'm working in Google Sheets, and I have data for race finish times in "8d 3h 40m 13s" format and I'm want to extract that information so I can plot it in a chart. Any ideas for how to achieve this? ...
1
vote
3answers
134 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
1answer
25 views

Return a value from a column if multiple criteria are met

Can anyone teach me how I can return a value from a column if multiple criteria are met where if: a cell in a column of another sheet contains a word in a cell (referenced) the same row in another ...
1
vote
1answer
13 views

How can I divide row data into multiple Sheets and have sort work corretly?

Say I have a Sheet with people info, containing their names and some other info. I add a filter to this Sheet to be able to sort alphabetically by lastname, or any other column. Now I want to add ...
1
vote
1answer
20 views

How to import values from the 2nd sheet based on the colum of the 1st sheet?

I have two sheets - Sheet1 includes: col 1 | col 2 [Name] | [phones ] Sheet2 includes: col 1 | col 2 [Name] | [e-mail] I want to importrange if the name on Sheet1 is equal to name on Sheet2.
1
vote
1answer
29 views

Generate custom sequence in Google Sheets

I would like to create a column with a custom sequence: A-Z,a-z,0-9. (Essentially the Base64 character set with the last two characters, + and /, left out) So, A1 would be AAA, A2 would be AAB ... ...
0
votes
1answer
28 views

Search for text in multiple columns in multiple worksheets

I have 3 columns (Col1, Col2, Col3). All 3 columns are in their own worksheet (WS1, WS2, WS3). I want to search each cell in Col1 see if that cell matches anything within Col2 in WS2 then output "...
0
votes
1answer
18 views

Unable to SUM column calculated by Array Formula

I've used Array Formula to calculate the total price for an order and I have a manual column next to it to note what has been paid. When I come to do a comparison of the two though, Google sheets ...
0
votes
1answer
13 views

All text in a cell before the “ - ” or “ : ”

Is the Query function capable of taking the contents of a cell and returning everything before the first instance of - or :? Here is a screenshot of what I mean: Obviously, this isn't my actual data,...
0
votes
1answer
137 views

Convert phone number format from (404)-313-2974 to 404-313-2974

I have a list of phone numbers entered into Google spreadsheet in this format: (404) 313-2974 How can I convert this entered cell into 404-313-2974?
0
votes
1answer
10 views

Return a list of unique values based on part of the string - Google Sheets

I have a list of values as follows: Maestro purchase 28.02.2019 18:54 Coop-4853 ZH Bombach Card number: 79090775 Payment order Maestro purchase 27.02.2019 08:29 VBZ ...
0
votes
1answer
23 views

If cell contains a text string assign a value to another cell

This is my Google Sheet. I have a number of "shopping" cells formatted to have a dropdown list of products/words using the (right-click) "data validation" function. I have another cell I want to ...
1
vote
1answer
17 views

How to count unique text in multiple column with mixture of number and text?

If: column A a123 b345 c567 1234 1245 column D a123 b234 c223 4567 7689 How to put the formula to count the number of unique text in both column A and D? So that there is 5 count of ...
1
vote
1answer
23 views

Is there a way to count unique numbers and count unique text+numbers

For example: Column A 1234 4567 2345 4356 a123 b345 c567 What formula to use so that when I count row 1-7, I get 4 counts of numbers and 3 counts of text+numbers?
0
votes
1answer
108 views

How to use custom function with arrayformula?

I'd like to use a Google Apps Script custom function in conjunction with arrayFormula so this may be applied to a range. How do I achieve this? Let's say I have wrote a function that will return ...
1
vote
1answer
10 views

VLOOKUP needs to find value from a list and if its equivalent then

I have a problem where I'm currently using a VLOOKUP function on this sheet. LINK What I wanted to happen is VLOOKUP would search on the List on G column and compare it to B column and if it matches. ...
1
vote
1answer
22 views

Conditionally format rows with partial matches

I have a Google Sheets table that looks like this: (col a) (col b) (col c) (col d) Company Cohort Session Count Group -------------------------------------------- Acme ...
1
vote
1answer
44 views

How to combine two QUERY results into one column

I have the following QUERY =query(XX!A2:A;" select A where A>'50' ") and =query(YY!A2:A;" select A where A>'50' ") how do I combine these two data sources so I don't have to have two ...
0
votes
2answers
16 views

Google Sheets Formula for Attendance Records

I have a very overwhelming and large attendance sheet of 350+ people that I need to keep track of. I have a very simple attendance record of 0 if you weren't there and 1 if you were. For one of our ...
0
votes
2answers
25 views

How to make a formula so it repeats istself indefinetly

I have a formula that does exactly what I need except for that, for now, I need to manually expand the range for which it applies. What do I need to add so I only have this in one cell and it ...
0
votes
1answer
23 views

How do I calculate average duration in Google Sheets Result from years:months:day

All I'm trying to do is to get an Average Duration to result in Google Sheets to the average duration from a set of duration years:months:days I calculate DURATION using this formula: =INT((K3-B3)/...
0
votes
2answers
12 views

Query function problem

When using a query function that selects two columns from another sheet, I get two results in one cell. It looks like it doesn't respect the initial structure of the selected data. Any idea, please?
2
votes
1answer
34 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 ...
-1
votes
5answers
84 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
82 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
2answers
50 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
23 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
12 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
71 views

How to return what is missing from a 2 column list comparing against all possible combinations of 2 single column lists?

Sample data: A B C D E F 1 name handed in students assignments name missing 2 a 1 a 1 b 2 3 b 1 b 2 ...
1
vote
1answer
34 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
85 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
48 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
44 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
28 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
3answers
49 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,"="&...
0
votes
1answer
32 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
27 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 ...