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.

Filter by
Sorted by
Tagged with
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,"...
dma_k's user avatar
  • 386
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 ...
Thufir's user avatar
  • 1,610
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 ...
Petros's user avatar
  • 21
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 ...
Saeed Neamati's user avatar
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.21 gigawatts's user avatar
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 ...
Jaz's user avatar
  • 11
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 ...
Xavi Montero's user avatar
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 ...
DoubleRainbowZ's user avatar
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 &...
vaidab's user avatar
  • 23
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 ...
Ray Britton's user avatar
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: ...
MysteriousPlantain's user avatar
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 ...
Fran's user avatar
  • 11
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 ...
Suyash's user avatar
  • 11
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 ...
Robert Seal's user avatar
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 ...
Eduardo's user avatar
  • 11
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 (...
Jacob's user avatar
  • 113
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 ...
Shiv's user avatar
  • 13
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&...
gamer0's user avatar
  • 945
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'!...
TechCrunch's user avatar
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 ...
Bogdan's user avatar
  • 111
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 ...
alamar's user avatar
  • 143
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 ...
Dimitri Stukalov's user avatar
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 ...
Josh's user avatar
  • 11
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, ...
Flynn's user avatar
  • 200
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 ...
crownusa's user avatar
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.
Phil Nind's user avatar
  • 121
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 ...
Mac Sigler's user avatar
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 | ...
Aarjav's user avatar
  • 113
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 ...
Guglielmo Lauro's user avatar
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....
Dur's user avatar
  • 17
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 ...
user430416's user avatar
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 ...
user206168's user avatar
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 ...
perreal's user avatar
  • 675
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 ...
digitaljoel's user avatar
  • 2,723
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: ...
Cylindric's user avatar
  • 703
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 ...
Sebastian's user avatar
  • 121
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 ...
Abdulrahman Mohamed's user avatar
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 ...
Faustin Gashakamba's user avatar
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 ...
hyrshe's user avatar
  • 13
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 ...
Arctiic's user avatar
  • 1,075
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?
seralouk's user avatar
  • 111
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 ...
o.v's user avatar
  • 321
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, ...
Peter Mulholland's user avatar
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 ...
wendy's user avatar
  • 11
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 ...
M.M's user avatar
  • 11
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 ...
BudgetingInNeed's user avatar
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 ...
Lakerda's user avatar
  • 111
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: ...
Alpe Popas's user avatar
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 -...
Balaji Bhat's user avatar
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/...
paopao's user avatar
  • 11

1 2
3
4 5
10