Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

Questions tagged [filter]

Filters are rules or parameters one uses in order to exclude or include certain content from either appearing in a query results page, list or being returned in a search result.

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

COUNTA and FILTER returning a 1 when it should return a 0

I am using a summary page to show stats from a registration page. I want to show the number of registrations based on their date of registration (which is in column A of the Registrations page. I ...
0
votes
0answers
19 views

UNIQUE ,INDEX AND IMPORTRANGE

I am stuck. I use the import range to get a set of data however now I need to the unique values that that matches the import range that I have. Say for example below is randome sample. I have import ...
0
votes
1answer
20 views

Generate Google sheet with selection from other sheet

I want to make a list of vegetarians in Document 2 from information in Document 1. Document 1 Columns A and B. Bill Meat Jane Veg Tony Meat Sarah Veg I want Document 2 to search ...
1
vote
1answer
1k views

How to combine FILTER with TRANSPOSE in data validation custom formula

Cells A2, C2, and E2 contain the data I want to make my custom list for data validation. Cells B2 and D2 have data I don't want in the list (just text - "vs" - in them). So, I'm trying to use the ...
1
vote
2answers
2k views

How can I set the increment value for Google Spreadsheets to auto-increment?

OP Edit: I think I want Sheets to behave in a way it's not intended. I expected Sheets to recognize the incremented value in my formula the way I believe it recognizes the increment value in a ...
0
votes
2answers
21 views

Copy other cells if EITHER column has specific text

In a single sheet wanting to copy D3:N if either B3:B or C3:C = S2. I'm sure there is a simple formula to do this I am just unable to find it!
1
vote
2answers
103 views

Google Sheets' Query or Filter solution

I have a simple table with columns: A (Donor ID) B (Donation date) C (Donation Amount) I need to extract the following report: For each donor ID most recent donation date and amount donated ...
1
vote
2answers
14k views

Google Sheets formula for “if contains” from range

I try to figure out how to get a value based on "if some word is in a range". So formulas from (Google Sheets formula for "if contains") works: =IF(ISTEXT(REGEXEXTRACT( A1, "sites")), 1,0) ...
1
vote
1answer
27 views

How to summary the multiple entry into a single sheet by name and by date?

Names are in rows and Dates are in columns. Sheet 1: Name 1-Jun-18 2-Jun-18 3-Jun-18....... KB CL KB EL AJ SL SD SL SD CL I need ...
0
votes
2answers
16 views

Importing data found to be true from one Google sheet to another

I am trying to find a way to import data (text) from one sheet to another, via the same book. I assume it's going to need an IF or IFS formula because I only want data is that found to be "TRUE" (via ...
0
votes
1answer
376 views

QUERY with Week Number

I use query to extract two columns from another sheet with data. Column A has dates Column B - numbers I want to extract only rows that correspond to a specific calendar week number. For example 19. ...
32
votes
2answers
59k views

FILTER(condition OR condition) syntax?

I am using FILTER() to display a value from Sheet 2 where the Name columns match. My problem is where I want: =FILTER(Sheet2!A:F, Sheet2!A:A="Combat Medic" OR Sheet2!A:A="Universal") as OR results ...
0
votes
0answers
14 views

How do I edit cells in Google Sheets, after filtering or importing a range [duplicate]

when I import a range, in Google Sheets, or I use the formula Filter(), I get an array of data. I would want to then be able to edit the imported/filtered data, is that doable? of course, without ...
0
votes
1answer
93 views

Filtering a QUERY using MATCH - how to pass column value into function? Or is there a better way?

I want to get a subset of data from another tab. Eventually, this will become a script and pull data from a different sheet. I can use QUERY to get all the data available, but I only want to return ...
2
votes
2answers
3k views

Query or filter the data where the start time is past 3:00 pm

I've started using Google Sheets for our logbook and it's been a great help. I have data with a time component in it (start and end time) and I'd like to query or filter the data where the start time ...
2
votes
3answers
138 views

How to filter out a long list of sites from Google search?

I have a list of sites I would like to permanently exclude from Google search results. These are the large family of sites plagiarizing (and some of them garbling with machine translation) Q&A ...
1
vote
0answers
165 views

Google search by time frame anomaly

I'm trying to understand the logic Google search uses when I use the time frame filter. In the first search, I restrict results to "Past 24 hours": In the second search, I restrict to "Past week" : ...
1
vote
2answers
1k views

How to pull last value in a Google Sheets column and populate it across another worksheet column?

This is my first post here- I hope this is the right place for it, apologies in advance if it isn't. Context: I have a password protected presentation that I need to give users access to, but only ...
0
votes
2answers
299 views

Conditionally format cells that not match with range from Sheet2

I have two sheets in the same file where "sheet1" has cleaned up information and "sheet2" has the dump from my jira. Now I am trying to compare Column F from sheet1 and sheet2 to confirm if they are ...
1
vote
2answers
13 views

How do I detect duplicates in two worksheets?

I have 1 Google Sheet with 2 worksheets: Worksheet "1" has 1 column A with 5 lines of data (AAA, AAB, AAC, AAD, AAE) Worksheet "2" has 1 column A with 5 lines of data (AAA, BBB, CCC, DDD, EEE) I ...
0
votes
3answers
27 views

Summing every other row depending on the classification of the row above

I've got a confusing question, I can't figure it out. I have this spreadsheet of states population and their political control, each on separate alternating rows (political control is designated with ...
0
votes
1answer
29 views

SUM across other worksheets (without hardcoding their names)

I've got a spreadsheet for my organisation's management accounts. Each project has a worksheet, setting out income and expenditure by financial year. This is aggregated together in a few sheets ...
0
votes
1answer
9 views

Cross Comparing values between two columns for voting purposes

The current content of our Google sheet, is formatted as such: - PERSON 1 | PERSON 2 | YES - PERSON 1 | PERSON 3 | YES - PERSON 2 | PERSON 1 | YES - PERSON 2 | PERSON 3 | YES - PERSON 3 | PERSON 1 | ...
0
votes
1answer
35 views

Auto sorting / copying data based on inputted data?

Sorry if this is a bit confusing or if I’m not explaining this well enough. Using my provided picture as an example how would one auto copy data that gets inputted into that table based on estimated ...
0
votes
3answers
510 views

Google Sheets COUNTIFS Range of cells as criteria possible?

In the Google Sheets Formula =Countifs, is it possible to have a range as a criterion, for example: =COUNTIFS(ExampleSheet!A1:A10, "Some criteria", ExampleSheet!B1:B10, List_of_Names!C1:C) I couldn'...
0
votes
2answers
20 views

How to work over rows from all other sheets in Google Sheets?

I have a 'summary' sheet and 12 other sheets named 'Jan20', 'Feb20', etc. In the summary sheet I have this formula (to calculate Uber costs): =FILTER('Jan20'!F2:F, REGEXMATCH('Jan20'!D2:D, "UBER")) ...
0
votes
2answers
37 views

Help with a Vlookup function to return multiple results of not exact match

I am trying to run the following code to return all the matches of column A from column C in a set of rows. In this situation, cell A1, is contained in a few iterations of column C, however, these are ...
0
votes
1answer
12 views

Process visualization based on data

I am trying to use a Google spreadsheet to visualize a customer journey. I am trying to create the following graphic: I have created the following example spreadsheet: https://docs.google.com/...
0
votes
2answers
25 views

How to count duplicate names as one value in Sheets?

I have a large dataset in Sheets and I'm grouping together the data in a pivot table and have a hard time figuring out how to avoid counting every instance of a name instead of counting the name as ...
2
votes
1answer
4k views

FILTER has mismatched range size; referencing the second last cell in a column in range

I am using a multicriteria filter to check if my sheet contains a certain value combination. The formula should automatically include any new rows added to the sheet. However, if I do it this way: =...
1
vote
1answer
18 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
56 views

How to combine these funcions (FILTER + IMPORTRANGE) in one? [duplicate]

I have all data in one sheet and I need to filter the imported data to another sheet. How can I do that in one function? Function to filter data which I use: =FILTER(A7:C,RIGHT(A7:A,7)="?id=999") ...
0
votes
2answers
123 views

Make a filter but exclude one column

I would like to make a filter on Google Sheet but exclude one column For example: In this case I would like to have the filter on Column A and B but the data on Column B should stay as they are, so ...
2
votes
1answer
22 views

ArrayFormula to create a table of data from 1 column

I have the below formula and want to turn it into an array. =INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1)) I have tried it with the below attempt but only get 1 single cell result. =arrayformula(INDEX($A:$A,...
1
vote
1answer
39 views

Simplify multiple OR statements in a QUERY using IMPORTRANGE

I am trying to query values from a different sheet using a range from the current sheet. Is there a way to simplify the formula and get rid of all the ORs? Like maybe using a FOR loop? =QUERY(...
11
votes
4answers
2k views

Average of last 7 non-empty non-blank cells in Google Sheets

I have numbers in range D7:D and there are times when some cells are empty. I want the average of the last 7 numbers but to skip blanks. So if in the last 7 there's only 3 that are full, I want it to ...
1
vote
1answer
15 views

How to display list of items that is in other sheet but not in the other? [closed]

As the title says, I need a column to show what is not on a range(BASIS) that is in the other sheet(BASIS) : I've been searching for hours now but only found the opposite results.
1
vote
1answer
67 views

Delete Google Sheet rows matching multiple criteria with custom formula

I have a Google sheet where I am trying to remove multiple rows matching any one of a vector of values using (Filter by condition -> Custom formula). For this example, let's say I want to remove ...
1
vote
1answer
36 views

Does Posteo.de support Sieve/Managesieve protocol?

Posteo.de offers filters which can be configured through the web UI. This function is however lacking function of the full set of sieve features which I'd get from filters defined through a ...
9
votes
4answers
897 views

Blocking stupid videos on YouTube

There are a lot of really stupid/trolly/fake videos on YouTube. I'd like to blacklist them forever when I see them, so I don't accidentally click on them in the future, and sometimes block the ...
1
vote
1answer
18 views

Pull image or skip to next cell if empty

I am currently using Google Sheets to make a list of videos and thumbnails. I have various sheets titled: Frying, Stewing, Baking, etc. Within each sheet, there are only two columns which include a ...
1
vote
1answer
19 views

Outputing Array Formula in one row

I came across the below array formula, which works really well, I'm just struggling to tweak it, and barely understand it's inner workings, but it's very good. Instead of displaying a total of stock ...
1
vote
1answer
24 views

Solve errors in a series of FILTER functions within array brackets?

I have been working on a budget project on Google Sheets. My goal is for a range of data to be filtered by date (which is represented as an integer between 1 and 31 for the day of the month). All ...
2
votes
1answer
544 views

Copying values between Google Sheets

I have 2 Google Sheets. I have data on Google Sheet A and I would like it to automatically transfer some values to Google Sheet b. For example, I have some email addresses and some data associated ...
1
vote
3answers
6k views

Increment Cell Reference in Formula by 10

I have a spreadsheet that will have values every 10 rows. I'd like to make reference to them but I'm having trouble doing this by clicking and dragging the corner handle to fill out the cells. If I ...
1
vote
2answers
134 views

How can I allow results from a Google Sheets 'filter' action to be mutable?

Brief: The results of a filter are not mutable. I cannot directly add/edit rows to the cells in and around a filter result due to a #REF Error: not expanded because it would overwrite data error. ...
1
vote
1answer
966 views

Compare two lists and produce a list of names not duplicated

I have one list of total available people and the second list of people who have been assigned. I would like to auto-populate a third list of the people (from the first list) who have not been ...
1
vote
0answers
58 views

How can I list all cards I am watching in Trello?

How could I list all watched cards (or as a further refinement all watched cards I am not working on)? I couldn't see anything immediately on the pages regarding watching, filtering or searching.
2
votes
1answer
27 views

Google Sheets sort question

I used to know this a few years ago and for the life of me can't figure it out and can't find an answer in Google help. I know how to sort columns, but only by the default A-Z format, how can I sort ...
2
votes
1answer
35 views

Perform functions (AVERAGE, STDEV) on rows grouped by unique values in Google Sheets

I would like to be able to group rows based on unique values and then output the AVERAGE and STDEV values for those groups. Using the table below as an example, I would like to determine the AVERAGE ...