Questions tagged [formulas]

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

Filter by
Sorted by
Tagged with
2 votes
3 answers
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 ...
user avatar
  • 29
43 votes
4 answers
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 ...
user avatar
  • 541
1 vote
1 answer
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:...
user avatar
0 votes
2 answers
4k views

Sum of numerical values excluding formulas in a Google Spreadsheet

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'...
user avatar
0 votes
2 answers
4k views

Is there a limit on functions in Google Spreadsheets? [duplicate]

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 ...
user avatar
  • 111
2 votes
1 answer
6k views

JOIN formula in Google Spreadsheets

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 ...
user avatar
34 votes
9 answers
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 ...
user avatar
  • 501
15 votes
4 answers
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 ...
user avatar
2 votes
2 answers
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))
user avatar
  • 121
1 vote
2 answers
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 ...
user avatar
  • 138
1 vote
1 answer
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 ...
user avatar
-4 votes
1 answer
2k views

Simple row references in Google Spreadsheets and resorting data

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 ...
user avatar
13 votes
2 answers
59k views

Calculating shaded cells

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 ...
user avatar
  • 131
-1 votes
1 answer
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";; "/...
user avatar
  • 2,835
2 votes
6 answers
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 ...
user avatar
  • 185
10 votes
2 answers
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 ...
user avatar
  • 248
2 votes
1 answer
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 ...
user avatar
  • 248
1 vote
1 answer
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 ...
user avatar
  • 113
2 votes
1 answer
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. ...
user avatar
  • 123
14 votes
2 answers
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"...
user avatar
12 votes
1 answer
11k views

Retrieve a cell having a column and a row header in Google Spreadsheets

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 ...
user avatar
  • 121
-1 votes
2 answers
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 ...
user avatar
  • 131
12 votes
5 answers
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 ...
user avatar
1 vote
2 answers
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 ...
user avatar
7 votes
1 answer
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 ...
user avatar
  • 594
4 votes
4 answers
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 ...
user avatar
3 votes
2 answers
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. ...
user avatar
4 votes
2 answers
2k views

Google Spreadsheet conditional counting function

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 ...
user avatar
23 votes
3 answers
9k views

Complete reference documentation for Google Spreadsheets formulae?

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, ...
user avatar
  • 527
9 votes
3 answers
23k views

Are Excel formulas and Google Spreadsheet formulas the same?

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?
user avatar
  • 227
4 votes
1 answer
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. ...
user avatar
  • 143
4 votes
1 answer
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 ...
user avatar
6 votes
1 answer
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 ...
user avatar
2 votes
1 answer
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?
user avatar
2 votes
2 answers
40k views

Google Spreadsheet formula, MINUS?

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

Take SUM of colored cells in a column in Google Spreadsheets?

I have a column of numbers, where I've changed the background color of some of the cells to blue. All of the other cells in the column have the regular white background. Is it possible to create a ...
user avatar
  • 159
57 votes
6 answers
132k views

ISBLANK() function

Is there any way I can check whether a cell is empty? There is a function ISBLANK() but it returns false when there's a formula in a cell even thought the formula doesn't return anything (or an empty ...
user avatar

1
33 34 35 36
37