# Tagged Questions

43 views

### Complex Sum Function in Google Sheets

Apologies if this is lame, but I can't get a proper value using =SUM to perform order of operations on an employees hours (he has gotten two raises since starting after surpassing expectations). The ...
143 views

### Applying formula to lookup value in array of cells in Google Form Responses

I have a Google Form response sheet, part of which contains several columns (say B:G) that will only have a single value between them. The form asks respondents for their county of residence and then ...
356 views

### Decimal conversion of time values

What is or can it be done to add 2 cells, that return a time equation? I deal with multiple time entries and having to enter them in a standard format of 4:45 hours or 4:25 hours. I have a decimal ...
69 views

### Average two numbers IF the second number exists

I have 2 columns (Google sheets) of numbers similar to: 10.453 12.178 12.654 0 The third column is: if number higher than 0 exists in both columns, display an average of the two else just ...
1k views

In Microsoft Word, it is possible to use very simple formula e.g. =SUM(ABOVE) to display the total of a column or =SUM(LEFT) etc. Is it possible to do something similar in Google Docs? Obviously this ...
68 views

I'm trying to make a worksheet that gives graphs of data in other worksheets. I had the idea that the user could choose which worksheet they want to use. For example, there are 3 worksheets with data ...
2k views

Is there an option to add a time picker on Google Docs Spreadsheets? I don't need timezones or anything complicated. Just a string on the HH:MM format, that matches the custom format available. I don'...
167 views

### Google Sheets - Auto update table and auto add new data entries

I have a table in wich raw data is entered manually, with info about clients and the requests they made. And then I have this table in which I'm trying to analyze a bit the raw data (# of requests ...
260 views

### Can I use ArrayFormula with CountBlank or IsBlank if so how?

=IF( OR(COUNTBLANK(\$F3)>0, COUNTBLANK(\$G3)>0, COUNTBLANK(\$H3)>0, COUNTBLANK(\$I3)>0), "Missing Values", \$E3+(index(LSI_Index!\$B\$4:\$B\$40,MATCH(VLOOKUP(\$G3, LSI_Index!...
139 views

### How can I add hours in Google Sheets to show days?

I have 2 cells in Google Sheets with values: 22:00:00 22:00:00 and I have a cell which has the function sum to sum these values and it shows the value: 20:00:00 I am guessing it is trying to ...
3k views

### How to skip blank cells in Google Spreadsheet/Excel when copying a range from source to destination by Formula

I have three levels of hierarchical data in Google Spreadsheet that looks something like this: As data is being entered in here, I want it collect in three separate sheets like this: And The ...
707 views

### How can I COUNT rows with ARRAYFORMULA?

I have a sheet that lists incidents in an Incidents sheet: | Date | Name | Incident | |------------|-------|---------------------| | 01.01.2015 | Vidar | Car broke down | | 01....
114 views

### How to get the count of the current sheet?

I have a spreadsheet that I have spread out over many sheets. However, I could automate the filling out of some simple data by doing some simple calculations. I could program something simple like ...
720 views

### How to nest ImportRange into the query function in the new Google Sheets

We have applicants who come in and fill out a form via Google Forms. I have fields that auto-populate from the Response tab into a separate tab that is organized to fit our trackers. I am looking for ...
424 views

### ArrayFormula to compute Running Average for groups of rows

Is it possible to write an Array Formula which calculates the running average of the Amount column for each of the Name groups in the following sheet? The rows are sorted by Name. In Column C, I ...
675 views

### Find the closest value (equal or greater) in a range in Google Spreadsheets

I have a range with a list of values (depths). I'd like to be able to select the row where the depth matches a value in a cell I provide. If no value matches, the greater value should be selected. ...
2k views

### Which formula extracts the domain name from an email address (bob@example.com -> example.com)?

Which Google Spreadsheets formula do I use to get the domain part from an email address? Example: bob@example.com → example.com
197 views

### COUNTIF in Google Sheets that checks 'if' a string is in one column before counting in another column

I'm trying to use the COUNTIF function in Google Sheets. The Goal I want the 'dashboard' sheet to display the count of items in column F submitted to the responses sheet 'if' a specific string ...
110 views

I have a 'dashboard' sheet and a 'log' sheet. I'm trying to pull data from the 'log' sheet and perform calculations on the data before displaying it in the 'dashboard' sheet. The 'log' sheet will have ...
103 views

I'm having problems using the SUBSTITUTE function as part of a formula. On it's own, SUBSTITUTE returns the desired output (i.e. in cell O3), but when combined in a formula, 0 is outputted (i.e. cell ...
347 views

### Can I subtract a cell value in another cell that already have one in Google Spreadsheets?

Let's say that I have two cells A1 (time needed) and B1 (time spent). I want that when I enter a value in B1 the value of A1 will be reduced automatically. I want that in some way I could enter in ...
355 views

### Return value in column A in preceding cells in column B using an Array Formula

I've been looking for a solution to this for days now. How do you fill cells in column B (using an array formula) that are preceding the cell in column A with the value of column A only when cell in ...
1k 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 ...
44 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 ...
395 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 # column,...
3k 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 ...
64 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 ...
272 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 ...
79 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 ...
6k 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 ...
220 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 ...
2k 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?
2k 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 ...
668 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 ...
4k 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 ...
80 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) +...
2k 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) ...
168 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 ...
3k views

### Can I have formulas in a Google Form, not just the worksheet?

We use a Google Form for registration for our charity road race. I need to have two fields that contain formulas so people registering do not have to do the calculating. They can order t-shirts. ...
1k views

Is there a function that will tell if a specific cell has a plain (manually entered) value or has a formula?
325 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 ...
44 views

### Is there a way to do collaborative equation editing in a Google Presentation?

Since Google Docs now forbids equation in presentations (you cannot drag equation from doc anymore according to this question). Is there a way to include equations in a Google Presentation?
103 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 ...
2k 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 ...
278 views

### Use of importdata with arrayformula

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

Is it possible to sum a range of cells while ignoring rows which are not hidden? Thanks
434 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 ...