# Questions tagged [formulas]

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

915 questions
14 views

### Google Sheets: IF, AND, OR functions

I'm looking to build a formula with the following conditions: Sales >= 70% Bills >= 32% Accounts >= 43% Meeting all the above criteria would result in "Pass" appearing as the text. Built within ...
27 views

### How to do permutation of characters and print it horizontally along that row in Google Sheets?

I am stuck here in printing permutation of two or more characters horizontally along that row in which the characters are. Is it possible? Because I searched it all over and only found vertical ...
7 views

### How do I search a string, and return all values?

How do I use the script editor to search a string in one cell, and return all results from another sheet? For Example: I have my string in cell B1 - which is the item number Data to be searched in a ...
15 views

### How can I do an aggregate function with multiple columns with a Google Sheets query?

I've got two columns of 10 numbers in Google Sheets. I'd like to average the numbers in both columns using a Google Sheets query. Something like this: =query(A1:B11, "select avg(A&B)", 1) <- ...
21 views

I used to know this a few years ago and for the life of me can't figure it out and can't find an answer in Google help. I know how to sort columns, but only by the default A-Z format, how can I sort ...
20 views

### Perform functions (AVERAGE, STDEV) on rows grouped by unique values in Google Sheets

I would like to be able to group rows based on unique values and then output the AVERAGE and STDEV values for those groups. Using the table below as an example, I would like to determine the AVERAGE ...
5 views

### Validate that a combination of cells are unique in a row in Google Sheets

I'm looking accomplish the following but in a Google sheet. https://superuser.com/questions/902400/validate-that-a-combination-of-cells-are-unique-in-a-row-in-excel I have a column of first names ...
7 views

### Is it possible to edit the column a Google Form response saves to?

I have a client who uses a Google Form to save data to a Google Sheet. They deleted and recreated one question, and the responses to that question are now being stored in a new column. This messes up ...
15 views

### Date highlighting of a cell, separated by weeks, given only a starting date and a cell range

I am trying to build a "life calendar" similar to this - https://labs.coruscantconsulting.co.uk/life/weeks/ - in Google Sheets. I have the manually designed sheet finished, but want to add the ...
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 ...
11 views

### Multiplying several numbers within a text string, is this possible?

I have cell A1 which is a text string: 100 blue cars, 100 red cars, 100 yellow cars In cell B1 I have a number which is a multiplier say 2. Is it possible to change the numbers within cell A1 ...
28 views

### Checkbox to activate a column in QUERY

Is there a way to activate/deactivate a column in QUERY function? I have a query to query and selection of several columns Select Col1,Col31,Col32,Col33,Col25,Col26, and want to have a checkbox for ...
15 views

### How do I refer to data in a column under this month?

In the below screenshot, I'm trying to get B42 to show the value of whichever cell on row 40 is under the current month's column: As the dates are automatically generated using a transpose(...
17 views

### Google Sheets' MONTH function on an empty cell returns 12

Is this an expected behaviour that month(ref) returns 12 when the ref is an empty cell? I would have thought that this is a serious bug. Am I correct? No mention of that behaviour in the official ...
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....
21 views

### Is it possible to add an IF condition to create a comment if a condition is met? [closed]

I have a sheet connected to a form and my users sometimes add cases many times which causes duplicates, I solved this by adding conditional coloring to the duplicated sheets. Now I want to know if ...
21 views

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 ...
16 views

### Importing only certain rows after looking at other answered questions

=QUERY(IMPORTRANGE("URL","SHEET1!A5:P85"),"SELECT * WHERE K:K contains 'Name.s'") Error Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "K "" at line 1, ...
15 views

### Google Sheets Conditional Formatting custom formula not working

I am trying to shade all rows that are associated with a specific value of column B as =\$B2="BAMS", yet custom formula keeps saying the wrong formula. Any idea?
20 views

### What function formula should I use to calculate my salary so that I don't have to differentiate between Friday and weekend manually?

These are the formulae I am currently using where I have to manually sort out if its Friday or weekend by using conditional formatting to bring attention to the minority that needs a different formula....
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: ...
13 views

### How to get a true when a character is NOT in range

I want to filter all cells in a range which are not empty and does not contain a certain character. So I tried everything with regexmatch =filter(Planning!G\$5:G\$37; regexmatch(Planning!G\$5:G\$37 ; "![...
19 views

### Google Sheets conditional formatting for Max value that meets a second condition

I'm working with a baseball statistics spreadsheet in Google Sheets, and I want to highlight the maximum value in my batting average column, but only if that value is for a player who has satisfied ...
12 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 ...
23 views

### Comparing Unique Values to a Range

I have the following formula =CONCATENATE (FILTER(boxmod!AC2:AC, boxmod!I2:AB=concatboxmod!C2 )) The intention of this formula is to take a range and compare it to a list of unique identifiers on ...
19 views

### Insert literal text that begins with = sign

I would like to add text that begins with an equal sign in a cell but I get the following error: #NAME? Question: How can I set a cell's text to a string that begins with the = sign?
17 views

### Copy other cells if EITHER column has specific text

In a single sheet wanting to copy D3:N if either B3:B or C3:C = S2. I'm sure there is a simple formula to do this I am just unable to find it!
21 views

### Copy the contents of a row to another sheet based on a specific value

I have a Google sheet with various rows of data, in one column (Column E), the values for each row follow the following pattern: A1 A2 B1 B2 C1 C2 etc. I want to copy the rows ...
10 views

### How to modify my formula to take 1 hour off?

E6 = Finish Time. B6 = Start Time. My working formula: =E6-B6+(E6<B6) =Time worked in Hours/Minutes How should I modify my formula to "minus 1 Hour" for a break? Sounds easy, but I can't get ...
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: ...
59 views

### Google Sheet Query smarter way to get multiple criteria

I use a spreadsheet where I want a list of items which contains multiple criteria. (I need all cells in a range which contains the letter 'a - g' The formula I use now is =query(Planning!G5:G37; "...
20 views

### Counta unique string on filtered list

I'd like to count unique string values on a filtered list (For example, the value should be 3 in this screenshot): How can this be done in google sheets? Here's the spreadsheet: https://docs.google....
20 views

### Link to a date cell in QUERY criteria

I have the following formula and it works fine =unique(query(ARRAY_CONSTRAIN(ARRAYFORMULA(QUERY( {Database!A1:AB, (Database!N1:N)-(Database!M1:M)}, "where Col9 <> '' AND Col1 >= date'2019-...
27 views

### Split total volume of each customer through the weeks, respecting weekly volume

I need to create a tool that splits the total volume of each customer through the weeks, respecting each customer's weekly volume, like below's picture: Columns A to F are the input, "Division by ...
22 views

I am trying to make a currency exchange rate Google sheet. I want a cell to give me a value of conversion based on the value of another cell. Currency type can be selected from the Dropdown List and ...
14 views

### Importing data found to be true from one Google sheet to another

I am trying to find a way to import data (text) from one sheet to another, via the same book. I assume it's going to need an IF or IFS formula because I only want data is that found to be "TRUE" (via ...
26 views

### Averaging cells that have both blanks and numbers for values that are in non continous columns with nine columns in between them

I need help averaging cells that contain numbers ranging from -1200 to 1200 approximately, or blank values only. I will be averaging them by groupings of 100 increments ie. -1200 to -1100, -1100 to-...
12 views

### How to find a string then find the sum of all cells next to the found string

So I'm trying to get G4 to automatically find "Bananas" in all of C and for every time it finds a value it adds up the corresponding number in D.
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 ...
66 views

### Pivot Table Filters - Custom Formula

In my pivot table, I want to only display the entries in the column "Which Event" if the name contains "Baseball" or "Softball". I can very easily do part of this by adding a filter for the field "...
30 views

### Export PDF link sheet ID auto change

I have the link for export as PDF a specific range on a sheet =HYPERLINK("*****export?exportFormat=pdf&format=pdf&gid=962914045&range=A1:H68&pagenumbers=false&gridlines=false&...
26 views

### SUMPRODUCT inside ArrayFormula

I have a fixed vector, say \$A\$1:\$E\$1 I also have a vector per row, say A2:E2, A3:E3, A4:E4... Now I want output in Column F as F2 = sumproduct(A1:E1,A2:E2) F3 = sumproduct(A1:E1,A3:E3) F4 = ...
17 views

### Conditional formatting formulas in Google Sheets

I want to use Conditional formatting so all of the cells in column A change colors whenever the matching cells in column B have the word "later". ex: If cell B20 contains "later", then I want A20 to ...
17 views

### Conditional Highlight: color change cell only if multiple others filled in

I have a shift sign-up sheet in which I would like the cells in column A to be highlighted only once there is a value (name) in the corresponding cells in columns B:D. In this example, I would like ...
16 views

### Conditional formatting cells with multiple conditions and relative references over a range

I have a Google spreadsheet with arbitrary data. Three adjacent columns (let's say A, B and C) have been set up with Data Validation to only allow values from 1 to 10. I'm looking for a way to ...
26 views

### QUERY with Week Number

I use query to extract two columns from another sheet with data. Column A has dates Column B - numbers I want to extract only rows that correspond to a specific calendar week number. For example 19. ...
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 ...
17 views

### Google Sheets Pivot Table, Summary by Day

I have a sheet with 2500 dates & times of when customers have placed their order. I want to do a heatmap or a bar graph to show which days of the week are the busiest. I rounded the exact times ...