Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [formulas]

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

1
vote
1answer
13 views

How to insert cell text from one cell into an ImportRange Function

Hello! so I have an issue where I am trying to do is have multiple functions in C, D, and E where a link is required. Instead of copying and pasting the link inside all the functions in specific ...
0
votes
1answer
20 views

Google Sheets IMPORTHTML parse error

I want to use the IMPORTHTML function of Google Spreadsheets. There is this example by Google itself: =IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India","table",4) Unfortunately (like ...
0
votes
2answers
16 views

Match word in a cell with a column of words

I need to match a word to a list of words and then insert the category name in the cell next to it. Eg. I will in Column A type a list of words like Apple, Red Apple, Coke, Carrot The formula must ...
1
vote
1answer
39 views

Find value of last non blank cell in a row (while skipping specific columns)

So basically I have a list of data in a row. They are totaled weekly statistics numbers and at the end of every month, there is a "total" column. The column where the 8 is at is where this formula ...
1
vote
1answer
9 views

Merge variable rows into one cell

I have the following data and I would like to have it organized by individual parts while merging the parts IDs into a cell is there any way of doing this with Google Sheets?
1
vote
1answer
25 views

How to combine FILTER with TRANSPOSE in data validation custom formula

Cells A2, C2, and E2 contain the data I want to make my custom list for data validation. Cells B2 and D2 have data I don't want in the list (just text - "vs" - in them). So, I'm trying to use the ...
2
votes
1answer
23 views

Find cell that contains some phrase

I'm looking for replacement of Excel formula: =if.error(lookup(1000;search($B$1:$B$2;A1);$B$1:$B$2); "NONE") The sheet looks like this: So in column A, I have a long product index, in column B, I ...
1
vote
1answer
17 views

If cell contains then

I need to create a formula that will check for a word in a cell and return a value or if the word does not exist then check for another word. Eg. Check Cell A1 contains the word "red" then print "...
2
votes
1answer
28 views

How to autofill value on Google Sheets

I have the following example here. I have a random value that is no spaced exactly, (some have 10 empty between them other 30). I would like to have some value equally added in the empty spaces. I ...
0
votes
1answer
15 views

How to use calculated fields to pull up “Grand Total” in Google Sheets?

I have a grand total value that I want to use in my calculated field. Instead of using a specific number like =sum(impr./919), I want something like =sum(impr./"grand total") and that the grand total ...
0
votes
2answers
39 views

multiple IF statements with between number ranges alternative

How to efectively deal with multiple IF-statements and with multiple "between number" ranges? (e.g. C column based on B column) with a rule set as follows: 01-10 11-20 21-50 51-100 101-150 151-200 ...
0
votes
1answer
15 views

Google Sheets Using Countifs & Unique Together

I am trying count the number of unique clients of Payroll service who are Won. The answer as per the table below should be 4. How can I achieve this, given that the table will be dynamic?
2
votes
1answer
22 views

Splitting numbers of a cell and adding them together in an ArrayFormula on Google Sheets

My question is how can I have digits of a cell added together in an array formula format, in that 12 | -> 3 //(result: 3 = 1 + 2) 14 | -> 5 46 | -> 10 11 | -> 2 53 | -> 8 And let’...
1
vote
1answer
18 views

Is there a way to pull or search for data from other sheets?

We use Google Sheets for tech commission sheets (track jobs and hours). Right now we have 2 years (2017&2018) in 1 set of sheets, 1 sheet for each month (jan 17, feb 17, etc). So instead of adding ...
2
votes
1answer
18 views

What's wrong with this arrayformula?

=arrayformula(if(LEN(A:A),if(ROW(A:A)=1,"Outstanding amount Payment", sum(QUERY(daily_fee_collection!A:D,"select D where B="&A:A&"",-1))))) I am trying to cross-check for payments made in ...
2
votes
1answer
22 views

Aggregate values based on categories and subcategories

I am trying to create a document which automatically tracks spending based on two categories. For example, I have a sheet formatted similar to the table below: Type | Amount | Category ...
2
votes
2answers
25 views

How to calculate a monetary value for a given duration in minutes and seconds?

I have a monetary value for 1 minute like 0,10 € (10 euro cents per minute) In Google Sheets, I have a column to enter a length of time in minutes and seconds like "xx,xx", ex. 1,30 The calculation ...
0
votes
2answers
32 views

Safe SUM() in Google Sheets

When I use sum in Google Sheets, it: sums all numeric cells from the range (which is exactly what I want silently ignores all empty non-numeric cells from the range (which is usually what I want, ...
1
vote
2answers
35 views

Using QUERY and IMPORTRANGE to pull records from a specific date range

I have created two spreadsheets. This one is my source data: https://docs.google.com/spreadsheet_A And I would like this one to just be the people who are currently here (so Column D < today and ...
2
votes
1answer
15 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. ...
0
votes
0answers
14 views

How do I automatically import one CSV cell to a Google Sheet?

I am a bit of a novice as this will probably be the most complicated thing I have set up so please assume I know nothing. I have an app (App Usage https://play.google.com/store/apps/details?id=com....
1
vote
1answer
9 views

Max number in a column

I haven't been able to find an answer to this because I don't know how to word the search terms. How do I prevent a cell from going above the number 300,000? It is simply the sum of one column ...
1
vote
1answer
18 views

Query Importrange with multiple values

This formula is working fine, but I need to add in other teams into Column A. I would need to return ATT SLC, ATT Orem, and ATT Scottsdale within this same range, but I'm not sure the language to use ...
2
votes
1answer
15 views

Remove the false operator in Google Sheets

Here I am trying to add a random hexadecimal value every time a name is entered. It’s working but the other empty cells get filled with false. I want to completely delete the false part as it’s ...
3
votes
2answers
23 views

Any suggestions for conditional formatting for columns in Google Sheets, 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 ...
2
votes
2answers
18 views

Google Sheets - How to extract words from a cell using regexextract

I have a spreadsheet with many rows like this: Arkonor 16m³Crimson +5%Prime +10%Flawless +15% Bistot 16m³Triclinic +5%Monoclinic +10%Cubic +15% Crokite 16m³Sharp +5%Crystalline +10%Pellucid +15% ...
1
vote
1answer
21 views

Query Not Showing Certain Positive Values [duplicate]

I'm attempting to import some data to my Google Sheet. Using QUERY to reshape my data, an example code of what I am trying to import is as follows: =query(importhtml("http://statfox.com/cbb/cbbteam....
1
vote
1answer
31 views

Compare multiple cells for minimum value with criteria

I have a Google Spreadsheet for which I need to solve the following (I have tried using the MINIFS function, but it did not end well): A1: "0" B1: "100" C1: "1" D1: "150" E1: "1" F1: "101" A1, ...
0
votes
1answer
17 views

Enter Tab Name In Cell To Calculate Sum of Column or Range

I have seen one other similar question on this topic posted a few years ago, but I have been unable to have any success with the suggestions given there. I have sales sheets (a new one added each day)...
1
vote
1answer
13 views

How to find out the total number, based on particular conditions

Let's say, I have a sheet with these 2 columns, where column A is for 'Remarks' and B is for 'Date'. 2nd and 4th rows are intentionally left blank to simulate that no Remarks and Date are provided. A ...
2
votes
2answers
20 views

Negate(NOT) used in IFS, but I get error

I try to use negate (NOT) in IFS, but it is not working, I get error. =IFS( NOT(TRUE); "aaa" )) How to fix it?
0
votes
2answers
17 views

View only few non-continuous columns

=FILTER(Matrice_data;Matrice_nom=G2) Pretty straight forward. I type a name in G2 and I get all rows with this name. My problem is this one: the "matrice_data" contains many columns, like 30. I want ...
1
vote
3answers
31 views

Summing up amounts if one row contains a key within another sheet

I have two sheets in one Google Sheets document: | Key | Amount | |-----|---------| | ABC | 1 | | DEF | 2 | | GHI | 3 | | Keys | |-------| | ABC | | XYZ | | JKL | | GHI | ...
1
vote
1answer
11 views

Import Range of cells as per today date in another sheet

There are three columns A, B, C AND today date is in the first row of the column A means A1. I need to import these three columns date excluding date row. A2:A100, B2:B100, C2:C100 in another sheet. ...
0
votes
1answer
13 views

Splitting multi-line cells into rows

I'd like to be able to split multi-lined cells into rows using only 1 command. I have tried using the command =transpose(split(A2," ")) but when trying to add the 'tab' key as a custom separator ...
0
votes
1answer
56 views

Set formula into a cell based on content of another cell?

Google Sheets: I would like to set a formula into a cell, but this should depend on content of another cell. Example Google Sheet (anybody can edit): https://docs.google.com/spreadsheets/d/...
0
votes
1answer
14 views

Alternate to nested if statements (alternate posing of question)

Sample Data Sheet: https://docs.google.com/spreadsheets/d/1EIQcpzwdaoNSKEPu-F1NCUOtEjS6Ez0P73dmotClT7Q/edit?usp=sharing The end goal is to have a Month + Year show up in a single cell, based on the ...
0
votes
1answer
33 views

Alternate to Nested IF statements

Goal here is to have the sheets search a row, then once it finds the first instance of >=0, return the cell value that contains the month+concatenate the value in the cell next to it (or any other ...
0
votes
1answer
33 views

How to remove duplicate rows based on a column (leaving first one out) in Google Spreadsheets

I am trying to remove duplicates from my Google spreadsheet file. Here is an initial table example A B C D HelloWorld1 2 6 41 HelloWorld1 4 3 5 HelloWorld2 6 46 6 ...
0
votes
0answers
12 views

How to highlight a cell in column A if the text in column B matches text in a list on another tab?

I have a Google Sheet set up with place names in column B as below. I have another tab on the same spreadsheet with a list that can change from time to time depending on which stores needs items ...
0
votes
1answer
28 views

Cognito Form Calculation for current week number

I am trying to create a field within a form that will automatically calculate the current week number. It's only for internal use, I just need it to show up on the Entries listing after the form has ...
0
votes
2answers
37 views

A formula for importing specific data into another sheet

I need a function for column K of Sheet1, that says: "If the row of Sheet2 contains the same RID number as a row in Sheet1, then fill Sheet1 with the email address located in column K of Sheet2" ...
0
votes
1answer
139 views

How to prevent duplicate entry in google sheets per row

So using the example data below, how can I prevent a duplicate entry with an informative message. The duplicate entry should be based on the row and not the whole sheet. So, if I was to add a second ...
0
votes
2answers
50 views

Conditional formatting if entry is final entry of the day

I have a Google Sheet in which I keep track of transactions, kinda like this: | Date | Funds | Running total | |---------|---------|---------------| | 5/23/18 | 10.00 | 10.00 | | 5/23/...
2
votes
1answer
15 views

Formula for Adding Text to End of Cell (contains URL) - How can I account for cells ending both with and without a “/”?

Working in Google Sheets, I have a column with a list of URLs. I'm trying to add a column that automatically populates the blog link (all sites end in "/blog"). However, my list of URLs are formatted ...
0
votes
2answers
18 views

Formula that will take a number in a cell, and then add that number to itself -1, until itself -1 = 0

I need a formula that will take a number in a cell, and then add that number to itself -1, until itself -1 = 0. For example, if the number were 5, the formula would be: 5 + 4 + 3 + 2 + 1 I could do ...
1
vote
2answers
73 views

Using multiple AND conditions in one IFS fromula

I’m trying to make sheets calculate my delivery times based on which locations the items are shipped between. Tried combining the Ifs and AND formulas, but I keep getting errors. (Let’s say the ...
0
votes
1answer
16 views

Sum a quantity list in an array

I have an array table using ARRAYFORMULA({Fruit Names, Quantity}) that looks like | Apples | 2 | | Oranges | 3 | | Bananas | 5 | | Oranges | -2 | | Bananas | -1 | | Apples | 1 | I'd like to ...
1
vote
1answer
145 views

Conditional Formatting across a range of cells with a formula in Google Sheets

I have a calendar put into Google Sheets with the data put in as dates such as "9/24/2018". I have a separate sheet with a data set that has rows that classes end such as "9/24/2018". I want to ...
1
vote
2answers
64 views

Conditional Formatting based on other column

I have a timesheet log with columns DATE | HOURS | DESCRIPTION OF WORK | PROJECT An employee may enter several rows for the same date to log the different amount of time with respect to the ...