# Questions tagged [formulas]

Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.

915 questions
36 views

### Query select all values of specific month

A have a table where Column A has dates. I want to get all values of a specific month I have the following query for May for example =query(Database!A1:AD,"Select A,E,F,U,V,W,X,AB,AC where Month(A) =...
38 views

### Change a cell to a colour when a tick box is checked in another cell

I have a cell with a checkbox. I want another cell which contains a date to go green when the tick box is checked. I presume this is done in conditional formatting but I am struggling with it.
41 views

### Formula not working on Google Sheets #error! Formula Parse Error

I have uploaded an excel spreadsheet that works fine but shows as an error on Google Sheets. =SUM(H12,N12,T12,Z12,AF12,AL12)/INDEX(FREQUENCY((H12,N12,T12,Z12,AF12,AL12),0,2)) Its calculating average ...
20 views

### Сopy cells depending on values in another cell

In Google Sheets I try to copy cells B2:B4 depending on values in other cells C2:C4. For example - example.xlsx made in Excel. But stuck and can't find a solution on how to do it in Google Sheets. ...
26 views

### Google Sheets Formula Import Range with Indirect

I have this formula and want the reference to Archery to reference Archery which will be in Cell E1. I tried using the indirect formula but can't get it to work with the formatting for Google Sheets. ...
108 views

### How to return values in a formula, from rows of latest 4 nonconsecutive dates recorded

Using an ARRAY_CONSTRAIN formula we want to capture the latest 9 records (rows) over a period of nonconsecutive dates. =ARRAY_CONSTRAIN(sort(vlookup(query({row(EBPP!D:D),sort(EBPP!A:E)},Q15,0), {...
14 views

### Query with dropdown not working

In my Google sheet I'm getting an error: Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: I've added a dropdown so the formula will auto-update depending on the tab ...
12 views

### Google Sheets populate X cells with formulas based on single cell containing value X?

Google sheets dynamically populate cells based on single cell value. I don't know what the term would be called and a bunch of searching hasn't resulted in any lead on what it would be called. What I ...
20 views

### How to use IMPORTRANGE when I have multiple tabs?

I have to export some data from a Sheet with 30 tabs(a tab for each day of the months) and I have to extract data under specific criteria from all the sheets which have to be done every month on a new ...
10 views

### Set certain days of the week on repeat [closed]

I'm making an attendance sheet for my team, how do I make only certain days like Mon Tue Wed appear in the top column?
16 views

### Query Function Not Putting Consolidated Data in the Right Place

So I don't know how to explain this completely, but basically, I'm using the query function to consolidate data from multiple sheets into one Master Sheet. The function itself seems to be working fine ...
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 ...
25 views

### How can I track the time it takes for someone to fill out a Google form?

I am running a study for my thesis, and would very much like to track the time it takes to complete a Google form to compare against how long it takes to fill.
11 views

### How to pull data from a random cell on page load in Google Sheets?

Basically, I'm just trying to figure out how to make a cell pull a random cell's value within a range at random when the page loads. For example, have cell A1 pull a cell at random from range B1:B20 ...
8 views

### How to paste a comma separated string into one column in Google Sheets?

For example, if I have (bob, china, bing, bong). I want to put bob on top of china on top of bing on top of bong.
69 views

### How can I allow results from a Google Sheets 'filter' action to be mutable?

Brief: The results of a filter are not mutable. I cannot directly add/edit rows to the cells in and around a filter result due to a #REF Error: not expanded because it would overwrite data error. ...
18 views

### Filter column based in other column (using query prefer)

I am trying to make a filter with a query based in a column, but I cannot make it works. https://docs.google.com/spreadsheets/d/1zKMW8vuyHRpS8eVEmRgAOg_dBQ0r2OIXnqV3_TFEm9o/edit?usp=sharing This is ...
20 views

### Is there a formula to list all possible 2 card combinations of 4 playing cards in Google Sheets?

Example: Is there a formula to list all possible 2 card combinations of 4 playing cards? 4 playing cards: A♥ A♦ A♣ A♠ The result should be 6 combinations only (Not 4 x 4 = 16 Combinations): 1: A♥ ...
22 views

### Reverse subtraction if less than zero (negative)

I have a simple Google spreadsheet that Takes the estimated cost for a service provided and compares it to the actual cost of the service. the equation I have in the Q column is =K4-P4. When the ...
33 views

### How can I use ARRAYFORMULA (or something similar) with GOOGLETRANSLATE?

I'm learning a new language and I recently discovered Google Sheets' GOOGLETRANSLATE function. I thought it'd be neat to have a personal dictionary of translations which I can even annotate. So I ...
20 views

### Is there a way to create a calculated field using results of another calculated field?

Is it possible to use the results of a calculated field as part of a new calculated field? I know further calculation could be done outside the pivot table but I was hoping there was a way to somehow ...
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 ...
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 ...
32 views

### Trying to Query a list against another list

First time ever using stack exchange so please excuse any formatting problems. I'm trying to create a sheet where Sheet1 is a dump of data and Sheet2 contains: A date range A list of items True/...
23 views

### Add columns based on rows

I'm currently working on setting up a sheet, but I want to add columns in one worksheet based on rows in another worksheet. I have no idea if this is even possible, I've tried googling it. Might be ...
15 views

### How to group values ​by month and sum previous values?

I would like to sum the values ​​of a table grouped by months but adding up the previous months. Example: Result that I want: The closest I've come so far is: =Query(B2:C4;"select sum(C), B group ...
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 ...
18 views

### IF Function with too many arguments

I want to apply this formula =IF(A2= "", "", IF(REGEXMATCH(A2, "Brand"), "Brand", if(regexmatch(A2, "Loans"), "Loans" , if(regexmatch(A2, "Cards"), "Credit Cards", if(A2, "Generic"),"Generic",...
84 views

### Disable automatically calculation on a single sheet in Google Sheets [duplicate]

I would like to disable automatic calculation on a single Google sheet and then have a trigger, either by button or through some implementation of scripts, inserting a column, etc that will allow me ...
37 views

### How can we make a field (Cell) value mandatory if another field (cell) have specific value, while submitting the data?

I am developing a small attendance app in Google Sheets. Each day after choosing their name or team, the supervisor will see a list of their employees to update their attendance status. The list will ...
19 views

### Analyse multiple columns per row

I need to combine the data of three different columns. Thus, I have this: ------------------------------------------------------- | ID | ID Data | Markdown 1 | Markdown 2 | Markdown 3 | --------------...
37 views

### Increment number in array formula based on a condition

I have a data set with Sub-Total rows at various intervals. As the data can have rows inserted or deleted, I am trying to use an array formula to give each group of data an incremented integer as a ...
19 views

### Google Sheets Query Where Cell value

I'm looking for help editing a Google Sheets formula. I want the formula to Import Range from another worksheet where value equals a specified cell. I can only make the where in the formula equal a ...
21 views

### Using a checkbox to auto-populate a row on another sheet

I have this spreadsheet which is used for receiving and recording work. SST responses sheet is where people submit requests, Workload is where we record all work. I'm wanting it so that when someone ...
10 views

### How can I have a 2nd function start in the cell after the output of the 1st function?

So the problem I am trying to solve is where I have Google finance fetching the price of a stock and outputting it, but the length of cells of its price history is increased by 1 each day. In the ...
26 views

I am trying to make a certain cell on my sheet light up based on what week it is. Each cell contains the value Week X where the X is a week-number. After a little bit of experimentation I have made ...
43 views

### How to use ARRAYFORMULA and UNIQUE together in Google Sheets

How can I expand the UNIQUE function using ARRAYFORMULA? Example: Here is my data set, also can be found here. I want to see the unique values of each row in next columns. CL1 | CL2 | CL3 | CL4 JAN |...
13 views

### How to auto-calculate without a need to drag down the formula?

How can I fix formula to a row so that when data is inputted in any cell within that column, it auto-computed in Google Sheets? the % doesn't work Without having to drag the formula down. Example: ...
21 views

### Google form wrongly delete rows in second sheet

I copied a previous question here since it's exactly the same as what I want to ask: I created a form in Google Docs. Then I created a second sheet to pick and process data from the first sheet ...
32 views

### Data Validation in Google Sheets Using Multiple Criteria

I have a column with text in the format: [text:more text/numbers] I want to ensure whatever is entered into the cell conforms to this syntax, or at least as much of it as possible. text is always 5 ...
29 views

### How to fetch data in Google sheet using Import XML function with Xpath?

https://www.monito.com/send-money/australia/india/aud/inr/1000 Please open the URL and see what Query should I put to extract following data (=IMPORTXML("https://www.monito.com/send-money/australia/...
15 views

### The cell is filled with \$0,00 but IF(Cell >0;True;False) return True

In Google Sheets, when I compare a Cell filled with \$0,00 using: CELL [\$0,00] IF(Cell > 0; true; false). It returns true, but Cell is = 0. It should return false. It seems to be related to ...
9 views

### Search max value of column after individual operation on each value of that column using arrayformula or similar

I have a Google Sheets with a column D with identifiers in a certain format (i.e. Y19_XXX): Y19_234 Y19_213 Y19_814 Y19_215 Y19_817 ... Now I want to determine the highest value XXX in that column ...
10 views

### Auto-increment where cell values not NULL

I have a list of items purchased, like below. A | B | C | D | E | id | item | qty | unit price | subtotal | I'd like to have the column id to ...
25 views

### Add missing dates to list of dates with values

Current Data: 9/20/2018 6 9/22/2018 3 9/23/2018 1 Desired Output: 9/20/2018 6 9/21/2018 9/22/2018 3 9/23/2018 1 I would like to have the missing dates added to Column A while the ...
63 views

### Applying IMPORTRANGE formula in Google Sheets between tabs

I have one Google sheet having three tabs, Sheet 1, Sheet 2, Sheet 3. I have another tab having the name "Sheet 4" in which I want to import all data from these three tabs, but the problem is data in ...
32 views

### How to use FILTER to select only values repeated more than X times

I am trying to use the filter function to select from 2 columns, only the values repeated. I will give you an example: My column is like: Names Jobs Value - Gerard player 4 Gerard player 2 ...
18 views

### Box change depending on drop-down list validation

For this example, we will use the top line of data so... Type = H6 Time Depart = I6 Time landed = J6 Single Engine In Command = K6 Single Engine Dual = L6 My plan is to add I6 and J6 into L6 if H6 ...