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
votes
0answers
25 views

How I can Sum multiple columns in importrange query in Google Sheets?

I import data form the file master, and make query importrange. Now I have to sum multiple columns (Column 62 and Column 63). How can i do it? Can anyone help me? This is my one column sum query. =...
0
votes
1answer
17 views

Add multiple hyperlinks in one cell, using a Formula

in Google Sheets, I am trying to add multiple URLs in one cell, using the Hyperlink Formula.When trying =HYPERLINK("foo.com","Foo") & " some other text " & ...
1
vote
2answers
18 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),"...
0
votes
2answers
40 views

Restructure (flatten) data of Google Sheets table in a new table

Column A of my Google Spreadsheet contains dates, columns B to G contain numeric values, line 1 contains the header names. Due to automatic updates the line count varies, but the column count is ...
0
votes
0answers
25 views

IF Statement works in Excel but not G Sheet?

I have a simple IF statement in Google Sheets that is not computing correctly but it is computing correctly in Excel. The formula is =IF(G15="TRUE",1,0) and G15 is a cell with "TRUE&...
0
votes
1answer
12 views

Google Spreadsheets SUMIF/SUMIFS according to a another column?

I have two columns: Billing date : date Billing amount : currency I have another static cell where I want to sum all the Billing amount values, but only if the date in Billing date is greater or ...
1
vote
2answers
25 views

How do I count the number of times string “A” occurs in cell B1

Given the string "AABACCCDAC", how do I count the number of times "C" occurs? My initial thought was to use countif on a SPLIT of the string. But Split won't split on "" ...
2
votes
2answers
45 views

Calculate Total DateDif Average Between 2 Columns (start, end) according to year expressed in days, hours, minutes

I'm having trouble getting a formula to work to calculate the total average time between times (start and end). Sheet: https://docs.google.com/spreadsheets/d/...
-2
votes
0answers
19 views

GSheets formulas won't execute

So I can't figure out if I'm doing something stupid or what's going on. I have a Google Sheet with basically the exact same formulas running in about 1700 rows. In one column, the formula simply doesn'...
0
votes
1answer
16 views

Wildcard for any value including blank

According to COUNTIF the documentation, criterion can contain wildcards including ? to match any single character or * to match zero or more contiguous characters. But this is incorrect because * ...
0
votes
1answer
12 views

Google sheet - highlihghting cell if in the same row other cell not empty

I'm trying to make a worksheet where I log shift hours. I made columns with dates and to each date a row of hours I want the date text to be green if in the same row a cell is not empty.. The code I ...
0
votes
0answers
13 views

How can I calculate variance under certain condition in APPLE numbers

I want to do something like STDEVA(IF()) or STDEVA(FILETER()) in APPLE’s numbers, is it possible? thank you very much
1
vote
1answer
34 views

Multipliers In Google Sheets

I'm trying to apply multipliers to rows within a column total, having issues.. I've tried multiple attempts using ARRAYFORMULA, MULTIPLY, SUM, and combinations of these, with no luck.. Search attempts ...
0
votes
1answer
27 views

How do I convert rows with 45 columns into 15 separate rows, each with 3 columns?

I am working with a google sheet that gets data from an email parser. Each time an email comes in, a single row is created, and fills in these columns: B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, ...
1
vote
1answer
46 views

Google Sheets formula to find rows with matching values, looking up in multiple columns

What would be the Google Sheets formula to search for a matching value in a range that goes across multiple rows an columns? For example I need to search the entire range H:P (all rows and columns) ...
0
votes
2answers
39 views

How do I keep all the values when I split a cell by delimiter (comma) in Google Sheets?

I have a spreadsheet in Google Sheets, where I am currently running a script. There are two tabs, the first tab is the following: The second tab is the following: This is the script I am running: ...
0
votes
1answer
23 views

How can I use TODAY() as part of a formula checking against a list of dates in Google Sheets?

I'm trying to use the TODAY() formula to check against a long column of date cells, and mark them as 'LIVE' if the given date is less than (or equal to) today. Crucially though, if the date cell is ...
1
vote
1answer
24 views

How do I pulling in historical daily trade volume data in Google Sheets with Google Finance?

If I try =GOOGLEFINANCE(A1,"volume",A2) where A1 is the stock code and A2 is the date I'm querying, it comes back printing 'date' and 'volume' on one line as headers and then the data on the ...
2
votes
1answer
18 views

How to fix the date and time formatting in Google Sheets when running script?

I have a spreadsheet that currently has two tabs. Data: This tab has sample data Results: This tab has the same information from the sample tab, but "attendees" are split where there is a ...
2
votes
0answers
44 views

How to split cells in multiple columns in Google Sheets, but keep surrounding data?

I am including a sample spreadsheet of what I am trying to achieve. In the tab “Data” is the data being pulled from a Google form, column G & H both have attendees listed, separated by commas. In ...
3
votes
2answers
95 views

How to convert repeating rows into columns in Google Sheets

I have data in Google Sheets that looks like A B 1 Name: John Smith 2 Email: john@smith.com 3 Name: Dan Jones 4 Email: dan@jones.com ... many rows which I'd like to get into this format:...
1
vote
1answer
15 views

Color a cell if the text inside it is not the first time it has appeared on the sheet

In cell A1 I have the text "hello". In cell A2 the text "hello" appears again. In cell A3 I have the text "goodbye". In cells A4:A10 the text "goodbye" appears again. Is there are a way (with ...
1
vote
1answer
26 views

Search a Google Spreadsheet and return results that are 'clickable' that takes you to the sheet and row

I have a spreadsheet with many sheets. (Data1 Data2 and Data3). One sheet is named Search. This search sheet will search all 3 sheets (Data1,Data2,Data3) Results will return on the sheet named Search. ...
1
vote
1answer
21 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 ...
2
votes
2answers
22 views

Is there a way to get the `IF()` statement to output nothing if it returns false?

I've got the following formula =if(sum(b1:b5)>1, "Yes") If that equates to true the formula will input "Yes" into the cell. If that equates to false the formula will input "FALSE" into the ...
-1
votes
1answer
16 views

I Need a script or a formula to get Logged User Name in Column C When he enter the cell from drop down list Selection in Column D In Google Sheet [duplicate]

I Need the Logged User Name in Column C When he enter the cell from drop down list Selection in Column D In Google Sheet I have number of users to use one sheet they Have a list of drop down ...
1
vote
1answer
66 views

Listing and formatting tagged data

I'm creating a list of texts with some tags associated to them in Google Sheets, each text has a "tags" column where the tags are separated by commas. I want to have a second sheet where the ...
0
votes
0answers
25 views

IF & Then from Separate Sheets Using Words Instead of Numbers

On one sheet I have a name populating by a Data Variation from SHEET 2 and I would like the line below it to populate with some corresponding information in the cell next to it. SHEET 1: IF ROW 11 = "...
0
votes
1answer
43 views

Can automatic send emails when checkbox = True?

I'm looking for an script can automatic send emails when checkbox is True. This is my format example: What i want is when im clicking on checkbox = True at F2, i can recieve an email with excactly ...
1
vote
1answer
41 views

Convert string to real date time value?

Here is my current input: 14.05.20 12.37.01 And yes, as it shows, there is a line break in the cell... Here is example sheet: https://docs.google.com/spreadsheets/d/...
2
votes
2answers
61 views

Convert columns of ISO 8601 strings to dates in Google Sheets

I'm pulling data to my sheet from an API using an ImportJSON function. The data array contains mixed data, including several columns of ISO 8601 date strings in UTC. For example: 2020-06-02T00:00:00Z ...
0
votes
1answer
13 views

How to create a formula to look at my most recent past 20 scores in a column and averages the lowest 8 scores

In Google Sheets I would like to create a formula to look at my most recent past 20 scores in a column and averages the lowest 8 scores. In another post I found the following formula that will ...
0
votes
1answer
22 views

Google sheets, how do I combine data from two cells, where there is a URL (web-link) in the cells, without loosing the link(url)

I have tried to write a custom function in google sheets. I tried to create a function to joint the content of two cells. It works for text, however it does not work if the text contains a web-link (...
1
vote
1answer
87 views

Find the last instance of multiple values

I really need some help with this. I need to search for the value of A2: B2 in another tab in google sheets and it needs to return the row number of last occurrence/entry of the values in A2: B2 in ...
1
vote
1answer
34 views

Using a query and tick boxes to auto fill another tab

I'm new to this and I'm racking my brains over how to get this to work, I can do basic IF & Vlookup but I believe I need the merge the 2 for this one. Is there a formula I can use on the "NLN" ...
0
votes
1answer
36 views

Filter for a QUERY function

I need some help here. So I need a function which imports selected columns from a different sheet out of which one of them contains a specified word but allows you to filter/ignore based on specified ...
1
vote
1answer
15 views

How to link cell within googlefinance formula

I'm trying to get the googlefinance formula to work for multiple country exchanges. e.g. Tencent is listed on Hong Kong stock exchange, the code is 0700. If I put =googlefinance(HKG:0700,"price"), ...
0
votes
0answers
15 views

How do I use isblank formula with the divide function?

I am trying to calculate a percentage of two columns and I know how to do that, but I do not want it to include the blank cell in the response. How do I go about that? Currently, my formula looks ...
1
vote
1answer
11 views

How can I include text from a cell within a COUNTIF function in Google Sheets?

I am using sheets to mark quiz responses. I am trying to display a 1 if the answer contains a certain string (therefore, is correct). For example, COUNTIF(d2, "*serbia"). So, if D2 contains this ...
2
votes
1answer
43 views

Count occurrences of a specific word in Google Spreadsheet

I have some cells with text. I need to count the occurrences of a specific word (not a list) from those cells. Example sheet:https://docs.google.com/spreadsheets/d/...
1
vote
1answer
11 views

Google script “if” condition not working on active sheet; However, no error received

The purpose of the Google script below is to compare cell B2 & cell A5 (Sum of C2:M2) in active sheet/tab; if B2 is less than A5 than it will pop up an alert & simultaneously change the ...
1
vote
2answers
46 views

Return the closest value from two-dimensional data

I have an input value and a table of two-dimensional data in Google Sheets. I would like to output the value from the table that is closest to the input value, either without going over the input ...
2
votes
1answer
43 views

How do I populate cells (with formulas) from one google sheet into another while using one as a search key for the other? If C=This, State D

I'd like to populate Columns C and D in an entirely new spreadsheet, where Column D still auto-populates the cells just as it does in the original/first sheet (as it does in the photo attached inside ...
0
votes
0answers
20 views

Populate filter function more than once based on conditions

Bit of a weird request but I am trying to use google sheets filter function to filter data in one tab based on criteria in another. I have 2 different dishes called A & B that have the same list ...
3
votes
1answer
19 views

How to SUM LARGE 5 largest elements out of 8 numbers in a row

In Excel I am able to get the output using this formula: =SUM(LARGE(C5:L5,{1,2,3,4,5})) But in Google Sheets I am unable to get the right answer. Can anyone please help? In the image below, ...
1
vote
1answer
28 views

I want to 1) get unique names AND 2) strip out vacancy AND 3) alpha sort AND 4) append Last name, first name

I want to 1) get unique names AND 2) strip out vacancy AND 3) alpha sort AND 4) append Last name, first name. The names are in Sheet1 and the results are in Sheet2. I'm using =SORT(UNIQUE(Sheet1!A3:...
1
vote
2answers
22 views

How to write a text if conditions met in Sheets?

I want to automatically create a text depending on what the other columns shows. For example, if in A1 there is "Service", then G1='1'. And if in B1 there is "True", then G1 = "11" (as in '1' + '1'). ...
1
vote
1answer
21 views

Google Sheets to Gmail Search

Basically I store order data in a google sheet. The Rows are in numerical order corresponding to the order number, is there a way to link clicking on the order number to a gmail search result? ...
0
votes
1answer
23 views

How to make the ISBLANK function work

How do I insert a ISBLANK function in this formula? =(IF(D21-T21<=S25, "TIMES-UP", (D21-T21))) If cell D is blank then cell E is blank if not, then the formula above will be executed. Also, I ...
0
votes
0answers
12 views

Return all results for duplicate lookup values in google sheet

I have two tabs in Google Sheet. Tab 1: Data, which contains all row data. Tab 2: Output tab, where I need the results to appear. The data is about products and their components. Each product has 5 ...

1
2 3 4 5
26