Questions tagged [google-sheets-arrayformula]

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

1
vote
1answer
112 views

Consecutive numbering skiping empty cells with ArrayFormula

I am trying to auto-number only the existing names on a cell using an arrayformula: I have two cells one for the names and the other for consecutive numbers. Here I have an example of what I want to ...
1
vote
1answer
1k views

ArrayFormula to compute running average for groups of rows

Is it possible to write an array formula that calculates the running average of the Amount column for each of the Name groups in the following sheet? The rows are sorted by Name. In Column C, I have ...
1
vote
1answer
26 views

Automatic merging of duplicate cells under common ID

I have data in column A with IDs and a column B with data for those IDs. Right now I have multiple (usually 2-3) rows with the same ID but different data. What formula to use to merge those into one ...
1
vote
1answer
19 views

ArrayFormula stops working after shuffling rows

I have a pretty extensive sheet wherein many columns I use an array formula to automatically fill data in from various other spreadsheets. The problem I have is when I need to rearrange rows then what ...
1
vote
1answer
13 views

Dynamically update cell contents by merging incoming Google Form submissions

I have a google form where multiple students will anonymously submit comments/feedback for another peer. Currently I am using the formula below to essentially concatenate all of the student comments ...
1
vote
1answer
23 views

Comparing two cells and adding text to matching values

I have two columns, one with all items in a set and another with the items I currently have. I would like to compare those and for the matching results to have a bit of text added if they match. So ...
1
vote
1answer
20 views

Using ARRAYFORMULA and QUERY to produce results in multiple columns

I would like to be able to group rows based on unique values and then output the AVERAGE and STDEV values of two different variables for those groups. Using the table below as an example, I would ...
1
vote
1answer
11 views

Perform different functions depending on if a call is blank using Google Sheets

I want to determine the area of objects in each row. If values for length and width are present, I want to multiply the values to determine the area of the object. However, for circular objects, the ...
1
vote
1answer
13 views

Full Join 2 lists with 3 key values

I'd like to fully join 2 tables on 3 key values, there doesn't need to be 0 value on blank cells if it makes it easier to understand (Some Array Formulas can be really confusing) I have income: I ...
1
vote
1answer
16 views

Looking for a way to get the current date represented as a numbered month with decimals

Essentially I'm hoping to find a formula that would return the current date as a month in decimal form. So if it's October 15th, the number returned would be 10.48. September 10th would come back as 9....
1
vote
1answer
21 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
20 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
19 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
29 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
16 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
15 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
32 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
54 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
16 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
26 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
18 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
12 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
25 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
14 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
31 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
10 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
23 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
14 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
14 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
43 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
24 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
26 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
577 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
22 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
108 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
135 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
24 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
445 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
195 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
22 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
48 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
311 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
41 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
87 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
545 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
5k 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
43 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
21 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 ...