This tag is for spreadsheet questions where the question or answers involve an in-the-cell worksheet function, as opposed to some form of code.

learn more… | top users | synonyms

11
votes
2answers
9k 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 ...
5
votes
3answers
10k 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?
5
votes
1answer
58 views

How to refer to a cell in the same column at given row?

I would like to get a cell in a particular row above the current cell. This is to be used in conditional formatting, so I can't specify a particular cell, e.g. A$4—the reference would be the same for ...
5
votes
3answers
754 views

How do you replace a formula with its result?

I created a Google Spreadsheet that is made up of 20+ sheets (all formatted the same way). The company I work for loads containers. One of those sheets calculates a particular parameter on each of ...
5
votes
1answer
469 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 ...
4
votes
1answer
837 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 ...
4
votes
2answers
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 ...
4
votes
1answer
431 views

What formula would I use to calculate monthly cost (Google Docs)?

I'm trying to make a document on Google Docs 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 ...
4
votes
1answer
137 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. ...
3
votes
3answers
4k views

How to get the intersection of two sets

I have two lists (unique sets) in a spreadsheet and I want to get the intersection of the two. Is there an easy way to do this? Here's an example of what I want to achieve: List 1 List 2 ...
3
votes
1answer
34 views

How to create multiple columns from one single column?

I've got information that I gather from emails, from where I just take the lines, pass them through a filter and drop them into a Google Spreadsheet sheet. It ends up looking like this: Element 1: ...
3
votes
1answer
25 views

Google Spreadsheet Formula to show heading

I have a Google Spreadsheet in which I am comparing projectors. Basically it has formulas to divide the bulb life/cost and contrast ratio/cost. Then it adds the total of the two and gives me a ...
3
votes
1answer
64 views

Google Sheets notification triggered from function result

I have a google form that populates a spreadsheet with a timestamp and two numbers. For technical reasons I can't have the numbers passed directly into a database but they need to be copy-pasted ...
3
votes
1answer
313 views

Is GOOGLETOURNAMENT defunct?

I do not follow the NCAA Division I Basketball Championship but am interested in the accuracy of current advice re Google Spreadsheets. One of the functions is GOOGLETOURNAMENT which, rumour has it, ...
3
votes
3answers
4k views

Google Spreadsheets: Excluding Negative Values

Is there a way to get a function to return any result below 0 as 0 or not include negative numbers in sums? I'm using it to track my total overtime through the year at work. So right now I have it so ...
3
votes
4answers
4k 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 ...
3
votes
2answers
107 views

Google Spreadsheets: Identifying and Working with Any Cells that Match

I am trying to find a function, or a series of functions, that allow me to (1) Identify if any cell in column A matches any other cell in column A, (2) add numbers in Column B that occupy the same ...
2
votes
1answer
54 views

Have formula copied to entire column in Google Sheets

I'm trying to make a spreadsheet that displays the average of a row, i.e. =average(c3:al3) and the function is in column AN3. I want to copy that function down a row (but need to do this for 560 ...
2
votes
1answer
42 views

Forms Data Manipulation In Google Sheets

I'm trying to develop a solution using Google Forms with Google Sheets that will collect Google Forms data and then automatically divide that one form submission's data into multiple rows of data in ...
2
votes
1answer
42 views

Formula which will grab specific cells and concatenate them together but only if they are not blank

So I have this formula currently, but it breaks when nothing is in any of the cells. I'm expecting this of course, as I haven't told it to do anything when ISBLANK is TRUE. So my question is, am I ...
2
votes
2answers
3k views

Import Google Spreadsheet range if a column contains a particular value

I want to import a range of data from one Google Spreadsheet (1) into another (2), but only when Column J in spreadsheet 1 contains a 'yes'. I'm using the following formula: ...
2
votes
1answer
12k views

Using INDIRECT() in sheet reference range

I have a separate sheet with some data where a user's input will reference the row number of a sheet. I'm trying to reference that sheet by inserting the column letter and taking a number input from a ...
2
votes
1answer
138 views

In a Google Spreadsheet, show all combinations for a selection of columns

I have a sequence of letters, in a Google Spreadsheet: | A B C D E F --|---------------------- 1 | U Y 0 4 X N 2 | H X Y 3 | W The first row contains a ...
2
votes
2answers
39 views

Sum all expenses of a given type given a list of expense types in a separate sheet

I use Google Sheets to keep track of my expenses. The file currently consists of: Sheet 1: "Expense Types" Sheet 2: "2015 Details". The first column of Sheet 1 contains the list of admissible ...
2
votes
1answer
134 views

Formula in Google Sheets to lookup contents of another cell

I'm trying to create a formula that brings back a certain cell depending on what another cell has in it. But the formula I'm trying isn't working. It's working for other things (what I thought was the ...
2
votes
1answer
1k views

How to stop Now() function from auto-updating?

I'm using Google Spreadsheet's now() function as a shortcut to insert the current timestamp now. However, the value in the cell changes whenever the spreadsheet is modified in any way, even when the ...
2
votes
1answer
29 views

How to consolidate information from different sources in Google Sheets

So I have a Google Sheet with several sheets (tabs) in it, like so: Sheet 1 - Weapons Legend A B C D A = Date 5/3 John Sword 10 B = From 6/3 ...
2
votes
1answer
88 views

Google Spreadsheet limit ArrayFormula

I'm using the ArrayFormula to calculate multiple ranges and it's slow because it's trying to calculate everything in that range, even though it has nothing in it. Is there a way I can limit the ...
2
votes
1answer
80 views

Calculate total based on drop down criteria

I am creating a budget that has different expenses on one worksheet (Expense_Overview). On another worksheet, I have a list of expenses (Expense_Detail) I am entering that are categorized based on a ...
2
votes
1answer
172 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. ...
2
votes
0answers
12 views

Is it possible to import cell data AND cell format from one Google sheet to another? [duplicate]

I'm trying to import data "and" formatting from one sheet to another. I'm using =importrange("docID", "sheetName!A1:A30") to get the data, but it doesn't get the format. Suggestions?
2
votes
2answers
434 views

How to reference a fixed sheet name in Google Spreadsheet

I am doing reports from a set of data in Google Spreadsheet. As of now, I have several sheets, among them Raw Data and Processed Data. In my Processed Data sheet, I use formulas such as: SUM('Raw ...
1
vote
1answer
4k 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 | -------------------------- | ...
1
vote
2answers
39 views

Function name to add rows in Google Sheets

Is it possible to add rows with a built-in function (such as MIN() or INDIRECT()) in Google Sheets? I would like to fill rows in the main sheet such as +---+----+--------+ | A | 18 | Atext1 | | A | ...
1
vote
2answers
52 views

Formula result depending on the week of the day

I've got a bunch of cells that have formulas to display the day of the week according to the date above it. (I'm using Google Sheets by the way) I want the cell underneath the day of the week that ...
1
vote
1answer
37 views

Using IF Formula to get data from another Cell

I am just trying to use the IF formula: COLUMN A COLUMN B COLUMN C Cats NULL Dogs Monkeys Kittens NULL ...
1
vote
1answer
33 views

Dynamic Values in a formula?

I have a google sheet where I am using this query to pull data from another sheet : =QUERY(IMPORTRANGE("SHEETLOCATION","Master Customer!A1:u10000"),"Select Col3 Where (Col18='Survey Complete' and ...
1
vote
1answer
60 views

Google Sheets list key with highest value in key value pair

I have a some data in key-value pairs setup like so: A B 1 Key: Value: 2 Cars 5 3 Bikes 4 4 Vans 9 5 Trucks 6 What I can't seem to ...
1
vote
2answers
914 views

In Google Spreadsheets how do I select column 1's value if an input value is between the integers in colums 2 and 3 for a range?

In a Google Docs spreadsheet 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 ...
1
vote
1answer
18 views

Use the order of sheets in which they appear in a Google spreadsheet

Goal What I would like to accomplish is to have my aggregation-master-schedule-sheet (see context) put schedules in the same order that the sheets are. I.e. the left most sheet is 1st, the 2nd ...
1
vote
1answer
24 views

How can I import the whole sheet to another Google spreadsheet?

I have a sheet (sheet1A) in Google spreadsheet A, and another spreadsheet - spreadsheet B Now I want to have another sheet on B - called sheet1B - that's totally the same as sheet1A, with data get ...
1
vote
1answer
184 views

How do I find what is in a particular cell on the same row as another on based on a match?

I have the following query in Google Sheets: =IF(ISERROR(MATCH(A2,Sheet1!A:A, 0)), "No Match", "Match on Row " & MATCH(A2,Sheet1!A:A, 0)) I want to return the value in column C of the matched ...
1
vote
1answer
360 views

Calculate difference between numbers in 2 columns row-by-row in a spreadsheet

I have 3 columns in a Google Docs spreadsheet: A) Inventory purchased price, B) Inventory sold price, C) Difference between A and B (i.e. profit or loss) Data is going to be entered manually into ...
1
vote
1answer
61 views

Search for multiple variables in multiples rows at the same time in Google Sheets/Drive

I'm using Google Sheets (only option, sorry, know this should be a database). Sheet 1: List of 200 employee names, and up to 10 location codes they are frequently assigned to. Sheet 2: List of 50 ...
1
vote
2answers
286 views

How can I make a Google spreadsheet Function reference one column's value and adjust it to a different Value?

I am trying to create a spreadsheet that will calculate varying sizes. I need it to reference a key where a letter is an established size such as H=1984, S=672, etc. so as that that column can be ...
1
vote
1answer
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: ...
1
vote
0answers
12 views

match and lookup function on google sheets match different japanese kanji

Checkout this google sheets: https://docs.google.com/spreadsheets/d/1dw0v_64uN2lCyB7FxfoBmQ5d1OVIBEemxe5TtPaiUFY/edit?usp=sharing Despite the fact that those two kanji characters are completely ...
1
vote
0answers
30 views

Google Sheets: How to print information of user that is printing

Well, I am creating a sheet on Google Sheets and I will share it with some people. The users online can see its contents, but, to maintain the data update information when someone prints that, I ...
1
vote
1answer
32 views

Link in Google Sheets to either create a new document or link to the one that was previously created

I'd like to provide a column in a spreadsheet that allows the user to create a note for the column by clicking on a link. If the note has never been created, it will create the link to a new document ...
1
vote
2answers
378 views

How do I get the data in a cell of the last column of a row?

I'm using Google Sheets. Every week I add a column with new data, and I need the data from the latest column copied into another sheet. The formula needs to pull the cell of the last column in the ...