# Tagged Questions

26 views

### Conditional Cumulative Sum using Arrayformula

I saw a nice solution for the cumulative sum problem here: Cumulative Sum without Script My question is how do you make this conditional? I mean cumulatively sum only numbers that belong to a certain ...
24 views

### Counting specific words

I want to count how many times in a column certain words from a predefined set appear. In this example those specific words will be days of the week. For example: Mon =3 Tues ...
20 views

I have a Google Sheet, with the first row container headers (so row references will be -1) and rows are "grouped" by every 7 rows. See below. I've manually typed the numbers into the Image # ...
61 views

### How to interpolate data in a range in Google Sheets

I have an array with data: X Y 3 50 5 60 9 120 11 130 18 90 20 150 The data is entirely non-linear. X is guaranteed to be sorted. Now for any given ...
33 views

### Which formula sums all values from a column in a line that contains a specific word?

Let's use this random spreadsheet to illustrate my problem. What I want to do is a sum of all Page Views (F) that contains the Category (B) named /Online Computers. Which formula can I use to do ...
39 views

### How to summarize many-to-one table in Google Sheets

I've got two separate sheets we'll call 'Sheet A' and 'Sheet B' with a table on each. The table on 'Sheet A' has a Many-to-one relationship to the data on the second sheet. On the second sheet, I'd ...
26 views

### How do I refer to a range in another sheet?

Sheet 1 has numeric values in the B column. I'd like to sum them into a single value within Sheet 2, preferably without having to copy them individually first. According to the docs, I can copy a ...
128 views

### How do I combine a cell value with a string in a Google Spreadsheets formula?

I have the following IF condition in some cell: =IF(A1>A2, "value is <C5>", "value is <D5>") where <C5> and <D5> should be the values of cells C5 and D5. I know it is a ...
74 views

In the new Google Spreadsheets, I'm trying to make a simple spreadsheet which will assign points to my salesmen regarding some actions they complete. I would like to be able to add a new row below ...
82 views

### How do I multiply column values?

I simply want to multiply column h with column j, and store the result in column k in each row. What do I type in as the formula to do this?
79 views

### How do I output a string containing quotation marks?

I am not super computer literate. I am trying to use the equations to grade my Google quiz, the problem is that there is a quote in the answer. Is there a different symbol I need to use? This is what ...
196 views

### Dynamic generation of cell list / array from range value in Google Spreadsheets

I want to create an array/ a list of cells (i don't know the exactly terminology), in rows or columns form, covering the range between 2 numbers Fox example, I have 2 cells, A1 = 3; B1 = 10 I want ...
264 views

### Can I use column headers in a =QUERY?

Reading the documentation for the =QUERY function1, 2, 3, some of it seems to imply that I should be able to use column headers directly in my query. For example, the =QUERY function takes a third ...
53 views

### Import info from multi tabs in a spread sheet to one sheet

I'm looking to move data from "sub sheets" to the main sheet. I know I've done this before but it's been 5 years since. So here's what I'm looking for: ...
56 views

### A function opposed to SUMIF?

I have a huge list of matches between clubs and I want to calculate the goals for and the goals against. I could find a way to calculate the goals for, using: =SUMIF (A35:A46, "Liverpool", B35:B46) ...
90 views

### Multiple conditions look up

I am having trouble building a spreadsheet, the problem its my data table. I want to be able to pull data from the table, something that will match data from column A, and column B (on the same row) ...
112 views

### Using “if” (or other function) to recognize a string and sum values

I started programming just a few weeks ago, got a grasp on the concepts and been evolving on it, since I have certain activities with spreadsheets, I decided to take it to the next level and got stuck ...
176 views

Is there a function that will tell if a specific cell has a plain (manually entered) value or has a formula?
194 views

### Google Sheets - How can I use row values to index into another sheet?

I have an arbitrary google spreadsheet Data. alpha | 0.1 beta | 0.2 gamma | 0.3 delta | 0.4 epsilon | 0.5 And another sheet in the same file, Data Subset that holds an arbitrary ...
42 views

### Remove word values from the output of UNIQUE(SourceArray) in a Google spreadsheet

I am trying to list all the "other" options of a Google form. As such, I am using UNIQUE to list all results from a row so: =UNIQUE('Form Responses'!S2:S) I would the want to exclude the following ...
95 views

I have the following formula in a sheet that works in Excel but comes up with an error in Google Spreadsheets. =(IF(A6<=TODAY(), INDEX(\$AS\$2:\$AU\$12,MATCH(A6,\$AS\$2:\$AS\$12,1), ...
476 views

### How do I prevent that references in my formula change when I copy the formula to a new cell?

I'm trying to use submitted Google Form data in one spreadsheet to locate and retrieve data from another spreadsheet (tab in same document). The Google Spreadsheet file is here. The =QUERY statement ...
136 views

### Use of importdata with arrayformula

Iâ€™m using importdata to retrieve Yahoo stock information, which works fine on indivdual rows, but when combined with arrayformula to fill down the page when additional symbols are added does not ...
215 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 ...
359 views

Is it possible to sum a range of cells while ignoring rows which are not hidden? Thanks
218 views

Example: column A1 is \$35.00. I want column B1 to be a percentage markup value dependent on the value in A1 (i.e.: 75%). So if A1 is between \$1 and \$10, B1 = 75%; if between \$11 and \$20, B1 = 60% and ...
640 views

I have data like in the following table: +---+-----+-----+ | A | B | C | +---+-----+-----+ | 0 | 5 | 30 | | 1 | 0 | 30 | | 0 | 15 | 40 | | 1 | 0 | 40 | | 1 | 10 | 50 | ...
1k views

I am using the following formula in a Google spreadsheet to calculate the number of days between two dates (dates are in columns G and AL). The problem is that some of the cells in col AL are blank, ...
2k views

### In a Google spreadsheet, how do I list unique items, modified by a regexp?

I have a Google spreadsheet with items and prices. Column D has the prices, column C has item names, some of which contain multiple words. When an item has multiple words, only the first one is ...
558 views

### How to count the occurrences of 'STRING' in a row, but only if that row begins with 'IFTEXT'

I have a sheet with employee time logs, as follows: Name Day1 Day2 Subtotal of 'OFF' days this period Jess PRESENT OFF 1 Bob PRESENT PRESENT 0 Name Day3 ...
7k 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 ...
409 views

### “Yes” if a value partially matches in a range of values, blank otherwise

I have a column of values (column A for this example) like such: Phytophora sojae Phytophora ramorum Cryptococcus neoformans Coccidioides posadasii And I'd like to create a new formula column (B) ...
2k views

### Google Spreadsheets formula question: do not include cell in a formula if blank

I am a music teacher and I use the following formula to calculate my students performance grades: =100-SUM(F3-5)*-7 How can I change the formula so it only calculates a grade if there is data in ...
8k views

### COUNTIF but when multiple conditions are met

I'm trying to create a GDocs spreadsheet to collate data from a survey. I'm fairly new to the advanced features of GDocs/Excel but here's what I'd like to achieve. I've tried Googling but it's a tough ...
2k views

### Concatenating only filled cells

I currently have a spreadsheet to keep track of scores in a card game. There can be between 2 and 5 players. I have the following: | Players | |----------| | Dave | | Paul | | John | | ...
13k views

### IF OR Statements

How do you do an IF ... OR ... THEN statement in a Google spreadsheet? In PHP I would do this: if(\$C7 == "U.S.A." || \$C7 == "Canada"){ ... How to do the same as a spreadsheet formula? At the ...
16 views

Possible Duplicate: How do I link a cell in Google Spreadsheets to a cell in another document? Is it possible with a Google Spreadsheet to reference a cell to another spreadsheet using a ...
6k views

### How do I use the INDEX or OFFSET functions in Google Spreadsheets?

I have my spreadsheet set up as follows: first 10 columns full of descriptive data followed by 24 columns of month data (2 columns per month). The last 24 columns are static and the values are ...
2k views

### How to apply a function to all the rows preceeding the current one in an arrayformula?

So I have the following situation: In the first column I have certain values, in the second column, given a certain row, I want it to contain the result of a function applied to all the preceeding ...
395 views

I want to write a formula where I can add values to a cell whether another cell states approves the "transaction". It will be easier to explain with an example. Consider a spreadsheet containing the ...
189 views

### How can I have column E copy its data into the right column based on the initials in column M?

So what I have so far is a spreadsheet that will auto time stamp column N when my technicians enter their initials into column M. Now the 2nd thing I would like the spreadsheet to do is. When I ...
515 views

### Referencing a cell range relative to the bottom of a sheet

I am developing a "Daily Personal Inventory" spreadsheet based off a related Lifehacker article. I've successfully made a few modifications based on my own circumstances/goals, but there is one ...
4k 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 ...
2k 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 ...
819 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 ...
15k views

### Adding one Google Spreadsheet 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 Docs should work for that, but so far everything ...
2k views

### Google docs spreadsheets: 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 that isn't exactly synced up with the right lines as much as I'd like: I want to duplicate the column A and D values down into any empty spaces/rows. ...
5k 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 ...
50k 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 ...
4k 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. ...