Questions tagged [worksheet-function]

This tag is for spreadsheet questions where the question or answers involve an in-the-cell worksheet function.

Filter by
Sorted by
Tagged with
1
vote
0answers
24 views

How to sort a UNIQUE()-generated list based upon values from an independent column? [duplicate]

Can and how do I get the UNIQUE()-generated list in column B to automatically sort (in A->Z order) based upon the numbers in column C, as shown in the image below? I want this "unique sort" to ...
0
votes
0answers
14 views

How to auto expand column with formula to bottom of sheet

I am making a credit card debt paydown sheet that I want the user to be able to add more rows and have the formulas automatically fill down the column. I tried ARRAYFORMULA but because the results of ...
0
votes
1answer
45 views

Google Sheets checkbox validation “If this then that”

My sheet has two checkboxes who we will call "A" and "B". I need it so A must be checked in order for B to be checked. I cant find out how to do this using data validation but it seems there must be ...
0
votes
0answers
9 views

MINIFS using multiple ranges

I am trying to find the smallest positive value for multiple ranges. Essentially I want to find it in column J without including cells J7,J14,J21,J28 and J35. This is what I have so far: =...
0
votes
1answer
19 views

Conditional formatting row of data based off of a date range

I have a bunch of entries in a sheet which are all between a range of dates, the two columns with dates are start_date (B) and end_date (C). I already highlighted past time periods using; =DATEDIF($...
0
votes
1answer
35 views

Aggregate Formula in Google Sheet using wildcards

I'm trying to copy and paste an excel formula I use to categorize data into Google Sheets and struggle as the formula doesn't work. I have two sheets: The Referral sheet contains all the referral ...
0
votes
1answer
89 views

Google Data Studio - Sum of 2 fields from different data sources

I'm just getting started with Google Data Studio. I have connected 2 different Shopify stores to it, which worked totally fine. Now I try to show the sum of the revenue of both stores, but the ...
0
votes
1answer
32 views

How to sum specific columns when adding new columns for hundreds of rows?

I've got a sheet in Google Sheets that tracks a large inventory. I want to quickly see the total of the last three months (columns) but ONLY the last three months. So far it looks something like this: ...
0
votes
1answer
22 views

Query runs but returns first row greater than the DATE at 00:00:00 not the row after 10:00:00

Trying to use the following query: =query(WABrdg729!A:B, "select A, B where A > date '"&text(datevalue($A$6), "yyyy-mm-dd")&"' AND A > timeofday '"&text(timevalue($A$6), "hh:mm:ss"...
-1
votes
1answer
15 views

DeleteRow Formula parse error. Google sheets

I am not sure what I am doing wrong. I want to delete the row if it is True.
1
vote
1answer
19 views

Total Spent Formula in Google Sheets

So with my sheet, I am currently in the process of adding more stats in, I have looked into adding these stats by using Index & Match however it will only match to the first correct match it has ...
0
votes
0answers
16 views

how to visualize time range data in google sheets

i have several thousand rows of data. Each row represents a task and includes the start time, end time, and data related to the task in the other columns. A task duration varies between 1 second to ...
0
votes
1answer
34 views

Sliding window average of last N non-empty non-blank cells in Google Sheets

Similar to this question: Average of last 7 non-empty non-blank cells in Google Sheets I'd like a formula to compute a sliding window average of the last N non-blank cells in a column. e.g. For N = ...
0
votes
1answer
16 views

How to check separate sheet cell for any data, and return a Y?

I'm using several Google sheets to capture product data and optimize it. One sheet will feed data into the next, and then again into another sheet, using this formula: =IMPORTRANGE("https://url-to-...
0
votes
1answer
21 views

Query Function Not Putting Consolidated Data in the Right Place

So I don't know how to explain this completely, but basically, I'm using the query function to consolidate data from multiple sheets into one Master Sheet. The function itself seems to be working fine ...
1
vote
1answer
55 views

Sort Google Spreadsheets so it returns 'alternating' values in a column

How can I sort a random list in Google Sheets so it returns alternating values? The current random list looks like this: fName lName Room ID ----------------------------------- Sara Don ...
1
vote
1answer
417 views

Finding multiple “most common values” in a column

I am trying to display the 5 most commonly appearing text values in a column in Google Sheets. The following will give me the most common value in column A: =ArrayFormula(INDEX(A:A,MODE(MATCH(A:A,A:...
1
vote
1answer
201 views

Goggle Sheets IMPORTXML/REGEXEXTRACT out of date

Alright, so I found my basic answer from these posts, 2-3 years ago. Extract data from Steam Import total hours on Steam Steam has updated its website since then, so I've messed around with it and ...
1
vote
2answers
34 views

Google Sheets - Convert entire column of prices by 50%

I have a large column of prices, however, sometimes the word [FIXED] also appears in these columns (see screenshot below). I need to convert this price list to wholesale. All the products wholesale ...
1
vote
1answer
82 views

How to calculate a subtotal based on multiple columns in Google Sheets

I want to create a dynamic formula, which will calculate the sum of column C, subtracted by the sum of column D, between the range of the current new subtotal and the previous subtotal, and adding the ...
0
votes
0answers
21 views

copy/paste formula with reference to another sheet?

Within Google sheets, I have a formula like: =COUNTIFS(foo!H:H,"*"&B14&"*",foo!E:E,"*"&C14&"*") which I want to copy past as: =COUNTIFS(bar!H:H,"*"&B14&"*",bar!E:E,"*"&...
2
votes
2answers
377 views

Is there a way to autofill cells with a formula targeting cells horizontally (in rows) while autofilling vertically (in columns)?

In the above image fields are populated as follow: A1 is empty/not important B1 = animal C1 = dog D1 = male E1 = animal F1 = cat G1 = male H1= insect I1 = ladybug J1 = unknown I am working in column ...
-1
votes
1answer
60 views

Count worked days per employee

I am creating a spreadsheet in Google Sheets for a client who wants the sheet to pull data from their Google Calendar to calculate how many days each employee works. They use Calendar for their ...
0
votes
1answer
14 views

All text in a cell before the “ - ” or “ : ”

Is the Query function capable of taking the contents of a cell and returning everything before the first instance of - or :? Here is a screenshot of what I mean: Obviously, this isn't my actual data,...
0
votes
4answers
55 views

How to pull the page number and the number of images listed from one sheet and then populate cells in another sheet accordingly?

Sorry, I didn't know how to summarize it in the title. Basically, I have a spreadsheet where I'm keeping track of the number of images that need to be on each page of a document I'm working on. On ...
1
vote
1answer
1k views

IF statement not working in Google Sheets?

So I'm using Google Sheets and I'm trying to determine whether or not cells in a certain column AO have a higher value then cells in another column O. My formula is =IF(AO6 > O6,"UNDER","OVER"). ...
0
votes
2answers
32 views

How to compare duration to another duration using IF statements

=IF(TIMEVALUE(H2)<=TIMEVALUE("00:01:00.000"), 150, TIMEVALUE(H2)<=TIMEVALUE("00:01:30.000"),100, IF(TIMEVALUE(H2)<=TIMEVALUE("00:02:00.000"), 75, IF(TIMEVALUE(H2)<=TIMEVALUE("00:02:30....
0
votes
1answer
449 views

How to import data from Investing.com into spreadsheet?

I want to import the live individual stock price in the Google Sheets as highlighted on the image. Is that possible? https://in.investing.com/equities/jagson-airlines-bo
0
votes
2answers
616 views

Import range from 3 sheets/tabs into 4th

I currently have 3 sheets (tabs) in a Google Sheets workbook. Each column header (Row 1) in the 3 sheets are identical. I'd like to create a 4th sheet that combines ALL the contents in the 3 sheets ...
0
votes
1answer
36 views

4 IF AND Statements in one cell

Trying to get two equations to then present one of four options. Example: Have no idea where to start, all four need to sit in the one cell.
0
votes
2answers
268 views

VLOOKUP is not finding text value on other sheet

See this example spreadsheet. I get #N/A value when I try to find bbb on sheet2 using formula =VLOOKUP(A1;sheet2!$A$1:$B$4;1;FALSE) How to fix it?
-1
votes
1answer
88 views

Test if value of cell is used in a formula [duplicate]

In a google sheet I have some values that are used in formulas in other cells (until now, nothing unusual...). I would like these cells to have a special color to be aware that if I modifiy them, ...
1
vote
1answer
492 views

Is it possible to return a range from an IFS or SWITCH?

I am trying to simplify some 'if else if else' code by replacing it with a single IFS or SWITCH expressions. I have simplified all the code to be trivial but show the problem. This code works and two ...
0
votes
2answers
3k views

Can I auto populate cells in Google Sheets?

I have a bunch of categories; Bar Foo Ghost Misc Other All have variable amounts - except for category Ghost, the amount is always 15.00. I have Data Validation set up (which is how I pick the ...
0
votes
1answer
1k views

Conditional formatting with color scale for many rows independently once

I create reports with different cohort tables. Each row of a table must have conditional formatting with color scale (depending only on current row values, i.e. regardless of other rows min/max values)...
0
votes
1answer
2k views

How to reference a Google Sheet by filename?

How to reference a Google Sheet by filename? I would like to use IMPORTRANGE function, but it can only reference a file by URL not by filename. Example filename: 2018-11.xlsx 2018-12.xlsx
2
votes
1answer
54 views

Difficulty appending to cell in Google Sheets

My goal is to automate making a calendar on Google Sheets. I used =DATE(C1,A1,B1) to make a date and then each following cell, I added 1 to the previous cell. However, now I want to also automate ...
0
votes
2answers
62 views

Conditional formatting if entry is final entry of the day

I have a Google Sheet in which I keep track of transactions, kinda like this: | Date | Funds | Running total | |---------|---------|---------------| | 5/23/18 | 10.00 | 10.00 | | 5/23/...
1
vote
1answer
226 views

Is it possible to calculate in the background of a data validation cell?

Is it possible to calculate in the background of a data validation cell? I am creating a quite comprehensive spreadsheet that shall help me calculate dice pools in an RPG, but to not trouble you with ...
1
vote
1answer
758 views

Examine for overlap in Google Sheets?

Let's say I have the following start and end days/times: SHEET1 Resulting in a range of time from Mon 10am through Tues at 11:59pm. I'd like to know whether this range of time overlaps another ...
0
votes
1answer
102 views

Change COUNTIF range based on the current day of the week, with 3 days using the same dataset

I work as a delivery driver and have been futzing around trying to create a google sheet that automatically updates with the current day's drops. However, there is a rub with the data set. The ...
1
vote
2answers
2k views

Multiple IF statements: IF, AND, ISDATE, THEN formula

I'm trying to get a nested formula to work, but I keep getting an error. I tried using the generic formula =IF(AND(A1="this",B1="that"),"x","") but couldn't figure out how to make sure Q6=date. I had ...
2
votes
2answers
43 views

Sum points per row based on if expected value is unique within row

Given a Google spreadsheet of quiz answers (with rows for questions, and columns for people's answers), I want to work out a person's total score. The scoring system is that if only one person gives ...
1
vote
2answers
8k views

Google sheets function IF, AND conditional formatting

EDIT 2: link to test sheet: https://docs.google.com/spreadsheets/d/1IWQWDK9piw_xFVm_48XZ8YAS_LQqXi6sd2GITLIfoAQ/edit#gid=903221057 EDIT 1: added a column, so now J=K, K=L, L=M | updated formulas to ...
2
votes
1answer
2k views

Query Function with Unique References

I am currently using the QUERY function to move relative information to another sheet, which is then used for forecasting. Now what is currently happening is the same business names are showing up in ...
1
vote
1answer
647 views

Multiple IF conditions nested with a CONCATENATE

I have a formula as follows which I'm using for a scheduling system within Google Sheets: =IF(B2="","",(CONCATENATE($B$1&" "&B2&CHAR(10)&$C$1&" "&C2&CHAR(10)&$D$1&"...
1
vote
1answer
44 views

Check value even if it doesn't match validation

In my Google spreadsheet, I have a column G of dates that interviews are/have been scheduled on. There is a validation set on it to validate for dates with a warning. Every once in a while, when ...
1
vote
1answer
23 views

QUERY for like-minded rows

Scenario Consider the following query: =QUERY(A15:E861, "select B, sum(D) group by B") This works for rows with col B's that have the SAME description eg. 1 | abc | n | 10 2 | abc | n | 10 3 | cba ...
1
vote
2answers
557 views

Display date of each weekday of current week

I have seven spreadsheets, one for each day of the week. I am trying to get each one to display their date for that week. So for example, this week Monday would display 7/16/2018 and the Tuesday ...
1
vote
2answers
1k views

How to perform multiple operations where the IF statement is true?

The function below is what I use to handle contact information (email/phone numbers). If the entry is an email id (has text), it will keep it as is. When a phone number is entered, it does a variety ...