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.
6
votes
2answers
4k 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 ...
4
votes
2answers
5k views
How to sum values in a range of dates in Google Docs Spreadsheets?
I have a Google Docs spreadsheet in the format below — here's an example.
Column A → Dates
Column B → Numbers
I'd like to sum values in column B in a range of dates in column A.
Example:
...
4
votes
1answer
194 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
1answer
206 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
243 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
4answers
2k 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 ...
4
votes
1answer
104 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
1answer
1k 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 ...
2
votes
1answer
124 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
2answers
2k 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?
1
vote
1answer
2k 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
1answer
120 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: intersection of two sets
1
vote
1answer
568 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
3answers
136 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 ...
1
vote
1answer
992 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
1answer
3k views
Google Spreadsheet Apply Formula to Newly Inserted Row
I have a spreadsheet that keeps a running balance of a credit card with the newest transactions at the top (just like credit card web sites).
An example of the formula for each cell in the Balance ...
1
vote
1answer
19 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 ...
0
votes
1answer
1k views
Leading zeros in google spreadsheet automatically removed
I have a column in which all inputs are numbers. But these numbers are actually not real numbers. Not real in the sense, 050 in this case is very different from 50
The problem is that whenever I add ...
0
votes
1answer
216 views
importxml is not working properly -Google spreadsheet
find attached link for my Google spreadsheet in which i tried to pull out the data from booking.com.
i retrieved Hotel name in column. it successfully retrieved the hotel name. but not work with ...
0
votes
2answers
766 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
...
0
votes
1answer
1k 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";; ...
0
votes
2answers
59 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 ...
0
votes
1answer
35 views
How to get the Elapsed time of two cells hh:mm in Google Spreadsheet
In columns K and L I have time stamps.
Let's say 03:30 is in column K and 04:30 is in column L.
I want the total elapsed amount of time in hh:mm in column P.
I have tried the average function, ...
