# Questions tagged [formulas]

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

474 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
10k views

### How can I duplicate a row in Google Sheets while retaining all of the formulas and format?

I need help with Google Sheets. We are using a sheet as a working file and new rows will be added by different users. Formatting and formulas are included in various columns. How do I ensure the new ...
• 41
602 views

### Conditional Formatting in Google Sheets

The shorter question to spare you from reading more than you need to I have a few different formulas for use in conditional formatting with custom formula in Google sheets. They are all roughly 1100 ...
• 131
622 views

### How to remove duplicate words in a cell only if longer than a certain number of characters?

I need to remove a duplicate text in a cell but only if it is longer than 4 characters. I have this formula that only works for any types of duplicate words: =join(" ",unique(transpose(split(A1,", ")...
411 views

### Conditional formatting for columns, date & time specific?

I have uploaded 4.000+ Venmo donation data sets into Google Sheets for sorting, but the date and time is preset and looks like this: 2018-11-01T20:16:52 I have tried multiple approaches to sorting ...
62 views

### Check if a string appears within another cell, and return the longer of the 2

I am having a hard time thinking of an formula that can help me pull the data needed in this example: In the C1 cell, I would like to make a formula that does this: Check if any cells in the A ...
• 21
24 views

### How do I write a formula while referencing another sheet in Google Sheets?

So I want to do a CountA on Column A and Column B in Sheet Two and divide Column A by Column B in Sheet One to get the percentage totals. Can I write all that in one line? =SUM(Sheet!((COUNTA(A2:A516)/...
• 65
41 views

### How do I input a current balance in a cell from a running balance that will be updated frequently?

I know I'm not accurately explaining that but here's a sample spreadsheet of what I mean:
132 views

### How to generate standard competition ranks (not dense) for my Google Sheet?

What I have is a leaderboard sorted according from high to low scores. I would like to assign standard competition ranks (1224) formulaically. All 7 people who have the highest score (30) should be ...
54 views

### How to create draggable sheet reference with the same cell?

I'm not very familiar with Google Sheets and have a question that may be very simple but I can't seem to find an answer. I am trying to pull information from one same cell example, H47 from multiple ...
• 21
74 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 ...
• 216
38 views

### IF Cell = True, then retrieve (separate) Cell Value

Cells: T2 = Price to Pay U2 = (Tick box) V2 = =if(U2=True, "Paid", "To Pay") W2 = empty What I want to do is be able to tick U2 and have W2 contain the value of T2. V2 works, so it does not ...
653 views

### Formula for PROPER Case with Apostrophe

I'm using Google Sheets glued together (not be me) from multiple sources where some of the names are in all caps. In trying to clean web-ready content, I have good success with the PROPER() function ...
• 59
32 views

### Using multiple IF and OR statements with calculations

I'm trying to calculate prices for items that have various costs. Depending on what that cost is determined its multiplier. I'm hoping I can create a formula in a spreadsheet to streamline my process. ...
• 21
203 views

So I've already found this question answered several times around here but for some reason, despite essentially copy and pasting the answers it still doesn't work for me. I'm trying to add ...
• 21
3k views

### Format telephone numbers, keeping only digits

I have a spreadsheet that has phone numbers in the cells that are typed out like this: +1(313)313-3333 and I need these numbers to all be plain and look like this 3133133333 (with no 1 in front). ...
60 views

### If I ask a yes/no question, am I able to give it a value and add to calculations?

Question on my form: Is your wedding on a holiday? If they answer yes, I'd like to add a \$100 value to that answer and then add it to the total. Is this possible? If so, how?
• 21
666 views

### Random #REF! errors

I am a new Google Sheets user, but have been using Excel or equivalent for over 25 years. I have an issue with #REF! errors associated with Google Finance lookup in a sheet I wrote to pull stock ...
• 21
2k views

### How to sum amount with a criteria separately by day in Google Sheets?

I am using Google Sheets to track income and expenses. I have a separate file for each month and a master file to summarize each month. The file for monthly account contains a couple of sheets - each ...
• 21
1 vote
6 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 ...
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
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
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 ...
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
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 ...
1 vote
26 views

### How do you change the font color if the cell contains hyperlink on google sheet

How do you change the font color if the cell contains hyperlinks on google sheet? I have 1 cell that contains texts with hyperlinks. I want to change the color of the cell if it contains hyperlinks. ...
1 vote
17 views

### Conditional Formatting Help

I must be having an off day as I'm usually good with formulas - but I'm trying to finalize something for work and my brain is not getting me there.... I need a formula to color cell G75 Orange if cell ...
• 11
1 vote
115 views

### How to get weekly averages for workout tracker data in Google Sheets?

I am making a personal training app that uses data from Google Sheets. I want to create a formula that takes the data from a sheet of workout tracking inputs and creates a weekly average for each ...
• 11
1 vote
53 views

### Use ArrayFormula to automatically update the column with a formula already containing an ArrayFormula

I am using the below formula to concatenate some data into one string: =TEXTJOIN("",FALSE,ARRAYFORMULA(IF((Data!\$F\$3:\$F=\$C2)*(Data!\$G\$3:\$G=\$D2),Data!\$L\$3:\$L,""))) I however have ...
• 35
1 vote
19 views

### Round up duration calculation to the next multiple of five minutes

I currently have this formula: =text(D2 - C2, "hh:mm:ss") I need this formula to round up to the nearest 5 minutes. I have tried mround(), ceiling() and roundup(), but I'm not getting the ...
1 vote
32 views

Original Problem: Need to count Multiple Criteria from one column in addition to a single piece of criteria from 2 other columns in one tab into a count on a separate tab of the same workbook in ...
• 11
1 vote
22 views

### Count and number how many times the same conditions have me

I'm trying to see if there is any way to count in Google Sheets how many times conditions in other columns are met. Please see image below. In the example, there are 3 conditions (Car, Color, Status) ...
1 vote
53 views

### IF condition with ISBLANK using Google Sheets *driving me insane*

I have a Google Sheet for investment tracking setup and I'm trying to add a ISBLANK to the following formula: =IF(G67=TRUE,G12, G12*GOOGLEFINANCE(("CURRENCY:USDEUR"))) <--This formula ...
1 vote
33 views

### FILTER between columns

I have thousands of data in 2 columns First one is ordered by export and the other by latest activity. The export columns has more queries than the latest activity. (can't change the export to the ...
1 vote
20 views

### Change the text string of a cell based on whether or not previous cells are filled

I am working on a spreadsheet that tracks the overall pipeline for video production for a group of volunteer filmers and editors. Being on as a volunteer manager myself, I wanted to create a ...
1 vote
16 views

### How to create formula that copies 15 rows and inserts below

I am a newbie when it comes to formulae and macros in spreadsheets. I am creating a manager sheet for a tabletop gaming group, and during the game players may add additional characters to their ...
1 vote
43 views

I'm looking for a way to calculate the average turnover of the past 4 weeks. The formula I used to determine to current year/weeknumber is: =year(now())&"_"&0&(WEEKNUM(now();21))...
• 11
1 vote
19 views

### How do I add specific entries in columns to a final total?

To start, I'm trying to create a spreadsheet to help keep track of my spending and earnings throughout the year in an effort to crack down on my budgeting and excessive spending. I would like to ...
1 vote
86 views

### Google Sheets formula working in only some cells

https://docs.google.com/spreadsheets/d/1nmMw2cfJkD5EgPHFjFYdO4PhJ822PfQhCcl3MYYnYJ0/edit?usp=sharing I'm using a formula to search for a term within a cell and populate the correlating name into a ...
1 vote
32 views

### Count number of rows with a specific MONTH in the date of a specific cell and a Boolean condition in another cell

Let's say I have a number of dates and whether they occupy full or half a day: DATE | WHAT | HALF? ... 2021-4-1 | Something Something | false 2021-4-2 | Something Something | false ...
• 123
1 vote
37 views

### Google Form to sheet to Doc, with calculations after pushing to sheet

NOTE! I am not a programmer I'm just fumbling through this! I've gotten as far as I can with searches, getting lost on multiple steps as most responses don't integrate on thing to the next. Background ...
1 vote
33 views

### Add '/' character in between a number (which should be formatted as a date) and turn it to a date

I have received a spreadsheet which has the dates formatted with only numbers without separators and I would like to transform them into a date format. For example, I would like to change the number ...
1 vote
51 views

### Is there a way to detect clicks in Google Sheets and run a function?

I have a Google Spreadsheet as shown below. The 30 at the bottom is what the title takes to display the points. How do I make it so that when the user clicks the buy button it will deduct the amount ...
• 111
1 vote
31 views

### How to add data in a specific row after searching a column for a specific keyword?

So I'm trying to figure out how to have sheets look in the username column for a specific username and then find the sum of the numbers in the "kills" column that are in the same row as that ...
• 11
1 vote
48 views

### Spreadsheet formula to calculate most common items in ordered items lists

I am searching for a correct google spreadsheet formula to go through the list of ordered items and summarise the resulting winning items. Like is you have an Oscar ceremony and you get votes from ...
• 153
1 vote
502 views

### Google Sheets: filtering and matching a list treating NON-unique values

I have a list (column A) of non-unique text values. Items from that list are put into column B (via dropdown data validation). Any items from column A that are missing in column B are shown in column ...
1 vote
32 views

### Want to use a cell to get a limit number

Here is my query. I want "select * limit 5") the 5 to come from a cell. How do I do that? =ArrayFormula(AVERAGE(QUERY(SORT(Tabell!D4:D33,ROW(Tabell!D4:D33)*ISNUMBER(Tabell!D4:D33),0),"...
• 11
1 vote
324 views

### importing from multiple workbooks for Google Sheets to ignore workbooks that have nothing listed, instead of returning an error

={FILTER(IMPORTRANGE("116uNZi52pAQe1_D_ZLNuD18gSx3zfB1P08TL2cT7Vdk", "Trade and Manufacture!P17:T"), IMPORTRANGE("116uNZi52pAQe1_D_ZLNuD18gSx3zfB1P08TL2cT7Vdk", "...
1 vote
29 views

### Getting the column header if the row value is the greatest of the entire row

I think that the best way to describe what I want is with a image Check the image. Each row will have a description (Of a feature of the programming language, for example) each language will have a ...
1 vote
43 views

### How do I return all results associated with an input cell in Google Sheets?

I have a time study data set that has three different order types and resulting times to fulfill that order. An Order Type will be entered into C8. In cells C9:C, I want to create an auto-updating ...
• 11
1 vote