# Questions tagged [formulas]

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

1,173 questions
Filter by
Sorted by
Tagged with
26 views

### Is there a way to generate many rows and columns from a single value?

Let's say I have a sheet with a single column that lists all of my clients with each row being a unique value: e.g: client1,client2,client3,client4,... Goal: I'd like that in a separate sheet, 2 ...
16 views

### Sumif and filter using regexmatch with multiple criteria

I need to sum specific cells in 1 column (Q) based on if the other columns (R, S) have a certain word(s) within the cell. For example in the picture if a cell in R or S contains the word toy or green,...
15 views

### How to calculate the difference between two times accurately across midnight? [duplicate]

I've seen this answer on a related question which is an excellent simple solution to calculating the difference between two times. However I've run into an issue when trying to calculate the ...
12 views

### How can I keep the parenthesis in this formula from moving? `=(-0.01 - 0.29)- (0 - 0)`

I have a cell where I would like the formula to be =(-0.01 - 0.29)- (0 - 0). The order of the parentheses is particularly important because of some regex commands down the line. However google ...
35 views

### How do I import data from one Google spreadsheet to another

I need to import a range of data from one Google spreadsheet to another but I keep getting the error: "Cannot find range or sheet for imported range using this formula" =IMPORTRANGE("https://docs....
48 views

### Run formula that checks multiple cells and outputs to one cell

I want to run a formula that checks each cell between say F2 and F8, and if they match the relative cell in column B, it adds those cells together, and outputs in cell F9. Example expected result: |...
22 views

### Conditional Formatting on Google Sheets using regex

I am now going in circles, I tried to look for answers here but I am still stuck. I want to highlight a cell using conditional formatting if the value of that cell is present anywhere in a specific ...
21 views

### If cell contains certain text then another cell calculates a number

I have a budget spreadsheet with two sheets. First sheet is titled "Summary" and the second "Transactions," where I log financial transactions. I want a cell (Q18) from my first sheet to return the ...
13 views

16 views

### Google Sheets Formula - Trying to Sum, based on multiple cell criteria

I am looking for help with Google Sheets formula. The formula I need is - If A:A=LG and F:F=Indexed then sum the total of P:P for all of those instances. If A:A=PG and F:F=Indexed then sum the ...
44 views

### applying a formula to the data from google forms

I have a google spreadsheet which is connected to a google forms I wanted one formula to be automatically copied to the new lines(Column R) when google form response is submitted the spread sheet: ...
14 views

### How to extract digits only from formatted phone number string?

I'm getting user submitted phone numbers in a variety of forms, for example (fictional): (423)700-4000 9002570000 703-203-3000 I want to extract just the numbers so that I can upload them to an app ...
18 views

### How do I combine 2 IF conditions in Google Sheets?

I am trying to combine the 2 IF statements below into 1. Can you tell me the combined syntax when trying to combine the 2? =IF(AND(C5="Non-eng",P5>=2,N5>=0),"Met","Not Met") =IF(AND(C5="Eng",N5&...
37 views

### How to count Hours and Minutes in one cell

I want to count # of hours worked by subtracting the Start & end time in 1 cell (as per my comp template) ( 9am - 6pm) .( No I can't alter the template to put start & end in different cells/...
10 views

### How can I calculate the remaining loan amount at a given year in a single formula?

If I have a loan like the one in the "Amortization Schedule" here where the following is the load terms: interest_rate 4.5% NPER 24 PV 50000 ...
14 views

### Multiple formulas (?) in one cell

I'm putting together a Google Sheet for my daughter's Girl Scout cookie sales. I have a cell for "Proceeds per box". If she sells 250 or less, her proceeds are \$0.75 and for 251+ the proceeds are \$0....
14 views

### How to copy and paste a formula changing only the cell references made on this sheet

How can I copy and paste a formula changing only the cell references made on the active sheet and leave references to ranges on another sheet as is?
14 views

### How can I paste HYPERLINK formula in Google Sheets cell, to make it clickable link with defined text?

I got a script that takes some data and prepares it to paste into Google Sheets cells, some of which are supposed to be links with custom text. But when I paste hyperlink formula the script created, ...
27 views

### Filtering results on a lot of parameters

I'm searching for a way to filter +- 100 weighted averages, for which I've already made the correct formula. The datasets are in 7 different tabs and contain +- 40 columns and on average +- 50 rows. ...
21 views

### Search for missing data starting with certain text

Hello I need to compare two columns and output all missing data into a third column. The problem is that I need to also filter the first column for only certain cells that start with two letters ...
18 views

### Google Sheets: Fill out dates and replace blank cells with zeroes

I'm trying to make a timeline graph of seed sales, and right now it's almost completely unuseable: I think there are two reasons for this: Not all dates have values associated with them, and ...
20 views

### Adding Positive Values Only (across multiple sheets)

The example formula below works perfectly in my spreadsheet if ALL the numbers are positive. However, I would like to pull the values from the cells indicated below (from 14 different pages), but ...
41 views

### Need to create a formula with the operand in a different cell in Google Sheets

For eg . Existing formula which works Query(A4:K,"Select A Where I = > 0.3") should be replaced by Query(A4:K,"Select A Where I = "&N1&"") with cell N1 containing value '>0.3' Appreciate ...
27 views

### Find value in 2 different sheets

So, as you can see from the example google sheet below, is there any way to have a formula in which when you try to find the value from both sheets (Example 1, Example 2; if lets say in Example 3, ...
20 views

### Combining Duplicate Data Within A Table? | Google Sheets

Table Data: This is a dulled down version of the Data Table I am trying to filter - which is a massive list of leads. Basically, I am trying to find a way to combine all rows with similar contact ...
13 views

### Adding new rows when using IMPORTRANGE [duplicate]

I have a spreadsheet as follows: A B C D E F 1 steve 12 xxy yellow 12 a1 2 david 32 yyx green 165 b1 3 tom ...
62 views

### Set calculation precision in Google Sheets

Google Sheets uses some sort of floating point arithmetic in its internal calculation, even if only integer numbers are in the calculation. For example: A1: =128^128 A2: =A1+1 A3: =A2-A1 Then, A3 ...
85 views

### Google Sheets If else formula

I'm trying to write an if else statement in Google Sheets. I want the value in cell B2 to dictate what the value you will be in cell D2. If the cell B2 is less than 12 than I want D2 = 17, if B2 is ...
39 views

### Count Occurrences of Unknown Values

I'm really new to Google Sheets and formulas in general. I know you can count occurrences of values when you know what you're counting e.g. the times red is written in the column and you can search ...
25 views

### How to sort a UNIQUE()-generated list based upon values from an independent column? [duplicate]

Can and how do I get the UNIQUE()-generated list in column B to automatically sort (in A->Z order) based upon the numbers in column C, as shown in the image below? I want this "unique sort" to ...
17 views

### Always display formula result

So I have this code formula on many cells in the sheet: =iferror(vlookup(D877,'Import Approved Video Stats'!A:L,5,false)) to import some info from other sheet. What do I have to add to that ...
26 views

### Google Sheets - If? ISBLANK? Not sure how to pull this one off

I am trying to find a formula for the following: If cell A1 has a date in it, copy that date to cell F1. If A1 does not have a date in it, copy the date from cell B1 into cell F1. I have a receipt ...
18 views

### Concatenate multiple columns automatically [duplicate]

I have three columns A B C D E F G H I I want to write a formula such that it will combine the three columns, one on top of the other such that my fourth column is: A ...
15 views

### New solution to identify BY as a column label instead of an identifier?

I'm new to this, so I've posted this question as an "answer" to this post QUERY error for BY column a previous solution for this issue no longer works, and I need a new way to tell google (via code) ...
28 views

### Multiple Unique Values Across Worksheets in Google Sheets

I rarely use spreadsheets, but I thought creating a workbook for a contest would be really helpful. I am not sure if what I am asking is possible without a custom function. I am grateful for any and ...
24 views

### How do I use conditional formatting on one column based on certain characters in another column?

When column D has a hyphen (-) in it, I want the fill color in column H to change. What is the custom formula for that?
44 views

### Generating a list in Google sheets with checkmark filters

I've been wracking my head over this for a few days now, basically I tried to use a index/match/if/countif setup to try to get a unique list generated based on filters made by checkboxes. (not ...
40 views

### Rolling Year Formula

I have a Google sheet where I would like a score based on a rolling year figure. I have managed to do these separately using the formulas: =query(A2:B," where A >= date '"&TEXT(today()-E1,"...
25 views

### Google Sheets Error could not fetch url: [duplicate]

So I have a google sheet that tracks some parameters from an investment I am in. About 3 week ago all of my importHTLM or importXLM functions just quit working. They all say the same error of could ...
28 views

### What formula could I use for conditional formatting to highlight cells in Column B if the text in Column A has a link in it?

I want to easily see what text in Column A has a link in it by looking at Column B.
17 views

### How to make arrival time in Google Sheets

Hello I would like to ask how to calculate the arrival time. For example: I have 1 cell where I put km which are left for 1 truck. I want to calculate the current time + the cell where I write the km ...
53 views

### Filtering multiple ranges into a single list

I have a very long list of data, the first column of which is the date. I have several date ranges from within that list that I want to pull out and combine in a smaller list so I can analyze the ...
30 views

### How to return a blank or dash when one of the cells is left blank?

I am looking to calculate pricing and tax between 3 cells. Though if 1 of the cells is blank, I want the end result cell to be blank. In this instance I have the: (order total * the tax rate) + the ...
35 views

### Importrange a formula?

My question is: I've a lot of files, like 30+, that I need to maintain periodically. My idea was to create a master file, where I will modify formulas only 1 time, and then the formulas will be ...
19 views

### How would I define multiple answers based on which checkbox is selected in google sheets?

I currently have this formula set into my cell: =if(G32 = TRUE, "12", "") Thus, if G32 is checked it will display 12. However, I want to allow the formula to react with multiple check boxes each ...
24 views

### Google Sheets, CountIf, name matches and is within date range from another spreadsheet [duplicate]

In another sheet I want to count how many meetings were scheduled for a rep within a date range. My 3 columns are the date, rep, and misc data =countifs( "https://docs.google.com/spreadsheets/d/...
27 views

### Googel Sheets MINUS formula is adding

I have been working with some numbers in Google Spreadsheets and when I type the formula =MINUS(A1,A2) or even =A1-A2 the spreadsheet keeps adding the two numbers instead of subtracting them. What am ...
26 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. ...