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.

0
votes
1answer
9 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
1answer
18 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 ...
2
votes
1answer
21 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
20 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 ...
1
vote
0answers
14 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.
1
vote
2answers
13 views

How to get a true when a character is NOT in range

I want to filter all cells in a range which are not empty and does not contain a certain character. So I tried everything with regexmatch =filter(Planning!G$5:G$37; regexmatch(Planning!G$5:G$37 ; "![...
0
votes
2answers
17 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
22 views

Copy the contents of a row to another sheet based on a specific value

I have a Google sheet with various rows of data, in one column (Column E), the values for each row follow the following pattern: A1 A2 B1 B2 C1 C2 etc. I want to copy the rows ...
1
vote
2answers
20 views

Counta unique string on filtered list

I'd like to count unique string values on a filtered list (For example, the value should be 3 in this screenshot): How can this be done in google sheets? Here's the spreadsheet: https://docs.google....
0
votes
2answers
14 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
34 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. ...
1
vote
2answers
69 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. ...
0
votes
1answer
18 views

Filter column based in other column (using query prefer)

I am trying to make a filter with a query based in a column, but I cannot make it works. https://docs.google.com/spreadsheets/d/1zKMW8vuyHRpS8eVEmRgAOg_dBQ0r2OIXnqV3_TFEm9o/edit?usp=sharing This is ...
1
vote
1answer
55 views

COUNT UNIQUE values by group ONLY IF NOT already counted in previous group

I hope to explain clearly what I'm trying to do. I have some data in column B, grouped as shown in column A. I'd like to count the unique values for each group present in column A, not taking into ...
1
vote
3answers
32 views

How to use FILTER to select only values repeated more than X times

I am trying to use the filter function to select from 2 columns, only the values repeated. I will give you an example: My column is like: Names Jobs Value - Gerard player 4 Gerard player 2 ...
0
votes
1answer
42 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 ...
0
votes
0answers
13 views

I have a specific excel list of emails that I would like to apply gmail filters to

I have an excel file of emails and I was wondering if it’s possible to get add gmail filters to the excel spreadsheet so that anytime a certain emailer emails me it will go straight to that gmail ...
2
votes
1answer
14 views

How do I filter really tiny columns in Google Sheets?

Sometimes in my filter views, whenever I have really small columns, the triangle icon to filter them does not show up at all. I'm able to temporarily resolve this by expanding the column, but it's ...
1
vote
1answer
17 views

How do I get rid of the blank line when I use UNIQUE in Google Sheets?

I used unique... the result is in column B, but I want results to look like column C. So what do I need to do to achieve this?
1
vote
3answers
88 views

Filter cell data using Query/Importdata

I am attempting to grab data from a cell, by using additional data in the cell above it to make sure its correct. Here is the data, each line is put into a cell by the formula. <total_count>6&...
1
vote
2answers
41 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
23 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
1answer
29 views

Conditional Formatting using Google Sheets

So I'm trying to build out a simple campaign tracker and I've been running into a few issues with the conditional formatting on google sheets. I need the cells on column C to change color and format ...
1
vote
2answers
9 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
25 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
8 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
33 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
1answer
18 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 ...
2
votes
2answers
181 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 ...
0
votes
3answers
111 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
18 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
1answer
48 views

How to count how often a value is submitted to a Google sheet via Google Forms per day, and put that count in different Google Sheets workbook

I have two Google Sheets workbooks, Workbook A and Workbook B. Workbook A is generated from a Google form that collects email addresses submitted from users. Workbook A has two columns next to each ...
0
votes
2answers
19 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 ...
1
vote
1answer
35 views

Return a value from a column if multiple criteria are met

Can anyone teach me how I can return a value from a column if multiple criteria are met where if: a cell in a column of another sheet contains a word in a cell (referenced) the same row in another ...
1
vote
1answer
14 views

How can I divide row data into multiple Sheets and have sort work corretly?

Say I have a Sheet with people info, containing their names and some other info. I add a filter to this Sheet to be able to sort alphabetically by lastname, or any other column. Now I want to add ...
0
votes
1answer
1k 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
2answers
522 views

Filtering Pivot table by multiple values

I have a pivot table below that I am trying to filter dynamically based on a separate list and am trying to figure out how to do this by referencing the relevant field. In the below example I have ...
1
vote
1answer
25 views

Using Google Sheets to dynamically add entries to column based on logic?

I have a list of people that I have a drop down for if they are attending or not to an event. If "Yes" I want them to be added to another column on sheet 2. Except I don't know how to add them ...
1
vote
1answer
19 views

Extract Value from Poorly formed JSON result

I have a Google script that returns the following result set, but when I try to get the value for asking_price or asking_price_condition it comes back undefined. I assume it's because of some {} ...
-1
votes
3answers
77 views

Parsing a non-nested JSON string in Google Sheets using formulas only

I have a cell with the following JSON data: {"rev2_0": "2,058", "rev2_1": "2,099", "rev2_2": "2,141", "rev2_3": "2,184", "revchg3_0": "3%", "revchg3_1": "6%"} In another cell, I want to get the ...
2
votes
1answer
38 views

Sum value in cells that contain both number and text

I need to sum a range of cells with text in them. Example: a1= "25,00 market" b1="24,00 credit card" c1="75,11 food" a2= "10,00 sweets" b2="9,00 market" c2="24,00 keyboard" I want to sum ...
1
vote
3answers
147 views

How to return what is missing from a 2 column list comparing against all possible combinations of 2 single column lists?

Sample data: A B C D E F 1 name handed in students assignments name missing 2 a 1 a 1 b 2 3 b 1 b 2 ...
0
votes
2answers
49 views

How to apply conditional formatting only if cell has X value? [google sheet] [duplicate]

I have a sheet with statistical data to which I had applied color scale conditional formatting. Some rows I set to have green for highest and red for lowest number in row and other rows the other way ...
0
votes
2answers
79 views

FILTER for multiple conditions including OR

How can I use FILTER in a single formula to check for multiple conditions, such as in this case? The filtered range must be equals1 AND notequal2 AND equals3 OR equals4 This is of course an ...
1
vote
2answers
32 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 ...
0
votes
1answer
645 views

Can you auto update the range for a filter in Google Sheets?

I have a Google Sheet document with many rows in it. I have several filters set up to view different collections of rows. The problem is, this sheet has new rows automatically added to it several ...
1
vote
1answer
592 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
1answer
426 views

Catch New Rows in Existing Filter Views

I have a series of filter views that filter my Google sheet based on the values in column B. I have 200 rows in my sheet, so the range for each filter view is A1:C200. Often, new rows get added to ...
1
vote
1answer
51 views

Google sheets - display only rows containing a digit in range [1-9]?

I have very little knowledge of Google sheets but I need to filter column A (a list of URLs) to display, for example, only URLs containing a digit in the text string. I see there's a filter button but ...
0
votes
1answer
421 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 ...