The 2024 Developer Survey results are live! See the results

# Questions tagged [formulas]

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

2,112 questions
Filter by
Sorted by
Tagged with
1 vote
1k views

### Using UNIQUE and SORT together

I'm currently using UNIQUE to look through a spreadsheet and display in another spreadsheet all the unique names in a certain "Name" column. However, I need to sort these names by another column ...
1 vote
51 views

### How do I inhibit conversion of `(3)` to `-3` within a Google Sheets formula?

=index(split("A (2)"," "),2) renders as -2 even when cell number format is set to "Plain text". It also breaks further functions like REGEXREPLACE that expects text, not ...
1 vote
80 views

### SUM across other worksheets (without hardcoding their names)

I've got a spreadsheet for my organisation's management accounts. Each project has a worksheet, setting out income and expenditure by financial year. This is aggregated together in a few sheets ...
2k views

### How to use a UNIQUE formula with an IFS formula in Google Sheets

For example if a Uniqueifs existed, I'd have =uniqueifs('Data'!H2:H10000, 'Data'!F2:F10000, A18) If a cell in the range of 'Data'!F2:F10000' contains the contents of the cell A18, then.. Print out ...
23 views

### Connect Participant Scores(Column D) to their id Numbers(Column A) by sorting Columns B and C

I have 4 columns of data. I am trying to connect the Scores to the idNumbers. Two sets of data. Column A and B are idNumber and Member names. And Column C and D are Participants and their Scores. The ...
1 vote
14 views

### Filter Rows while also doing SUM columns based in column header values as part of Select statement in Query function in Google spreadsheets

I am trying to set up a pivot table using Query function in Google Spreadsheets, so that this can be used for various different reports. Below is the sample data i have. As per the guidance from the ...
446 views

### Return a list if 2 criteria match in Google Sheets

In an effort to organize staff training: So far I've made the pivot table, but I'd like for the pivot table to extract who needs which training, as a list in one cell. I tried using =JOIN, but I have ...
1 vote
19 views

### How can I round up numbers in increments of 0.5?

In Google Sheets I'm trying to calculate variable shipping costs. Shipping cost is calculated by adding a fixed base price to the product of the item's weight in kg and the rate per kg: cost = base + (...
1 vote
20 views

### Break out data from one row to multiple rows on Google Sheets

I am currently building a sheet to collect data from an online form and need to have individual records for each division a person is in. The data comes in like this: ID Number First Name Last Name ...
357 views

### Using REGEXEXTRACT to retrieve specific content from two separate rows in the same column

I Included a Google Sheets link =REGEXEXTRACT(A2:A3, "19..") To me, it would seem as if this would check both cells for any date starting with 19 and extract that into the cell But, it ...
1 vote
411 views

### A Master Sheet / Template for Google Sheets similar to a Slide Master for Slides/Powerpoint

How do I create a Master Sheet with formulas that, whenever those formulas are updated, it updates those formulas in sheets made by copying it? So whenever changes are made to the Master Sheet, linked ...
86 views

### My QUERY formula doesn't work on text and images

I'm having trouble with a QUERY formula in Google Sheets. I have fruit-specific sheets named Apples, Pears, and Plums that contain both data and images. I am trying to pull the combined data and ...
80 views

### Using a FILTER function in conjunction with other functions

sample sheet I am looking for a function to display the Title (from column B) that has the lowest numerical average in column (found from column G). I have that already in the form of =INDEX(B2:G,...
13 views

I'm trying to make this formula as one statement, what formula should I use, thanks. =IF(AND(\$E2="Simple",\$G2<=3),\$M\$2,\$M\$5) =IF(AND(\$E2="Complex",\$G2<=7),\$M\$3,\$M\$5) =IF(AND(\$...
1 vote
24 views

### Sum up all hours from the same day in Google Sheets

I'm trying to sum up all hours for the same day row by row. So in column 'F' I want to add the hours of the day. But I cannot use a direct cell reference like 'E4'. The reason is that I use IFTTT to ...
1 vote
1k views

### Stack two sets of data into one in google sheets - different columns

I am looking for a solution that helps me stack two sets of data (from diff. columns, different order) into one data sets EXAMPLE: columns names + order tables1: first name, last name, city, age, ...
43 views

### Using created filter to filter specific value base on other cells value

I can't seem to figure this out. I am wonder if it is possible to create a filter for a column, and then filter that column based on another cells value. So I have created a filter for A column which ...
2k views

### How can I get GOOGLEFINANCE to retrieve a ticker's high and low price data for a 15 minute period?

I would like to retrieve a ticker's High and Low prices for a 15 minute period using the GOOGLEFINANCE function but am unable to get to. Can anyone provide a formula?
135 views

### Is there any way to set different formulas for multiple ranges all at once?

In my question Is there any way to set different values for multiple ranges all at once?, I found out how to set different values across dis-contiguous cells all at once, and we developed a good ...
37 views

### Comparing data changes over time?

Let’s say I’ve collected data on a scale of bins from 1-6 at two different time periods (T1 & T2), organized as two columns in Google Sheets: T1 T2 1 2 1 5 2 3 5 ...
2k views

### Dynamic updating referenced cell from another workbook after adding/deleting rows using IMPORTRANGE - use ARRAY or VLOOKUP?

I add/deleted/move rows frequently in my workbooks. When I add/remove rows from Spreadsheet#1 ("S1"), I need the referenced cell in Spreadsheet#2 ("S2") to dynamically update so it still references ...
49 views

### Formula to calculate difference in rows between the current row and the first row above whose value matches a condition

I can't come up with a combination of functions in Google Sheets that would return the row offset of a cell from another in the same column that matches a given rule. In the image below is an example ...
1 vote
141 views

### Search for matching words in a list based on the last word in a cell

UPDATE Now it only need to scan the whole row instead of only the cell next to it. I am quite new to google sheets and I want to achieve the following: Lets say we have list 1: Red apple yellow ...
1k views

### Search across multiple columns for a value(s) return multiple row results

I am a rookie at this and I spent hours trying to figure this out but can find anything that really helps. Scenario 1: Looking at the photo. I want to search columns C through M for a selected color(s)...
1 vote
2k views

### Google Sheets COUNTIFS Range of cells as criteria possible?

In the Google Sheets Formula =Countifs, is it possible to have a range as a criterion, for example: =COUNTIFS(ExampleSheet!A1:A10, "Some criteria", ExampleSheet!B1:B10, List_of_Names!C1:C) I couldn'...
2k views

### ImportRange not pulling in Dates from first sheet in correct format

I'm trying to pull in a date cell from one sheet to another with an importrange function: =iferror(query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1UClhpev01z-...
176 views

### Google sheets: auto sorting based on color and alphabetic order

So I have this list: B (white background) A (white background) C (green background) And I would like to make a function where I sort it as follows: C (green background) A (white background) B (...
23 views

### Return sum of values to the right of columns matching criteria

I have each row of data organized in column pairs. In my example below, the column pairs would be A:B, C:D, E:F, G:H and the pattern would continue. The first column in each pair contains "...
94 views

### Google Sheets average analysis by time value

Link to test sheet In the above segment of a wider Google sheet I want to analyse the results of test1 test2 and test3 according to the time of the test to the extent that I wish to average all ...
1 vote
219 views

### Google Sheets Filter function w/ other function

I have two columns of data, one with names (A7:A), and one with percentages (B7:B). I am trying to use the filter function to show me the names of results with non-zero percentages. I also want to ...
7k views

### How to auto-calculate time based on time zones

What I'm trying to do, is to list out time zones for 24 people. The problem comes in when daylight savings dates trigger, the listed time is no longer correct. Currently, I just have everyone's GMT ...
3k views

### Copy data validation "list of items" from one sheet to another?

On one sheet I have a column that uses "list of items" data validation which allows for a drop-down menu to extend and a multiple of options to be selected. Is there a way to automatically populate ...
788 views

### Google Sheets - Filtering Chart Data with Checkboxes

In Google Sheets, I'm making a 'workbook' that keeps track of inventory in a shop. There are 3 sheets in the workbook. The first one is the raw data which is 'Item Name', 'Initial Stock', 'Stock Used',...
51 views

### How to work over rows from all other sheets in Google Sheets?

I have a 'summary' sheet and 12 other sheets named 'Jan20', 'Feb20', etc. In the summary sheet I have this formula (to calculate Uber costs): =FILTER('Jan20'!F2:F, REGEXMATCH('Jan20'!D2:D, "UBER")) ...
1 vote
1k views

### Arrayformula for running MAX of separate column

I'm trying to use ARRAYFORMULA in Google Sheets to get the max figures from a separate column only up to the row being calculated each time but can't for the life of me figure out how. It sounds more ...
20 views

### How to remove the first letter from each alphanumeric string in a column?

I have a list of 107 alphanumeric serial numbers that have an extra "S" as the first character that I want to remove. For example, if a serial number is: SFNS28O413AD It would become: ...
1 vote
25 views

### Countifs in between ranges

I'm struggling to pull below conditions together. I'd like to have a formula that will count values between 1 and 5 only when cell U13 contains the text 1-5. =COUNTIFS(U13,"1-5") =COUNTIFS(K2:K22,"&...
1 vote
172 views

### Function to search columns and tally data

I am wondering if I can write a function to search a column in my spreadsheet for a specific name, then return x number of values within the row(s) that contain the name. For example... M4 holds the ...
27 views

### Filtering by flight times in Google Sheets

I have flight delays ranging from hours ahead of schedule to delays of all durations. I need to countif any delay that is longer than 20 minutes. The values in the column take on this format: "-0:...
1 vote
57 views

### How would I SUM column values while excluding any that precede the text string "reset"?

I have a spreadsheet where the hours of wear for pieces of equipment are logged: Data is logged in new rows as additional wear-hours are put on the equipment. From time to time, equipment is replaced,...
417 views

### When I insert a row, indirect does not update

I have a summary sheet and I would like that every time I add a new sheet everything gets referenced to the summary sheet. I have used indirect to do this, the only flaw is that once I add a row, the ...
16 views

### Google Sheets Conditional Formatting w/ Several Criteria

I posted a similar question here yesterday, but didn't realize that I should have included more info/conditions. Basically I am starting with a spreadsheet of timesheet data. Each row is a single ...
2k views

### Average value using different sheets on Google Sheets

I have a data set that's separated by year (so, the first year is Sheet1, the second year is Sheet2, and so on. There are 18 years in total). I want to average the data from different years. At first, ...
1k views

### Conditional Formatting across a range of cells with a formula in Google Sheets

I have a calendar put into Google Sheets with the data put in as dates such as "9/24/2018". I have a separate sheet with a data set that has rows that classes end such as "9/24/2018". I want to ...
241 views

### How to filter an spreadsheet using filters + drop down feature in google sheets

I've created an spreadsheet and I'd like to filter the information in the rows using a drop down filter. I've managed to create the drop down menu in A3 but now I'd like to show the information in the ...
585 views

### How to make sequential list of months?

I want to fill column names with a sequential list of the last day of each month. For example, 3/31/2018, 4/30/2018, 5/31/2018, etc. Is there a formula that can do this? If cell B1 = 3/31/2018, I ...
51 views

I'm using Google Sheets make a spreadsheet with the name in column a, the company in column b and in column f, I need their email. I have 3 companies, and they have slightly different email formats. ...
1 vote
22 views

### Google Sheets Conditional Formatting w/ Multiple Criteria

I'm trying to format a spreadsheet containing timesheet data to flag certain conditions. Each row of data represents a single pair of clock-in and clock-out times for an employee. There can be several ...
1 vote
2k views

### How to create a formula in Google Sheets that can sum specific cells based on multiple criteria

I am wanting to create a formula that will look at a dozen different cells and sum to the total value of those cells but some cells will not be numbers and I want to exclude those cells in the sum. ...
585 views

### Query Problem: Function QUERY parameter 2: NO_COLUMN: Col64

I have been using this formula in a document for quite some time and have had no problems with it. Now however when I opened it up, it suddenly stopped working. When I revert the document to an old ...