Questions tagged [google-spreadsheets]
Google Spreadsheets is a part of the Google Docs web app suite. Questions about Google Spreadsheets are off topic and should be asked on webapps.stackexchange.com, unless they specifically involve your computer or browser.
477
questions
1
vote
1
answer
443
views
Count number of cells that match certain criteria with formulae
Suppose I have column with dates:
2015-01-10
2015-08-12
2014-01-02
2015-01-28
2016-10-14
I want to count how many dates fall to January. I would supopse that formulae is like this COUNTIF(A1:A20,"...
1
vote
2
answers
5k
views
Is there a tool to put a phone number into E.164 format?
I'm looking for both a quick and dirty, as well as easily repeatable, solution to strip non-numeric data from a column in a spreadsheet for import to a MySQL database. Example:
(123)456-7890 fred ...
1
vote
1
answer
6k
views
How to increment cell references by 7 rows for each row dragged in Excel
In Excel 2007 and Google Sheets, I'm trying to drag a formula to populate cells in a column, but I need each successive row to have the cell references incremented by seven rows. For example, drag ...
1
vote
1
answer
43
views
How to use countif to find specific formulas
I'm using Google Sheet. I have a bunch of =char(10003) to show check marks in some cells.
I want to use =countif to count them all.
I'm stuck at what to write for the criterion parameter.
How can I ...
1
vote
1
answer
2k
views
Is there a formula for calculating the difference in minutes or hours between two times?
Is there a formula for Is there a formula for calculating the difference in minutes or hours between two times in two columns?
In my spreadsheet I have the following two columns called start time and ...
1
vote
2
answers
203
views
How can I have a total row appear at the bottom of a table of a variable size?
I have a table with a variable number of rows. I'd like for there to be total row beneath it.
The rows are being generated by a function producing an array. So my current solution is to have enough ...
1
vote
1
answer
200
views
Function to calculate length of a string in Google Spreadsheets when the string contains certain UTF-8 characters
This function returns 6 and it's expected to return 4:
=LEN("🥎A🥎B")
What function should I use to get the "real char count" expecting that one symbol in the UTF-8 icon map is 1 ...
1
vote
1
answer
365
views
How to copy rows of specific columns based on a criteria (and then continuously update)?
I have 2 sheets, let's call them sheet 1 and sheet 2.
In sheet 1, I have some data where essentially at its core its a Scoring tally, something like this:
Name
Age
Gender
Score
Jack
12
Male
155
...
1
vote
1
answer
261
views
How to calculate max sum held over a month?
I need help with a formula that needs to calculate the max sum held at one time for each month.
In this example it would be 54 for March (12+13+14+15, held on the 29/03/2021). Month is determined by &...
1
vote
1
answer
75
views
Sum and lookup over range in spreadsheet
I have a google spreadsheet that looks like this
Items
Item
Value
A
10
B
12
C
15
D
20
Calc
Total
Items
A
A
B
A
A
C
C
C
In Total I want to have the a formula that sums the value of the ...
1
vote
1
answer
94
views
Microsoft Excel/Google Sheets - Update Adherence
I would like to produce a result wherein it checks if an update is done within 3 hours, it would be pass. If it goes beyond 3 hours, then it would be a fail. So this is how my raw data looks like:
...
1
vote
2
answers
118
views
Conditional formatting custom forumla for comparing value to another cell
I have a row (31) that contains various numerical values e.g: 1,000 or 25,000
I have a cell (H7) that contains a value e.g 20,000
I want to set a custom formula so that when any value in Row 31 is ...
1
vote
1
answer
412
views
EXCEL : Hyperlinked dynamic index
I found below formula to generate a dynamic index in spreadsheet.
=IFERROR(INDEX(Sheet!$A:$A,SMALL(IF(ISTEXT(Sheet!$A:$A),ROW(Sheet!$A:$A),""),ROW(Sheet!A1))),"")
Is there a way i could add ...
1
vote
1
answer
51
views
SUMIFS Using Multiple Ranges?
In the screenshot below, columns A-B are on one tab, D-F are on another, and finally H is on a tab of it's own. Columns A-B is a tab that I use to tally worked hours by each employee less any time ...
1
vote
2
answers
83
views
How can I sort out a list of numbers according to certain characters in the middle of these numbers?
So, at my job, we want to divide the work of checking certain items according to their registered numbers. Here's an example of these numbers:
0022499-80.2017
0200781-98.2016
0021322-13.2016
Items ...
1
vote
2
answers
4k
views
EPS for a market in Google Sheets
In Google Sheets, I can use the GOOGLEFINANCE function to get a number of stock data points including:
price
volume
eps
I have used the GOOGLEFINANCE function on market data points for the Dow (...
1
vote
1
answer
2k
views
Conditional Formatting using average function in Google Sheets/Excel
I am trying to figure out how to conditionally format a particular cell depending on the average value of a number of other cells. So I've tried a variety of solutions all either ERROR-ing or not ...
1
vote
1
answer
63
views
how to save time with multiple COUNTIFS possibilities?
I am sure that there is a smarter approach then this brute one:
=IF(COUNTIFS(O40<>P40;O40<>Q40;O40<>R40;O40<>S40;O40<>T40;O40<>U40;
p40<>o40;p40&...
1
vote
1
answer
1k
views
How to not change certain values when dragging in Google Sheets
I have a formula like below
=HYPERLINK(CONCATENATE('Code Repository'!B2, "/browse/master/", C4, ";", 'Code Repository'!B3, "$", D4), "Link")
I don't want 'Code Repository'!B2 and 'Code Repository'!...
1
vote
2
answers
77
views
Output matched value based on range
I have a spreadsheet with two tabs: one is a search term data from an Adwords campaign and another one is a list of text fragments.
I want to use a function to return check if the search terms ...
1
vote
1
answer
2k
views
Use series number as X axis label in Google Spreadsheet [closed]
When you create a line plot chart out of a column in Google Spreadsheet, it has its Y-axis marked with values from the data range, but its X-axis completely unmarked, which makes zero sense:
Note the ...
1
vote
1
answer
84
views
I need a formula in Google Sheets that does the following [closed]
I need a formula that:
Counts the number of cells that contain either 1 or .
Returns different numbers based on that count
The pattern is: the sum of
1 = 350
2 = 400
3 = 450
4 = 500
5 = 600
6 ...
1
vote
1
answer
12k
views
List of Subfolder names and file links in google sheets - script
I'm trying to get a Google Sheet list of file links in folders and subfolders which I'm keeping in Google Drive. It should look something like this:
I was googling and reading forums for a quite a ...
1
vote
1
answer
1k
views
Google Sheets - Conditional Formatting based on column heading of cell value [closed]
I've had a problem that's been bugging me for days and I can't quite figure it out. Scenario:
Names: This is an unsorted sheet where there are a bunch of names.
Team: This is another unsorted sheet, ...
1
vote
1
answer
2k
views
Extracting data from an excel sheet into another excel sheet
I have a sheet with certain data arranged like this:
X8 - something - something2
X12 - somethingelse - asdfg
X8 - asft - sastaryraavt
X8 - random - random2
I need to extract only and all of the rows ...
1
vote
1
answer
807
views
Excel If cell is blank show nothing else carry out this IF statement
I have the following formula
=IF(N37="Y",5,-5)
At the moment it shows -5.00 if the cell N37 is blank however I have it to display nothing until the cell contains N or Y.
1
vote
1
answer
2k
views
How to simplify Excel/Google Sheets formula with redundant method calls?
I have an Excel (Well, technically Google Sheets) workbook containing product inventory warehouse storage information. I figured out how to construct queries so it grabs the needed information from ...
1
vote
1
answer
686
views
Creating a table similar to pivot table
I'm not sure how to explain it, but I hope this example makes it clear.
Data:
Yes/No | Place1 | Place2 | Place3
----------------------------------
Yes | Thing1 | Thing2 | Apple
No | Apple | ...
1
vote
1
answer
39
views
How to make a formula to calculate how many of each unique item are in a column in the last 365 days
I have two columns A:A (dates) and B:B (items)
.....
I need to find a formula to calculate how many of each unique item are in column B : B in the last 365 days.
In the example the right value in the ...
1
vote
1
answer
7k
views
Copy every nth column on a specific row from one sheet to another (in Google Sheets)
I want a function to select every 2nd column on a particular row and display it in another sheet.
I have two sheets: Sheet1 and Sheet2
In Sheet1 A2 I have a list of numbers that start from 1 to 1000....
1
vote
1
answer
4k
views
add prefix to a cell in a spreadsheet
I have a cell in a (google)spreadsheet document (let's say L7) where I usually put a numeric value (like, say: "28", "12", "87"...).
I'm trying to make a formula that adds a letter in front (or ...
1
vote
1
answer
158
views
How to xml to xml parse
I am trying to automate this process.
I have link to this xml file. it has many parameters, but I am only interested in 3 of it's parameters. I want to use those 3 parameter and create new xml file ...
1
vote
2
answers
45
views
How focus on the document after find?
I'm filling a column after finding a row via a search on the name column. So I do CTRL + F, type the name, and click the the cell with mouse to fill it. Is there a way to do this without using the ...
1
vote
1
answer
152
views
How to visualize historical data
I have inherited a horrible spreadsheet with 4 years of data about when people participated in a given activity, by month. Each person would likely participate once every 18 months or so, but ...
1
vote
1
answer
6k
views
Google Spreadsheet Indirect in a VLookup [closed]
I'm trying to convert an Excel spreadsheet into Google, but can't get this VLookup(Indirect)) combination to work.
My data sheets are named with a code, for example "fred01", and look like this:
...
1
vote
0
answers
15
views
What's the best way to draft emails in Gmail that use variables from Sheets? [closed]
I'm want to draft emails in Gmail that incorporate variables from Sheets. Is there a free tool that does this, or am I better off writing a script?
Crucially, I need to look over the draft emails ...
1
vote
1
answer
95
views
replace comma separated values in a sheet uses SORT formula to get sorted results from another sheet
I have a sheet called "Form Responses 1" connected with a Google Form, and I made another sheet/tab and used this formula:
=SORT('Form Responses 1'!A2:M, 4, TRUE)
to sort the responses with ...
1
vote
1
answer
1k
views
Drag and a drop a range from one sheet to another sheet of the same workbook or spreadsheet in Excel/Google Sheet
I would like to move a range of cells that contains many formulas (some using absolute reference and some others using relative reference). If I just do Cut and Paste then the relative formulas will ...
1
vote
2
answers
439
views
Using XLOOKUP or FILTER to aggragate data into a single cell
I have a data set that is compiled by various people filling out a form to populate a spreadsheet on Google Sheet. Here's what the data set might look like:
A data set listing out name, collected ...
1
vote
1
answer
40
views
Excel vs Sheets: use case of rand() as autonomous trigger for sheet calculations?
I recently read about a fairly brilliant and novel use of the rand() function in order to produce (functionally speaking) real-time calculation on G Sheets independent of the sheet actually being ...
1
vote
0
answers
435
views
Google Spreadsheets: Find last unique value in column given some conditions [closed]
I have an excel like the attached below.
I want to find the last unique value (transaction) of column A, given that column I is > 0.
Any tip how to achieve this?
1
vote
0
answers
530
views
Override font smoothing (antialiasing) method in Google Sheets grid (Firefox)
How to override font smoothing used in Google Sheets grid to make fonts sharp (as everywhere else on a page)?
As you can see from the screenshot below, most of the elements use sharp font (standard ...
1
vote
0
answers
117
views
Copy + Paste a cell, keeping the cell reference the same but increasing the column increment by one
I'm working on a dashboard (Google Sheets) at work, which shows all the figures for 12 different sites. The sites are arranged in rows with columns used for different figures (revenue, bar sales, ...
1
vote
0
answers
63
views
Formula - Get previous date and sum of unique months based on unique user
Goal Estimate the next date Abbie will make the purchase based on average days.
Screenshot to give a better idea:
Questions
A.1 & A.2 - how to search for the most recent previous purchase date ...
1
vote
1
answer
192
views
Searching a value of partial text from another sheet
My scenario is, I have 2 worksheet:
Sheet1 contains the name of the restaurants, and Sheet2 contains the name of restaurants to avoid.
My issue is, I use a formula in conditional formatting that only ...
1
vote
1
answer
45
views
Excel/Sheets: Average only the input data in a range of data?
I've got an annual budgeting spreadsheet in which each month is listed along with given categories of items. I'm trying to find my average monthly expenditure on any given category, but don't know the ...
1
vote
0
answers
33
views
Optimizing sales in excel/google sheets
I have a table that looks like that:
investment
p1
p2
p3
1000
100
50
80
2000
200
60
95
3000
300
70
110
4000
400
80
125
5000
500
90
140
6000
600
100
155
7000
700
110
170
8000
800
120
185
...
1
vote
1
answer
67
views
Get a drop-down list from another sheet based on lookup
I have a list of CA in my Source sheet that looks like this:
And in my main sheet I need to get a dropdown list of "little a" for each CA, like this:
Main sheet:
Sheets:
For example: ...
1
vote
1
answer
375
views
Extracting text between two repeated sets of values in Excel
I have campaign names that conform to the following structure:
[Network] - [Language] - [Country] - [Channel] - [Campaign Type] - [Free Text]
This could look like:
Google - English - India - Display -...
1
vote
0
answers
73
views
How to show different type of legend below specific columns under X axis on stacked bar chart
I have drawn a stacked column chart in Google sheet. I want to put different legends below specific columns as secondary label under the x-axis; here is my spradesheet link: https://docs.google.com/...