Questions tagged [worksheet-function]
This tag is for spreadsheet questions where the question or answers involve an in-the-cell worksheet function.
202
questions
0
votes
0answers
3 views
Using a cell to build a formula
I am trying to build an ever changing formula, that will list the contents from multiple work sheets.
The trouble I have is sheets will be constantly deleted and added to the spreadsheet, meaning I ...
0
votes
1answer
35 views
Multiple IF functions on a single cell
How do I add multiple IF functions on 1 cel, without blocking it on the first line.
As you can see, column D contains a bunch of text. In column A, I want to categories them.
It works up until the ...
0
votes
0answers
23 views
Go into “Display” tab, click a name off the drop down list, and view that individual's data
I have 3 tabs. "Timestamps" "Techs/Locations" and "Display."
My form timestamps when an employee clocks in or out. I have created two separate columns to carry over the ...
0
votes
1answer
28 views
How to sum up a repeating/chained formula in Google Sheets without typing each out?
I've got a chain of formulas I need that works out to:
=((C5*A5)+(C6*A6)+(C7*A7)+(C8*A8))/A3
=((D5*A5)+(D6*A6)+(D7*A7)+(D8*A8))/A3
All the way into 100's of rows, that I then need for subsequent ...
1
vote
1answer
19 views
Getting the column header if the row value is the greatest of the entire row
I think that the best way to describe what I want is with a image
Check the image. Each row will have a description (Of a feature of the programming language, for example) each language will have a ...
1
vote
1answer
182 views
Looking for a “weighted average if” functions in Google Sheets
I am familiar with the =AVERAGE.WEIGHTED function in Google Sheets, but I'm wondering if there's a way to add an IF in there, like =AVERAGEIF. I have a baseball spreadsheet, with the following ...
0
votes
0answers
23 views
Count mismatches between rows in google sheet
Need some help on this cause I'm getting an issue
I've this three columns (Time, R1 and R2) and I'm trying to count the mismatches between R1 and R2 but for each month (on the time column)
I already ...
1
vote
1answer
24 views
In Google Sheets,how to get a value lower than a particular cell value in a row?
In Google Sheets, I have numbers in 12 cells in a row. Then in the 13th cell of the same row, I have a number which is one of these 12 numbers. Now, I want find the 1st value lower than the value in ...
0
votes
0answers
23 views
How do I return all of the email addresses from column D in one Google Sheet that are not present in column D of a different Google Sheet
I want to build a function that will return all of the email addresses from column D in one Google Spreadsheet that are not present in column D of a different Google Spreadsheet.
This could be in ...
0
votes
1answer
31 views
Convert a field in a Google Spreadsheet document from text to number using a formula ignoring text but keeping numbers
I have a Google Spreadsheet document with a column which contains string data which includes both numbers and text. For example:
$39,90 + Tax
$69,90 + Tax
$20 / month
I want to sum all those values, ...
0
votes
0answers
18 views
Find a row based on cell value and then count within that row
I'm currently working on a Google Sheet for work, in which I'm trying to work out whether each student has done a particular paper or not. I want to have two sheets. One sheet will have the students ...
0
votes
1answer
34 views
Google spreadsheets sorting confusion when using formulas
I am sort of confused about the behavior when sorting by a columns content. I have this formula in A1:
={"Page";arrayformula(iferror(filter(Events!A16:A,Events!C16:C="Feedback R"),""))}
I get the ...
0
votes
1answer
42 views
Google Sheets SUMPRODUCT and IFS on different sheets
=ARRAYFORMULA(SUMPRODUCT((Products!B2:B=D2)*(IFS(H2="x",Products!D2:D,I2="x",Products!E2:E,J2="x",Products!F2:F)*(E2:E))))
I have a spreadsheet with an Orders sheet and a Products sheet. Product ...
0
votes
1answer
19 views
Pulling data from most recent google sheet
I am currently working on a sales sheet for my team and have a question.
I currently have each month separated into different sheets for ease of use with our current workflow and would like to be ...
1
vote
1answer
45 views
Is there a way to do a “filter”, “if” function across multiple columns to only pull information based on certain criteria and headers?
I do not know how to explain this perfectly without a picture so please take a look at the image before reading.
I am trying to filter the data in Fig 1 (Above). based on the headers and then if the ...
0
votes
1answer
26 views
“IF” Function Question
So I have run into an issue...
This maybe simple, but I just can't figure it out.
On a spreadsheet on Google Sheets, I need for a cell to populate a total when a certain cell contains another ...
-1
votes
2answers
71 views
Is there a way to automate a priority table in Google Sheets based on another table?
I have a shared sheet where I have people list their classes by priority like below:
StudentA StudentB StudentC StudentD
Math Math Sci Eng
Sci Eng Eng Math
Eng ...
0
votes
0answers
27 views
Updating a cell on another sheet by changing dashboard
I am currently working on a to-do list· dashboard for my household chores and have a question.
I am using a filter to pull daily tasks out of a raw data table, and would like to be able to mark each ...
0
votes
0answers
18 views
Spreadsheet formula to find all matching criteria then subject from cell in row
I have a Google sheets document arranged like so:
Sheet 1:
A | B | C
tomatoes | 10 | **SUM**
Sheet 2:
A | B | C
2019-01-01T15:00:00 | tomatoes | 2
On Sheet 1, ...
-1
votes
1answer
96 views
Fix my script? How can I automatically create events in google calendar using dates from google sheets?
I read that it is possible to link a google sheet to google calendar, so dates can be used to create events in the calendar. I used script I found around about, but absolutely nothing happens. Can ...
1
vote
0answers
26 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
1answer
142 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
1answer
24 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($...
-1
votes
1answer
190 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
893 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
138 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
49 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
26 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
34 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
1answer
143 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
23 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
27 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
79 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
3k 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
418 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
57 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
180 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 ...
2
votes
2answers
632 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
152 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
67 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
3k 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
160 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
2k 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
1k 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
37 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
556 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
195 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
989 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
6k 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 ...
