Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more

Questions tagged [google-sheets]

For questions about Google's web application to create and edit spreadsheets on a web browser or app. Don't use it for the Google Drive Excel files editor. Please consider adding data as markdown table to your question; this will greatly increase the chance that you get a satisfactory answer.

Filter by
Sorted by
Tagged with
0 votes
1 answer
14 views

Show zero value if result is NA in query google sheet

It showed #N/A if "Cash" is not in list. How can I show result "Cash is: 0" instead of #NA?
Sok Dara's user avatar
-1 votes
0 answers
11 views

Need Help with Building a Google Sheets Time and Earnings Tracker Template

I would like to build a google sheets template for my own use. I want to track my work hours spent and also the earnings based on hours spent. I do not want to track clock-in/clock-out. Simply want to ...
Circumference's user avatar
0 votes
2 answers
20 views

What's the fastest way I can open the Google folder in which the spreadsheet is located?

I have opened a Google spreadsheet. What's the fastest way I can open the Google folder in which the spreadsheet is located? Fastest way means with the fewest clicks.
Franck Dernoncourt's user avatar
0 votes
0 answers
7 views

I need to be able to generate a list based on critera from one column that matches a value I type into a specific cell [closed]

I have a list of locations in the UK. I need to create a list in a column of all the locations within a certain county. I need to be able to type a different value in a cell based on what I need. For ...
Victoria Cichocki's user avatar
0 votes
0 answers
18 views

Google Query function not returning any answers [closed]

I have a datasheet with Dates and individual information. My query function uses drop down boxes - one is a date and the other is text info. When I run the query with the date and the text info ...
willie's user avatar
  • 1
0 votes
2 answers
14 views

Sort data by number in cell containing number sometimes followed by text

I'm working on a shopping list with aisle numbers in Google Sheets and the aisle numbers can be digits alone or digits followed by a letter. For example 1, 2, 3, 3, 4, 6A, 8, 19B, etc I want to sort ...
JeffK627's user avatar
-1 votes
1 answer
26 views

Breaking up long column array into different columns

I want to be able to copy and paste an order of draw (starts with the draw number and school name, then each proceeding row is the athletes names) which is just a long array in a single column and ...
AnonymousTurtle's user avatar
0 votes
2 answers
25 views

How to use Array with Sumproduct or other formula

I am trying to use an array formula like SUMPRODUCT to perform a lookup with 2 criteria. I have a sheet named data that stores customer-specific pricing for products. A B C             D 1 ...
Anakin Teow's user avatar
0 votes
0 answers
10 views

MORE DETAILED explanation of ERROR: Array result was not expanded because it would overwrite data in B6 [duplicate]

Here are screenshots from my spreadsheet that has the issues I discussed in the previous question: IMPORTRANGE(,) works in a single cell, however, when adding the same formula to the next cell in the ...
Newton's cat's user avatar
-2 votes
2 answers
36 views

Formula for adding value in each cycle for compound interest

In Google Sheets, I know the formula for compound interest, for example: 10000*(1+10%)^n 10000: start-up cash 10%: interest rate n: interest cycle But what if I also add 10000 cash each month? What ...
Yu Lun CHEN's user avatar
0 votes
1 answer
16 views

How to sum an answer tracing upwards until it hits an positive value?

Thx for coming in! I need a formula to trace and sum the value in a column, upwards, until it hit an positive value. For the following table, A will have the answers, and B will be the column where I ...
Yu Lun CHEN's user avatar
-2 votes
1 answer
48 views

Solving ERROR: Array result was not expanded because it would overwrite data in B6 when using IMPORTRANGE

I am trying to make a Google Sheets book to keep track of my schedule. When importing data across sheets, I used IMPORTRANGE(url, range) to import data from Sheet1 to Sheet3. This worked at first; ...
Newton's cat's user avatar
1 vote
2 answers
25 views

Importrange Query importing correct OR statements, but not refining by date

I have a formula pulling relevant columns from a different document. It is working well except for the last part regarding the dates. It is seemingly ignoring that part of the formula and returning ...
Salt_Tea's user avatar
1 vote
2 answers
22 views

Count based on drop-down selection criteria

I have a sample dataset similar to the screenshot below in column C:E. I am trying to have a formula that will return the total count (B9) based on the dropdown selection in A2. If 'All Items' is ...
Elaine's user avatar
  • 43
0 votes
1 answer
16 views

How do I set up conditional formatting for values that contain text from a cell in another tab?

From similar problems I found online, I've tried a number of regexmatch and combined it with indirect but nothing seems to work. Here's a sample of my sheet: https://docs.google.com/spreadsheets/d/...
Jessica Gutowski's user avatar
1 vote
1 answer
12 views

If a number is entered within a certain range is populates the corresponding pay and revenue

If using data similar to below, How can I have it so if a number is entered within a certain range is populates the corresponding pay and revenue. Data Example: **Miles** **Revenue Pay** 0-55 ...
Melanie Huer's user avatar
1 vote
2 answers
29 views

What is the syntax for an indefinite range? [duplicate]

In Google Sheets formulas I often see ranges specified with a large terminating number, such as B3:B299 = SUM(B3:B299) The upper-bound, or largest row number, is often chosen to be large enough to ...
Theodore Shepard's user avatar
3 votes
2 answers
64 views

Google Sheets Formula to find specific months

I have a Google Sheets document with multiple sheets. What I want to do is perform a lookup on a specific sheet Expenses, and find all the expenses for a given month and year. Here's what I have so ...
jbuck's user avatar
  • 31
0 votes
1 answer
19 views

How to strikethrough a text if the text is the same in another cell using conditional formatting?

I am making a table assignment for the guests for my brother's wedding. I want to know if it's possible to conditionally format to strikethrough a cell that has the same text as another cell. In this ...
user326450's user avatar
0 votes
1 answer
24 views

Macro to return 1 + the value of the cell above

I am experienced with VBA but not Google Apps Script / Javascript. I'm doing a lot of data entry for which filling down with CTRL+D is super-useful but I want to create a macro that will copy down the ...
Daniel Austin's user avatar
0 votes
1 answer
19 views

How to use if contains within an arrayformula and multiple or conditions?

I want to replace the "=" in the following example with a text contain to match entries partially instead of entirely. However, I cannot get this to work. I thought I could replace the (A2:A=...
orschiro's user avatar
  • 5,498
1 vote
2 answers
33 views

Replace text in document using A1 notation as variable names in order to retrieve values stored in those cell references

I have a Google Doc that contains placeholders based on A1 notation like %A1%, %B3%, %A3%, and so on. In a Google Sheet I have values stored in the cell references that match the variable names. I ...
user61681's user avatar
  • 113
2 votes
1 answer
22 views

How do I set up a cell to output the mean or median of multiple datapoint entries?

In Google Sheets, I'm taking blood pressure readings and entering them into an entry sheet. It's useful to measure the pressure a few times (perhaps three) in short succession and then record the ...
Maciej Tomczak's user avatar
3 votes
1 answer
80 views

About countif but ignore empty cells with formulas in it

I've tried different solutions from Google but none worked. I want to extract the most frequently occurring text from a column. I was trying the following formula but it only returns a blank cell ...
Yu Lun CHEN's user avatar
0 votes
0 answers
14 views

How to correct script which get emails of users? [duplicate]

I try to get eMails of users which change any range in my Google Sheets. I use this script below: function onEdit(event) { var as = event.source.getActiveSheet();//Текущий лист var address = event....
Group Stroy's user avatar
0 votes
2 answers
24 views

Automatically copying the formula whenever I INSERT rows IN BETWEEN rows, not at the end of the row

What I want to happen is that whenever someone insert a new row in between rows, the formula would have also copied there instead of dragging the column again and again. Here's the code that we're ...
Zen_Zen_Desu's user avatar
0 votes
1 answer
47 views

How to retain a value from a formula, update the year, and rerun the formula N times

The formula in cell A2 produces the following: How do I create the following results table in a new tab? I want to: Copy the data from the GOOGLEFINANCE Tab column B to the RESULTS Tab corresponding ...
Lon's user avatar
  • 3
0 votes
1 answer
28 views

Import certain columns when one column contains a 5 digit number

I was trying to use Query on this, but read that query doesn't work well in a column with mixed data. What I have is a financial spreadsheet where Col3 either has the words DEBIT or a check number. ...
Salt_Tea's user avatar
0 votes
1 answer
29 views

Multiple substitutions in a table

Suppose we have a table like the following : A B C D E F G 1 17-01 DO 22-06 DO 17-01 22-06 22-06 2 22-06 22-06 17-01 DO 22-06 09-17 09-17 3 09-17 DO 22-06 22-06 DO 17-01 17-01 4 17-01 22-06 17-01 ...
Maidenas's user avatar
3 votes
1 answer
93 views

Return the most recent unit price of a matching item that is dated on or before a specified lookup date

In Google Sheets I have a list of orders recorded by order date, item, and price. There is one order per row, but the same items are ordered on different dates and the prices vary between orders. I'm ...
Se7en's user avatar
  • 31
2 votes
1 answer
53 views

ImportRange Query Formatted Incorrectly

I have a spreadsheet that I want to pull data from another spreadsheet. This is my formula: =query(importrange("https://docs.google.com/spreadsheets/d/14lj6yPp8h9U1JAEA4dz-uROaQgZxy60NjXniHOFUeAU/...
Salt_Tea's user avatar
-1 votes
1 answer
38 views

Dependent date selections

I have a Google spreadsheet that contains a list of events displayed in a 5-week calendar format. These are 2-day events however the first and second day are roughly two weeks apart. A user selects ...
T Cor's user avatar
  • 1
0 votes
0 answers
17 views

spreadsheet recognize my "time" input as "date" [duplicate]

This column (green) contain data with time format (HH.MM.SS),but for some reason, any time format with their minute below 12, converted into date format (probably because on date format, max month is ...
Vintikon Indonesia Perkasa's user avatar
0 votes
1 answer
27 views

Cumulative SUM formula

I want a cumulative formula that will return the running total for each value in column B. For example, in the rows in column A: =SUM(B$1:B1) =SUM(B$1:B2) =SUM(B$1:B3) =SUM(B$1:B4) etc. I would like ...
Giovanni Capuano Solution's user avatar
2 votes
1 answer
89 views

How to sum in a column from the max value upwards until the value is not descending?

How to sum in a column from the max value upwards until the value is not descending? Here is the example: A 15 1 2 3 1 2 3 4 5 1 2 3 4 5 I want a cell to calculate the sum value from ...
Yu Lun CHEN's user avatar
0 votes
0 answers
10 views

Iterate Through a Range in Google Sheets to Generate a Sum Based on Conditions [migrated]

Apologies in advance if this has been asked already. With Mint shuttering and forcing us to Credit Karma, I'm hoping to build a semi-automated budget and spending tracker in Google Sheets, and would ...
Ben's user avatar
  • 1
2 votes
1 answer
44 views

How do I exclude a text value from a count in a Google Sheets query statement?

I have gotten an output that works for my sum, but I need to exclude one value in count(D). I want to exclude from the count in column D all cells containing the text ZERO. I've been researching and ...
Larry OBrien's user avatar
-1 votes
1 answer
26 views

Get list of files from Google drive into google sheets based on date range using app scripts

Hello I am looking for a script to get list of files from google drive within a specific date range. I have got a script on this site here: List new files from a specific Drive folder and subfolders ...
Shah Ankit's user avatar
0 votes
1 answer
50 views

"Mirroring" data across google sheets

Is it possible to "mirror" data (example below) between separate google sheets? If not, is there another application that would allow me to do something like this (preferably within google ...
Otter Spotter's user avatar
0 votes
2 answers
59 views

How to sum total sales for a store dependent on max date

I'm trying to sum the sales for different stores with the criteria to use the maxdate in a column, in google sheets. Below is the data I have: Column A: Dates (Weekly) Column B: All store names Column ...
Crose's user avatar
  • 3
0 votes
3 answers
27 views

Finding strings from a list between 2 string conditions that are part of the list

In Google Sheets, I have a list of unique strings that I want to filter based on two conditions. These conditions are two strings within that list. Ultimately, I want to find all the strings, ...
pfnanc's user avatar
  • 43
-2 votes
1 answer
40 views

I need a formula for this situation

So, this is the situation I'm facing, need formula in column B & column C: A B C 1 2 3 4 5 6 0.2 3/4/5 4 0.1 0.1 0.2 0.2 0.2 0.3 0.1 1 1 0.1 0.2 0.2 0.2 0.3 0.3 0.3 5/6 5.5 0.1 0.2 0.2 0.2 0.3 ...
Yu Lun CHEN's user avatar
0 votes
1 answer
32 views

COUNTIF that uses Dropdown Values in the Formula

I am trying to create a Google Spreadsheet to keep track of how many shirts I need to order, for a Cross Country Camp. I wanted to use a COUNTIF to count how many of each type of shirt I need to ...
James's user avatar
  • 1
1 vote
2 answers
24 views

Printing the date of a cell in the Google Apps Script console

I wrote in cell D1 the function "=today()", and in google apps script I'm trying to print it in this way: function myFunction() { const ss = SpreadsheetApp.getActive(); const ws = ss....
Federica Guidotti's user avatar
0 votes
2 answers
30 views

How can you calculate the distance between in a spreadsheet?

I want to calculate the distance between an address and multiple addresses in a list. I want to use this calculation to quickly determine which addresses fall within a delivery radius. The addresses ...
Mark C's user avatar
  • 101
2 votes
1 answer
41 views

Conditional formating max values over multiple rows

I have Google Sheets data in column pairs from Column B to AQ. Each column pair represents a single year with the first column containing order counts, Orders, and the second containing order totals ...
Wes B's user avatar
  • 21
0 votes
0 answers
10 views

Extracting web-publish exchange rates using google sheets IMPORTHTML function [duplicate]

I would like to extract specific exchange rates from two Thai banks: https://www.bangkokbank.com/en/Personal/Other-Services/View-Rates/Foreign-Exchange-Rates# https://exchangerate.krungthai.com/#/...
Maciej Tomczak's user avatar
2 votes
1 answer
34 views

Highlighting only text for google sheets

Is it possible to highlight individual text in Google Sheets, just like how you can on Google Docs or Slides?
Emma's user avatar
  • 21
1 vote
1 answer
14 views

Sorting a range of data based on a column within that data

I have a set of data spanning columns A through P in Google Sheets. I would like to add a column in Q that will return the values of A based on the top values of column P. I assume this can be with ...
Colin Davis's user avatar
4 votes
2 answers
2k views

Converting "4.8 million" into an actual number

I have a sheet that has numeric records that are a mix of three different number types: 260,000 4.8 million 870 million 1.1 billion The first one is handled just fine as a number, but the ones ...
RobKohr's user avatar
  • 151

1
2 3 4 5
179