# Questions tagged [formulas]

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

1,840 questions
Filter by
Sorted by
Tagged with
8 views

### Google Sheets formula to format substrings of column concatenation?

If I have a spreadsheet with columns for author, year, title, publication, etc, could I add a column that concatenates and formats those as a citation for a bibliography? It would be something like ...
• 255
1 vote
13 views

### Regexextract to get a text in the middle of string

SAMPLE SHEET I have data in a column in this format: 1 P01 - Product Name here - PAID IN FULL 2 P02 - Product2 Name here - PARTIALLY PAID 20 1 P03 - Product2 Name here I've been trying to ...
• 191
1 vote
8 views

### How to return a value from a row from the last column a text appears

I am having problems returning the correct value i need. I enter data on this table. And i want to return some values on the other table. In that second table i want to return the last date his name ...
19 views

### How to find the next lowest available number from a list on Google Sheets

I'm looking to find a formula which I can use, to show, the next lowest available number for a range of Asset Tag 1 - 500 As an example if 1 has been used the next available number will be 2. However, ...
20 views

### Combine an array formula with query function

I want to know if there is a way that I can combine any of these 3 formulas. Sample Sheet I have in col. A3, this extracts the info in Data tab, and splits into rows: =arrayformula(substitute(...
• 191
1 vote
12 views

### Why 2 columns of the same value give different total?

Sample Data in Result Sheet I have a formula in A2 that extracts data & splits it into unit & product (A & B). I used the SUMIF formula to give the the total of each separate set of ...
• 191
19 views

### Why is COUNTIF returning a 0? It should be returning a value of 8

I have a fairly simple countif function that counts how many items are 90 days old or more. =COUNTIF(PROCFilter!H2:H,">=90") But all this is returning is a value of 0, when I know that ...
254 views

### Text result of JOIN is longer than the limit of 50000 characters

Sample Sheet I want to split a column of data, where there are multiple lines in a single cell. I want these split into rows with a formula. I've got the JOIN formula set up for that and it works ...
• 191
24 views

### How to make formula based off a withholding table?

I have been attempting to make a formula to easily calculate federal income tax rates. The picture attached is the withholding table that I've transferred onto a sheet. These are the thresholds I am ...
• 1
22 views

### Is there a way to return more than one row from multiple matches in Google Sheets?

I'm trying to find a way to fill two columns in one table with two rows of data from another table based on matched criteria. To be more specific, A3:D contains the data I'd like to pull from. I'm ...
42 views

### Google Sheets: Making a function to find a number and impute it once

I am trying to make it possible to find the highest number under 30, and than place it in another cell. Then that cell will not change when another number becomes higher. For example if the highest ...
1 vote
29 views

### How to assign cell values based on options from another list

I am sure there is a simple formula for this but I have not been able to find a solution anywhere. I am trying to fill a cell based on a list in a different tab. In my main sheet, each row has a ...
• 11
21 views

### Show sorted elements based on priority

can you please help? I would like to autopopulate column C with values in column B sorted by the priority in column A. Column A: Project names manually ordered by priority. Most important in top. ...
• 23
24 views

### Google Sheets - Autosort Script issues

I am trying to sort data descending based upon column B (2) which has different numbers in each cell. EG: B8 = 99, B14 = 70, etc. I'm intending for it to autosort and do it onEdit. I'm not good at ...
• 1
32 views

### Is there a way to create a list of values from a certain column based on referenced criteria in two rows?

I'm trying to find a formula that will fill in two columns with data from another sheet based on values from a dropdown that correspond to header rows from the original dataset. I have Sheet1 that ...
8 views

### Sum by Date but Ignore Timestamp

I am trying to auto sum day by day as data is automatically added to the sheets doc via Google Forms but keep getting errors and I think the time stamps are throwing the calculation off, so I'm trying ...
1 vote
13 views

### Formula to automatically sort range of data into groups in different columns by colour?

I am an educator trying to find a way to automatically group students into broad groups of 'below' 'at' and 'above'. My data is more specific (i.e. numerical value between 0 and 30) and I would like ...
42 views

### Sheets: Collaborators inserting their names as values into selected cells

Previous post marked closed due to being unfocused, so I pared it down. Not sure if this is now good enough, if not please can someone comment to explain so I can correct it? I'm trying to find a way ...
15 views

### Counting different types of text and producing specific text outcome

So I'm trying to create a tracker where I can gauge a pass/warning/fail status for each category. Under "Report Status", I wanted a function that can put out the text "Fail" if one ...
1 vote
41 views

### How to make a formula expand to new rows when new data is added?

I've got a set of data, a column of numbers in this case, that I want to automatically calculate moving averages for when new data is entered. Currently I'm doing this by using =TRIMMEAN(\$A\$2:A2, 0.15)...
• 25
22 views

### How to create a linked sheet that checks a box for a day if found and inserts text from another column

I'm trying to create a sheet in Google Sheets for my team that shows me what runners have run on what day of the week. Their activities are automatically imported via Zapier with dates from Strava ...
22 views

### Flattening the values (not formulae) of a summary sheet

I work at a company that owns a lot of film gear that they bring on freelance shoots. I'm trying to create a simple, streamlined way for them to notate what's brought on each shoot so that they don't ...
126 views

### Formula for matrix (dates in descending order)

As a sequel to this question Formula for matrix (dates in ascending order) how would the formula (=IF(\$B2="Apple",A2-SORT(FILTER(A:A,B:B="Pear", A:A<\$A2),1,FALSE),"") ...
• 199
127 views

### Formula for matrix (dates in ascending order)

Firstly, apologies for this question but I couldn't really wrap my head on how to do this (ultimate aim is to put it in app scripts) Let's say I have a table that is updated daily showing the fruit ...
• 199
18 views

### How to sum a column of variable length, starting at the first cell below the nearest blank cell

Can anybody help with this please? I need to sum a column of figures. The number of rows in the column will be variable, but will be a contiguous range, with no gaps. The range for the sum will start ...
18 views

### Google Sheets - Is there a way to filter values but keep them in the respective rows they came from?

I'm looking for a way to filter values based on another column but keep them in the same rows they came from. Here's an example I created manually of the correct output: The goal here is to have a ...
• 25
1 vote
32 views

### Google sheet, from 2 lists to flattened table

I have two independent columns with data. How could I use that data to then produce a flattened table like this:
• 113
1 vote
30 views

### Google sheets function to filter out lowest associated cells

I was wondering if there's a way to use a function (maybe Filter?) in order to sort this data such that column K only contains one of each number, and the highest test associated with it in column L. ...
• 25
32 views

### Match value of cell if in between range and text match

Desired outcome: I would like to return a value in column C (column D is the values I'd like the formula to return) - the value in column C should return on the criteria it matches the product name in ...
21 views

### Why does my formula stop working after 1000 rows in Google Sheets?

I am working in Google Sheets and have the following formula: =ArrayFormula(IF(ROWS(A\$5:A5)>\$C\$2,"",INDEX('Annual Schedule'!\$A\$1:J\$1852,SMALL(IF(('Annual Schedule'!\$A\$1:J\$1852>=\$A\$2)*('...
29 views

I have been battling to get the COUNTIFS formula right for the below. I need to count the number of events per day. How many times did the event happen between 2021-04-20 14:24:54 and 2021-04-20 19:16:...
38 views

### What formula can I use in order to calculate Z-Score within a specific rolling range?

Doing some sports statistical analysis for fun. Wonder if any of you guys can help me. This is the sheet I'm working with. I'm sure you can see how I'm already experimenting with this in the Z-Score ...
• 3
1 vote
20 views

### Simplify formula that checks student year level and mark to a letter grade

I am writing a Google Sheet that checks a student's year level, then assigns a letter grade based on their mark in an assessment. If there is no mark, it is designed to leave the cell blank. The ...
17 views

### How to make column go reversibly blank with custom validation and conditional format rules

I was able to use scripts editor to make some conditional formatting. Would like the option of covering up the answer column, column B reversibly. Would love some help with a script or how to do this. ...
15 views

### Extending a formula (VLOOKUP) to the whole column

I created a VLOOKUP formula inside a Google Sheet, I have 130000 lines and need to extend the formula to the end of the column.
24 views

### How to add month in Sheets cell that corresponds with formula in below adjacent cell?

I am making a cash flow projection worksheet in Google Sheets. I am using this formula to give me sequential weeks (greatly relieving the tedious entry) =ARRAYFORMULA(JOIN("-",TEXT({"5/...
23 views

### Formula to get the data from a source and use it while leaving some defined patterns as spaces

I'm trying to create a formula so that Column E will output the Employee names on Column A. I've provided an image below as my example. As you can see, there is a pattern of 4 vacant after every ...
• 381
23 views

### Have imported spreadsheet from Excel to Sheets but this formula doesn't work

I have text in three columns and need to know how many times a particular name crops up =SUM(COUNTIF(INDIRECT({"d6:d37","I6:I37","n6:n37"}),"peter")) any ideas?
34 views

### How do I automatically record finish position?

I've created a spreadsheet to keep track of the progress that my friends are making in a competition we're doing. I would like google sheets to automatically update which one of us comes in 1st, 2nd, ...
1 vote
29 views

### SWITCH statement in Google Sheets Doesn't Compare Strings Properly?

I have a very simple SWITCH statement in Google Sheets: =switch( D2,"",B2, D2,"A","A", D2,"B","B", "Default" ) If I put ...
33 views

### Create a dependent drop-down menu for an entire column

The aim: For my work, I would like to create a google spreadsheet where we can document our errors in processing the order transactions. For this I need two drop-down menus; the first shows the ...
33 views

### Google Sheets: Sum till particular date

I have time-series data that follows no predictable entries. For example as below. Date Units 01 Jan 2022 1 05 Jan 2022 2 08 Jan 2022 1 30 Jan 2022 1 04 Feb 2022 1 10 Feb 2022 1 13 Feb 2022 2 ...
1 vote
15 views

### SUM Function Works Fine in One Sub-Sheet(?) but Not in the Other?

I have a SUM function that works fine in one sub-sheet(the tabs default titled sheet1, sheet2, etc. on the bottom of the screen) but decides not to work in the other. This is what the function looks ...
57 views

### Help with SUMIFS formula with horizontal and vertical criteria + REGEXMATCH

I need some help trying to sum totals for different events based on horizontal and vertical criteria. I've tried doing different combinations of SUMIFS + INDEX + MATCH formulas, including transposing ...
27 views

### "Multiply" (Concatenate) Text Columns in Google Sheets

Simple question that I can't seem to find a solution for. I have text data in two columns like Name Num a 1 b 2 and I would like to produce a single formula if possible to output a 3rd column ...
• 1
41 views

### Google Sheets - countif with dynamic range

In sheet1 have a column with a list of IDs, each with a set of statuses: ID Apr May Jun 1 Load Load Complete 2 Load Complete NA 3 Load Load Load In a separate sheet, sheet2 I ...
• 101
6 views

### I need help how to calculate estimated hours spent per article by the total number of working hours and total numbers of articles completed

This is what my sheet looks like. I need to get the average mins spent by the editor per article. I have the total number of working hours and the total number of articles completed. I hope someone ...
20 views

### How can i make a formula to determine payment types

I am having trouble nesting and using multiple criteria. I want a column to determine if payment type was CASH, CHECK or WAITING depending on if the cells for Check# and Payment are containing data. ...