Questions tagged [formulas]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
31 views

How to add a note or comment into a formula in Google Sheets?

Microsoft Excel Microsoft Excel has this feature where one can add a note into a formula with the N() function. It looks like this: Source: Add Comments to a Formula in Excel for Your Future ...
-1
votes
0answers
15 views

Auto fill a column with formulas when new rows are added

I need to write a formula which looks out for new rows entered into the spreadsheet and then auto-fills the my new cell in column with preset formula. i use it to get task competion date after ...
0
votes
1answer
18 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 ...
-2
votes
0answers
12 views

Trying to pull a redirect URL destination for use with IMPORTXML

I've got a spreadsheet with about 7000 rows of data. I have a URL for each row, but the URL is entered as a search query that redirects to the appropriate result: URL-LINK - www.shopcwi.com/...
1
vote
1answer
43 views

Issue trying to sum different columns

I am trying to sum 3 different columns and I'm not being successful. My function is: =ARRAYFORMULA(SUMIF(MONTH(D:D), 2, A:C)) Where D is a column with dates, and A,B and C columns with currency (A-...
0
votes
0answers
13 views

Compare two GSheets files and paste a cell value if selected values match

I would like to compare two different GSheet files (NOT sheets within the same file) and return a cell value if three (or more) values match. In File 1 ( https://docs.google.com/spreadsheets/d/...
0
votes
0answers
23 views

How to write multiple Google Sheets functions properly in set.formula function [duplicate]

I have a Google spreadsheet connected with a Google form. When a new response is submitted, the formula in the upper rows should be copied down to the new added (by Google form) row. I have used once ...
1
vote
1answer
25 views

How to copy formulas from one spreadsheet to another?

If I select say 10 rows in Google Spreadsheets and use ctrl+c and then create a new spreadsheet and try to paste all the data it only moves over the values but not the formulas. Is there any way to ...
0
votes
0answers
13 views

Manually specify suggested formula google sheets

Here is how my sheet looks like: Column A:A includes a series of timestamps. for every cell $XN where X is between B-K and N is a number it's value is either empty or equal to $AN-$A(N-1) Many times ...
0
votes
1answer
26 views

Finding duplicates of emails at domain level

Need some formula help with the following - I want to compile a list of same domain name emails. Sample sheet here https://docs.google.com/spreadsheets/d/1BAB9YyPbvDgNzbT-tx-...
0
votes
0answers
46 views

Calculate power trendline coefficient for array for specific period

I have database with 3 columns (Code, Week, Value) where Code is project code, Week is date of week start and Value is aggregated amount for respective week. I need to get power trendline coefficient ...
0
votes
1answer
63 views

Google sheets join arrays

What I'm trying to do is to join few arrays of dates. For start, I have start date and amount of months I want to generate. Which I did by using =ARRAYFORMULA(DATE(YEAR(A2), MONTH(A2) + SEQUENCE(C2, ...
-1
votes
1answer
14 views

I'm looking for help with a formula to automatically update 10 previous days on a frequency count

I'm working in behavioral analysis and trying to implement a formula the would track and automatically update the number of behaviors per time period. We track the 10 previous days, which gives us ...
0
votes
2answers
32 views

“IF” Functionality Inquiry

So I am trying to figure out how to whenever it's a "Y" and "INCOMING", then it will equal a dollar amount. So as if a + b = 1 then equals $35 =COUNTIFS(F3:F100,"Y",B3:B100, "INCOMING") I did it a ...
0
votes
1answer
57 views

Request for alternative method suggestions to this Google Sheets formula

I wrote this ridiculously long formula (60,000+ characters, shown below) in Google Sheets, and it works perfectly. However, now I want to simplify it, as I'm certain that it's inefficient and could be ...
1
vote
1answer
42 views

Is there a way to do a “filter”, “if” function across multiple columns to only pull information based on certain criteria and headers?

I do not know how to explain this perfectly without a picture so please take a look at the image before reading. I am trying to filter the data in Fig 1 (Above). based on the headers and then if the ...
1
vote
1answer
31 views

Matching multiple criteria efficiently in Google Sheets

I'm not quite sure how to word this but will try my best. Below is a snapshot of a sheet I am working on to determine the global supply of a certain item. I would like the last column to display it'...
0
votes
1answer
23 views

Conditionally convert formula range into static values

I have a Google sheet where I'd like to conditionally convert a formula range into its values (effectively locking them). I've searched for a function I could embed in a IF statement but don't think ...
0
votes
0answers
41 views

Script to copy a formula to the next row

I have a script that will capture data from the email and saved it to the next available row in a google sheet. function RevisionHistory() { // Use sheet var ss = SpreadsheetApp....
2
votes
1answer
25 views

How to calculate a sum based on a date range

I am trying to find dates within 30 days in Column B. If the date matches the range it should sum all those that match in column I. I have tried this formula: =SUMIF(B:B,">="&TODAY()-30,I:I)...
0
votes
1answer
23 views

“IF” Function Question

So I have run into an issue... This maybe simple, but I just can't figure it out. On a spreadsheet on Google Sheets, I need for a cell to populate a total when a certain cell contains another ...
1
vote
1answer
30 views

How do I find the sum of a specific row of values, but only for columns that contain a different specific value?

My sheet looks something like this: I'm trying to create a formula for the Total profit column, which would be the sum of the winnings/losses for that person for that row. So for instance, for ...
0
votes
2answers
36 views

Is there a way to automate a priority table in Google Sheets based on another table?

I have a shared sheet where I have people list their classes by priority like below: StudentA StudentB StudentC StudentD Math Math Sci Eng Sci Eng Eng Math Eng ...
0
votes
0answers
18 views

How to use SORT function on existing data, and not make a copy of it

I am following this tutorial to sort data using a drop down menu and a check box. My only issue is that I want to use this method on the original set of data, and not make a copy of it. I would really ...
0
votes
1answer
37 views

LookUp Value and then Count From Specific Column If Not Blank

We need to know how many times an employee has had a specific absence type for each AM and PM shift. We have 2 worksheets. The first, called Daily, has a list of employees (col A) with the shift time ...
0
votes
2answers
35 views

Filtering on gathered statistics adds dummy empty rows and fails anyway

I have a simple data set: Pupil A Teacher A pass Pupil B Teacher A fail Pupil B Teacher B pass And want to compute the following statistics: Teacher pass rate Teacher A 50% Teacher B ...
2
votes
1answer
48 views

Pull data as a string from a hyperlinked cell in Google Sheets

I have utility that I've been working on (modified from an existing sheet) for a Tabletop game I run for my group. Currently, it generates random items from a list. The name of each of the items has ...
0
votes
1answer
87 views

IMPORTRANGE QUERY formula filtered by dates range and values from cell

=COUNTA(IFERROR(QUERY(IMPORTRANGE("Sheet1","QC sheet!A792:B10000"), "Select Col2, Col4 where Col2 contains '"&B$1&"' and Col4 contains '"&C1&"' and date "&TEXT(G1,"'YYY-MM-DD'")&...
0
votes
2answers
29 views

Use one Formula in multiple sheets

I want to use a formula in multiple sheets. for example: I have a sheet named "Formulas" and in cell "A1", There is a text "B1+B2" Now, I want to run this formula in Sheet1 and Sheet2. So, If I ...
1
vote
2answers
59 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
80 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
18 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
17 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
87 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
54 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
33 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
23 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
15 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
17 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
73 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
29 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
41 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
11 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
18 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
21 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
23 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
25 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 ...

1
2 3 4 5
24