Questions tagged [formulas]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
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 ...
1
vote
1answer
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,...
0
votes
0answers
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 ...
0
votes
0answers
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 ...
0
votes
1answer
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....
1
vote
1answer
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: |...
0
votes
0answers
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 ...
0
votes
0answers
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 ...
0
votes
0answers
13 views

My function links all required cells between google sheets except one!

I have this query =query(IMPORTRANGE("<https://docs.google.com/spreadsheets/d/1uxIkcjOTxFAd25Ukzji3VJKoFbAKybuFXfHlBzaGDWc/edit#gid=1385200762>","Master!A3:y"),"select Col2, Col3, Col12, Col13"...
0
votes
1answer
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 ...
0
votes
1answer
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: ...
0
votes
1answer
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 ...
0
votes
0answers
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&...
2
votes
1answer
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/...
0
votes
0answers
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 ...
0
votes
0answers
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....
1
vote
1answer
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?
1
vote
1answer
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, ...
1
vote
0answers
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. ...
1
vote
1answer
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 ...
0
votes
1answer
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 ...
0
votes
1answer
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 ...
0
votes
1answer
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 ...
1
vote
1answer
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, ...
0
votes
0answers
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 ...
1
vote
0answers
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 ...
2
votes
2answers
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 ...
0
votes
2answers
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 ...
0
votes
1answer
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 ...
1
vote
0answers
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 ...
0
votes
0answers
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 ...
-1
votes
1answer
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 ...
0
votes
0answers
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 ...
0
votes
0answers
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) ...
0
votes
1answer
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 ...
1
vote
1answer
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?
0
votes
1answer
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 ...
3
votes
1answer
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,"...
0
votes
0answers
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 ...
2
votes
1answer
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.
0
votes
1answer
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 ...
1
vote
2answers
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 ...
0
votes
3answers
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 ...
0
votes
1answer
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 ...
0
votes
1answer
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 ...
1
vote
1answer
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/...
0
votes
1answer
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 ...
0
votes
1answer
26 views

Google Sheets formula

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. ...
0
votes
1answer
21 views

Help with a Google Sheets formula for Inventory Tracking

Wanted to get some help with finding the correct formula to input into a cell in google sheets. I need to determine when we are getting low on product and when it is recommended to order more. I ...
0
votes
1answer
15 views

Problem with Nested IF() statement on google sheets

I am trying to create a formula for a monthly expense report using the following nested IF() statement: =IF(B2="Weekdays", C2*20, IF(B2="Monthly", C2, IF(B2="Weekly", C2*4, IF(B2="Bi-weekly", ...