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
1answer
125 views

Change default values when Google Forms autofills a Google Sheets

There's a Google form that's linked with a Google sheet: whenever a response is posted to the forme, a new line is added to the sheet (here's a quick tutorial just in case). Is it possible to have ...
1
vote
1answer
24 views

Concatenating cell values to a matching cell

In the following sheet I am trying to grab a list of GUID keys from tab sheet1 and concatenate them to their respective order in tab sheet2. My expected output is in tab sheet2 column B as follows: ...
1
vote
1answer
25 views

Boolean test of whether a time is before or after midnight in Google Sheets?

I'm having a job trying to create a Google sheet which calculates the difference between the time I fell asleep and the time I woke up. Here's what I want to happen: Sleep time Wake time ...
1
vote
1answer
43 views

Left join 2 sheets on 2 key values, add constant value

I'd like to join by date on these 2 sheets (Income & Expense), on key values month and day and add constant value from FixedCost sheet, Income: Expense: Add FixedCost to every row/date: ...
1
vote
1answer
30 views

Time not formatting as expected in Google Sheets

I imported a report to Google Sheets and the duration column shows 7 h 37 m or 14 h 5 m a similar format. When I replace h with : it still does not properly format to time duration and so I cannot ...
1
vote
1answer
17 views

How to count cell with upper leter in array?

I need to count the upper letter if exist in a particular cell. So my results should be as on the image below. important is ignore in count empty cell. I noticed that task is simple if I can use ...
1
vote
1answer
18 views

Rotate and duplicate row values in Google Sheets

I'm trying to rotate values in a sheet and then duplicate the first value in the row as a header. For example: a | b | c | d would become: a | b ----- a | c ----- a | d Is there any way to do this ...
1
vote
1answer
36 views

How to import/merge data from from 2 separate sheets from another spreadsheet

I need to import data from "Spreadsheet A" to "Spreadsheet B" but all the data is in two separate sheets "MY" and "NY" in "Spreadsheet A" I basically need exactly this but with a function to import ...
1
vote
1answer
20 views

How can I have Google Sheets automatically parse and calculate text that follows a certain pattern?

I have the following sheet: https://docs.google.com/spreadsheets/d/1iEMjWVEwyuqbn_imW1UZ-AF2NlE__IbRZdHQifKJjko/edit?usp=sharing I would like to calculate "Total Work" by deriving it from my own ...
1
vote
1answer
107 views

COUNT UNIQUE values by group ONLY IF NOT already counted in previous group

I hope to explain clearly what I'm trying to do. I have some data in column B, grouped as shown in column A. I'd like to count the unique values for each group present in column A, not taking into ...
1
vote
1answer
21 views

How to find the average of difference between two columns?

Imagine you have a Google sheet with two columns, each holding a time - let's say one is bus departure and the other is bus arrival. Is there any elegant way to present the average of all the time ...
1
vote
1answer
73 views

Alternatives to dragging formula down?

I'm having trouble applying the following formulas to entire columns in Google Sheets. I think that an array is the best option because there are 5000+ rows, but any help is appreciated. I'm really ...
1
vote
1answer
19 views

Shortening this Formula, and Future Proofing it

I am creating a Google Sheet to use as an employee schedule. On this sheet, I have Cells which will contain the Total number of Hours worked per day by all employees. At the moment the formula I ...
1
vote
1answer
14 views

COUNTIFS issue, output is always zero

I have no idea what I'm doing, considering there's plenty of topics on COUNTIFS. However, each solution I've tried, as well as my own solutions, which I shall list, end up in the same thing... ZERO. ...
1
vote
1answer
32 views

How to convert multi-value answers into single column in Google Forms' response sheet?

I have a Google form that asks for Name and up to 3 Score. The data is dumped into a sheet like this But that data is not very convenient. I want to have the data like this... Can I do that by ...
1
vote
1answer
19 views

Formating a number with specific spacing

I'm attempting to pull a particular option value from Yahoo! into Google Sheets. The base URL is: https://finance.yahoo.com/quote/GOOG190418C00800000 When breaking the formula up, I have to ...
1
vote
1answer
32 views

Total Wages Calculations

https://docs.google.com/spreadsheets/d/13HdNN_qa9rhLFyMe0N9I9EusmBudi_dcdBkc6UeLHMM/edit#gid=878850570 I am trying to total Normal Pay but the formula does not Multiply Regular Hours by Normal Rate ...
1
vote
1answer
11 views

Finding the number of instructors available on different weekends in Google Sheets

I'd like to find a generic way to see when resources are available, in order to help plan when activities can be run. In this case, the resources are people with certain instructor qualifications (...
1
vote
2answers
31 views

Google Sheets - Convert entire column of prices by 50%

I have a large column of prices, however, sometimes the word [FIXED] also appears in these columns (see screenshot below). I need to convert this price list to wholesale. All the products wholesale ...
1
vote
1answer
46 views

Rounding in an array equation?

I'm hoping someone can help me out... Is there a way to round: =ARRAYFORMULA( (If(ISBLANK(A2:A),"", (H2:H*10^-3)/((E2:E*10^-3)*(F2:F*10^-3)*(G2:G*10^-3))))) to two decimal places in the equation ...
1
vote
1answer
20 views

Using a single cell serving as an ID source for formulas to import data from

I have my master Google sheet and I need a weekly updated Excel report to be imported into that sheet. Right now I use =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A; IMPORTRANGE("XYZ"; "'Overview'!A4:FZ"); 10; ...
1
vote
1answer
15 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
72 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 ... ...
1
vote
1answer
25 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?
1
vote
1answer
29 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
1k 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 ...
1
vote
1answer
48 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
1answer
187 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
189 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 ...
1
vote
2answers
31 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
3answers
524 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
vote
1answer
381 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
1answer
41 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 ...
1
vote
3answers
55 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 ...
1
vote
2answers
651 views

How to check if the 1st letter in a cell is UPPERCASE

I'm trying to find out if the 1st letter in a cell is UPPERCASE. I know how to convert case using PROPER(), UPPER(), and LOWER(), but not how to detect case. The following checks every word in the ...
1
vote
1answer
58 views

Can results of ARRAYFORMULA be used as values in an annotated timeline?

I am trying to create an annotated timeline with the following data, which exists on another sheet: I am using an ARRAYFORMULA to retrieve the text. I want to display these values as an annotated ...
1
vote
2answers
162 views

Add numbers to results of Google Sheets query

Is it possible to add a column of numbers to the results of a query? It would be nice to have a number (from 1 to n) next to each record returned from a query.
1
vote
1answer
49 views

Convert cell containing days of the week in first-letter format to day names in Google Sheets

I created a schedule of events in Google Sheets. I have a cell that contains MWF, indicating that the event is every Monday, Wednesday, and Friday. I have a different cell that reports the word ...
1
vote
1answer
670 views

ARRAYFORMULA for a column, but only for blank cells

I'm trying to fill a formula down a column using ARRAYFORMULA. However, there are certain cells in this column which I would like to overwrite, but all the other cells should use the ARRAYFORMULA. Is ...
1
vote
2answers
7k views

Google sheets function IF, AND conditional formatting

EDIT 2: link to test sheet: https://docs.google.com/spreadsheets/d/1IWQWDK9piw_xFVm_48XZ8YAS_LQqXi6sd2GITLIfoAQ/edit#gid=903221057 EDIT 1: added a column, so now J=K, K=L, L=M | updated formulas to ...
1
vote
1answer
44 views

Check value even if it doesn't match validation

In my Google spreadsheet, I have a column G of dates that interviews are/have been scheduled on. There is a validation set on it to validate for dates with a warning. Every once in a while, when ...
1
vote
1answer
22 views

QUERY for like-minded rows

Scenario Consider the following query: =QUERY(A15:E861, "select B, sum(D) group by B") This works for rows with col B's that have the SAME description eg. 1 | abc | n | 10 2 | abc | n | 10 3 | cba ...
1
vote
2answers
2k views

How can I set the increment value for Google Spreadsheets to auto-increment?

I have a series of cells referencing other cells: | Row | a | b | col B shows | |-------------------------------| | 1 | 1 | =a2 | 2 | | 2 | 2 | =a4 | 4 | | 3 | ...
1
vote
1answer
17 views

Add Two Weeks (Weekdays) on Data Validation

I am needing to add two weeks on to date in a separate column. In one column (A) I have the "Date Requested" and in the next column (B) I have "Due Date" I need the due date column to have the "Date ...
1
vote
2answers
21 views

Searching a list and replacing value if any item matches any item in the other list in Google Sheets

I have a list of usernames on two different sheets. I need to scan the first list of usernames to see if they appear in the second, and if they do, replace them with a predefined username. I can't ...
1
vote
1answer
26 views

Extracting specific phrases from a cell and echoing them in another

Longtime learner, first time poster here! I was wondering if anyone knew how to extract specific data points (phrases) from a Google Sheets cell and echo them in another cell. For instance, in my ...
1
vote
1answer
25 views

Conditional row sum

I'd like to sum a column of cells if the corresponding cell on the next column contains a string of any sort. I've tried using SUMIF but I can't figure out how to combine it with ISBLANK or ISTEXT. ...
1
vote
0answers
11 views

Survey Data & Google Sheets: Excluding Missing Values?

I am using survey data in Google Sheets with rows for "before" and "after" responses, with the same survey administered to the same subjects at two different time periods. With each question, a number ...
1
vote
0answers
28 views

why does this array formula on google sheets display no match when i add a new row?

hi and thank you for reading my post. please help me with my array formula. the goal of the formula is to display the status on the column of my work sheet (queue, working, done and blank). it works ...
1
vote
0answers
11 views

Arrayformula with iferror and vlookup does not update automatically

My spreadsheet is for inventory checking. I use several of arrayformula ,iferror and vlookup. The problem is I just noticed that sometimes the formula does not update. It might because I duplicated ...