# Questions tagged [formulas]

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

1,840 questions
Filter by
Sorted by
Tagged with
2k views

### A formula that does Excel "countifs" function involving dates

I have a Google spreadsheet in which multiple users are tracking a list of dates and class names (math, reading, writing, etc). On a separate sheet in the same document, I want to be able to give a ...
81k views

### Add 3 hours to Date value (DD/MM/YYYY 00:00:00)

I have cells with dates in this format: DD/MM/YYYY 00:00:00 and I want to add 3 hours because I'm in a different GMT zone. Is possible to use dates in a formula? Something like: 30/12/2012 22:15:00 ...
1 vote
2k views

### Nested IF statement on Google Spreadsheet, second part same as the first

I have a spreadsheet for my budget. Payments are either drawn from my bank or my Amex card and then my Amex card is drawn from my bank as well. So I add up all my monthly total like this: =sumif(I3:...
4k views

I have a long column of numbers in Google Spreadsheet, and occasionally there exists a cell that sums some of the cells below it. For example: =SUM(2:4) 5 10 30 =SUM(6:8) 40 10 10 I'... 4k views

As to answer a question on Super User, I was making a sample spreadsheet on Google Docs. I made it first in Excel 2010 on my computer to copy/paste it later on. When I tried to copy a formula that ...
6k views

I want to use the JOIN formula in Google Spreadsheets to transform all values in one column into a list, with , around each value. Has anyone used this formula before? The only information I can ...
221k views

### How to Fill a Column with Sequential Dates in Google Sheets

In Excel, you can right-click drag a date downwards to fill a column with the next dates in the sequence. For example, if you enter 9/1/2014, select that cell, then right-click drag down over the ...
41k views

### Is there a way to generate number of days in a month?

The title kind of says it all but just to give you the use case, I have a spreadsheet where I track monthly advertisement revenue and want to end up with a daily revenue column. Here's a simplified ...
1k views

### How to put the name of a cell into another cell?

In Google Sheets, is there a simpler way to put the name of a cell reference into another cell than this? =concatenate(char(code("A")+COLUMN(D3)-1),row(D3))
1 vote
3k views

### Return an offset cell value, where last occurrence in range is matched

I have a worksheet like this: || A | B | C | ---++--------+-------+-----| ---++--------+-------+-----| 1 || 1 | a | | 2 || 2 | b | | 3 || 3 | X ...
1 vote
9k views

### How to query for count of dates in Google Docs?

I have a Google Spreadsheet of line items. One of the columns is a Date field. How can I query (or use a formula) to, for example, get the count of records where the Date field is within February of ...
2k views

This is my scenario Users are entering data into rows with several columns Last column is calculated on values of few previous columns (so it's row number independent). While entering new and ...
59k views

I am using Google Spreadsheets to create a matrix (time/activity) as a daily schedule for staff to sign into. Is there a formula to calculate the number of cells in a column that are shaded (or not ...
4k views

### Google Docs ImportXML can't scrape anymore?

ImportXML lets you get information from filetypes such as xml, html, csv, tsv, as well as RSS and Atom feeds. This example in Google Docs Help for importXml function - =importXml("toysrus.com";; "/...
12k views

### Sum of the values of the 10 cells above me?

I'd like a formula for Google Sheets that will return the sum of the values of the ten cells above the current cell. That is, if I'm on C31, I want the equivalent of =SUM(C20:C30). However, I want ...
20k views

### Adding one column to a string to dynamically create a URL in the next column

I have a spreadsheet of product ID codes that I'd like to turn into links, and from everything I've read, the concat or concatenate functions in Google Sheets should work for that, but so far ...
8k views

### Copy data down into next set of blank cells, if any, for whole column?

I have some text that I've taken in from a pdf, however the columns are not lined up the way I need them to be: I want to duplicate the column A and D values down into any empty spaces/rows. So in ...
1 vote
8k views

### Circular dependency detected even while using IFERROR

I have the following function in the cell B2: ARRAYFORMULA(SUM(IFERROR(Purchases!B3:B, 0))) And the following function in cell C2: ARRAYFORMULA(SUM(IFERROR(Purchases!C3:C, 0))) Basically, the ...
214 views

### Update a cell based on two matching fields in separate tabs within a range

I'm trying to update a cell in Google Spreadsheets where its value comes from another sheet and based on the date range entered with it. Here is what the two worksheets look like in the file. ...
18k views

### Sum of all cells in Google Spreadsheet where matching row fits a value

I want to return the sum of all cells in ColumnA only if the value of a cell on the same row is equal to a specific value. For example, sum C4 if D4 = "Bob". Assuming there are six rows whereby "Bob"...
11k views

I have a spreadsheet in Google Docs like this: Johan Mark Gil Pablo Course 1 45 50 80 50 Course 2 250 300 50 270 Course 3 19 50 ...
132 views

### Create a formula based on the number of columns in Google Spreadsheets

I have two rows of data Row B = price of product Row C = quantity of producst I need to get the summary price: (B1xC1)+(B2xC2)+(B3xC3)+..+(Bn+Cn) I don't know n (quantity of columns) because n is ...
59k views

### Conditionally formatting a cell based on another cell's value in Google Spreadsheet

I did some searching on this and came across this post. However, this solution uses an onEdit() trigger. I would like to see a solution that uses a formula approach, so in the OP's example from that ...
1 vote
998 views

### How do I select Column1's value if an input value is between the integers in Columns2:3 for a range?

In a Google Sheets I have two tables: Date BMI BMI Outcome 2011-06-21 22.1 ? 2011-06-22 21.8 ? 2011-06-23 21.5 ? and Name From To Severely underweight ... 1k views

### Google Forms erroneous deletes rows in second sheet

I created a form in Google Docs with quite some questions. For a good overview I created a second sheet to pick the important parts from the survey. There I'm also able to add extra columns to add ...
5k views

### Returning the SUM of a range of cells from user-input START to END

In Google Docs, I am trying to sum the values of selected rows in a table, from position START to END. Imagining this sample data: Index VALUE 1 300 2 400 3 750 4 850 5 ... 5k views

### Is there a way to search and replace a regular expression in Google Docs spreadsheets?

The problem is that I entered a lot of formulas like =round(x+y) into a Google Docs spreadsheet. Now I need them exported to MS Excel and the latter only accepts =round(x+y,0) as a valid format. ...
2k views

I am having trouble writing a function for this need (in Google spreadsheets): I have 2 columns A and B. I want to be able to get a percentage based on those two columns. I want to count the number ...
9k views

As a programmer, I find myself somewhat frustrated when trying to use Google Spreadsheets, because there seems to be no definitive description of the syntax that I can use for formulae. For instance, ...
23k views

I want to be sure all my Google Spreadsheet work can be converted OK to Excel. How many of the Excel formulas match the formulas of Google spreadsheets?
202 views

### How do I use a computed range in formula?

I currently use this formula: =sumif(\$E\$2:\$E\$363,L3,\$D\$2:\$D\$363) I'd like to change it to instead of having the \$D\$363 hard coded, that it pick up the "363" part from another cell that I specify. ...
1k views

### What formula would I use to calculate monthly cost?

I'm trying to make a Google Sheets that will list my total expenditures in the form of recurring fees/subscriptions. I've successfully enabled the basic 'SUM=(C2:C24)', but I would like to add a ...
2k views

### How do I reference existing formulas from custom formulas in Google Spreadsheet Scripts?

I want something essentially like this: function contains(arg1, arg2) { return NOT(ISERROR(search(arg1, arg2))); } But NOT and ISERROR are undefined even though using =ISERROR(...) works in the ...
4k views

### Validation based on multiple sheets

I have two sheets that each contain a column of data that I would like combined and used as a validation list for a column in another sheet. What would the formula look like for this?
40k views

I have a spreadsheet with 3 columns: | ITEM | COST | SUBTOTAL | Subtotal, for example, is 1000 to start, like: -------------------------- | ITEM | COST | SUBTOTAL | -------------------------- | ...
1 vote