# Tag Info

## Hot answers tagged filter

Accepted

### Google Sheets Filter Multi Column List With Another List Using A Function

Use a FILTER/MATCH combination. Example on your data below: =filter(C52:F57; match(C52:C57; I53:I54; 0))
• 1,539

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

TL;DR =AVERAGE(OFFSET(A:A;COUNT(A:A)-7 + MATCH(TRUE;INDEX(A:A<>0);0)-1;0)) Example Explanation First we count how many numbers are in row A with =COUNT(A:A). Cells containing text won't be ...
• 290
Accepted

### How can I filter a set of data based on a column containing a given date in sheets

Short answer Try =FILTER( 'Output Checker'!A:P, ROUNDDOWN('Output Checker'!A:A)=A2, 'Output Checker'!L:L <2, 'Output Checker'!M:M ="NO", 'Output Checker'!N:N ="Yes", 'Output Checker'...
• 42.3k
Accepted

### Increment Cell Reference in Formula by 10

On Locking Ranges using "\$" The first problem I see is the \$ sign is placed before the row. This tells the spreadsheet that it needs to LOCK that row (2 & 12) from changing numbers. The drag-...
• 855

### QUERY with Week Number

weeknum is not a valid scalar function so you need to trick it like this: =ARRAY_CONSTRAIN(ARRAYFORMULA(QUERY({A2:B, WEEKNUM(A2:A)}, "where Col3 = 21")), 999^99, 2) =FILTER(A2:B, WEEKNUM(A2:A)=21)
• 24.8k

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

Yet another way: =AVERAGE(ARRAY_CONSTRAIN(SORT(FILTER(A:A, ISNUMBER(A:A)), 1, 1), 7, 1)) Explantion: filter out all numeric values turn the column upside down (so that last values are above) by ...
• 2,439

### FILTER(condition OR condition) syntax?

=FILTER(A:F, REGEXMATCH(A:A, "Combat Medic|Universal")) =QUERY(A:F, " where A matches 'Combat Medic|Universal'", 0)
• 24.8k

### Filter multiple categories Spreadsheet

You may use an additional column to filter categories in place. Here's the formula to check if any text matches "golf" or "ski" : =REGEXMATCH(A2,"ski|golf") It will give True if cell A2 contains ...
• 225
Accepted

### Dynamic range for filter function

You might use INDIRECT within a query, say: =query(indirect(\$B4&"!D:H");"select sum(H) where D ='"&B\$3&"' label sum(H) ''") but really you seem to have backed yourself into a corner. You ...
• 16.9k

### Google Sheets Filter Multi Column List With Another List Using A Function

@ttarchala provided a very helpful answer. If you need to invert the selection you can simply do it like this: =filter(C52:F57; ISNA (match(C52:C57; I53:I54; 0)))

### Google Sheets' Query or Filter solution

Wilson. Here is another approach (assuming the date is in A1:C): =ArrayFormula(IFERROR(VLOOKUP(UNIQUE(A:A),QUERY(A:C,"Select * Order By B Desc",1),{1, 2, 3},FALSE))) Just be sure to format the ...
• 4,667
Accepted

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

=UNIQUE(FILTER(A2:A, A2:A<>""))
• 24.8k
Accepted

### Filter causing Circular Reference Error - Not obvious why?

The issue is that in your sheet Result in cell A1 (that is in column A) you have a formula like ={filter(SheetA!A1:A, arrayformula(ISBLANK(A:A)=false)), filter(SheetB!A1:A, arrayformula(ISBLANK(A:A)=...
• 11.8k
Accepted

### How to filter to show data that contain numbers greater or equal to "x"?

I replied over on reddit, but either of these will do it =ARRAYFORMULA( FILTER( {A2:A,D2:D}, --REGEXEXTRACT(D2:D,"Volume: (.*) Volume")) or if you want to use your filter views, use ...
• 223
1 vote
Accepted

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

Assuming that the value of rev2_1 always will have 5 characters use =mid(A1,find("rev2_1",A1)+10,5) Explanation find finds the position of the first character of rev2_1 mid gets a text (substring) ...
• 42.3k
1 vote

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

short formulas: =REGEXEXTRACT(A1;"_1""\: ""(.{5})") =REGEXEXTRACT(A1;"_1""\: (.{7})") =REGEXEXTRACT(A1;"rev2_1""\: ""(.{5})") =REGEXEXTRACT(A1;"rev2_1""\: (.{7})") =REGEXEXTRACT(A1;"rev2_1""\: ""(...
• 24.8k
1 vote

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

There might be a shorter way, such as with string manipulation (e.g., MID) or regular expressions (REGEXEXTRACT), but this works: =1*trim(substitute(substitute(substitute(index(SPLIT(A1,":"),3),"...
• 16.9k
1 vote

### How to count duplicate names as one value in Sheets?

=TRANSPOSE(QUERY(QUERY({A20:B}, "select Col1,Col2,count(Col1) group by Col1,Col2"), "select count(Col1) where Col2 = 'rock' label count(Col1)'Rock Count:'")) =COUNTA(UNIQUE(FILTER(A20:A, ...
• 24.8k
1 vote
Accepted

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

This is not doable as you imagine it, because the only link/reference between C column and an array of A:B column is row order which upon filter change on the first sheet is lost. The solution would ...
• 24.8k
1 vote

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

example for those who need to use FILTER formulas and struggle with range mismatch of FILTER =FILTER(A1:A, MATCH(A1:A, B1:B, 0)) =FILTER(A1:A, COUNTIF(B1:B, A1:A)) =FILTER(A1:A, ISNA(MATCH(A1:A, B1:B,...
• 24.8k
1 vote

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

=ARRAYFORMULA(QUERY({QUERY({ TRANSPOSE(SPLIT(REPT(CONCATENATE(C2:C&CHAR(9)), COUNTA(D2:D)), CHAR(9))), TRANSPOSE(SPLIT(CONCATENATE(REPT(D2:D&CHAR(9), COUNTA(C2:C))), CHAR(9)))}, "where ...
• 24.8k
1 vote

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

put this for example in C1: =QUERY(A:A; "select A where A matches '.*[0-9]+.*'"; 0) to kill off zeros you can try this one: =QUERY(A:A; "select A where A matches '.*[1-9]+.*' and not A matches '.*[...
• 24.8k
1 vote
Accepted

### Copying values between Google Sheets

this could be solved in many ways, one of which is: =IMPORTRANGE("URL-of-your-spreadsheet"; "Sheet1!A:Z")
• 24.8k
1 vote

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

cell A2: =UNIQUE('Sheet1'!A2:A6) cell B1: =QUERY('Sheet1'!B1:D1) cell B2: =IFERROR(INDEX(FILTER({'Sheet1'!B\$2:B\$6};{'Sheet1'!B\$2:B\$6}<>""; ('Sheet1'!\$A\$2:\$A\$6)=\$A2);1);) and ...
• 24.8k
1 vote
Accepted

### Compare two lists and produce a list of names not duplicated

C2: =ARRAYFORMULA(FILTER(A2:B; ISERROR(MATCH(A2:A; B2:B; 0)))) bonus knowledge: to achieve the opposite: =FILTER(A2:A; REGEXMATCH(A2:A; "^"&JOIN("|"; FILTER(B2:B; LEN(B2:B)))&"\$")) to avoid #...
• 24.8k
1 vote

### Google Sheets formula for “if contains” from range

for "true / false" part: =IF(ISTEXT(REGEXEXTRACT(A1;"sites"));"True";"False") for "more words crossreferenced with another sheet" part: =IF(ISTEXT(FILTER({'sheet1'!A1:A3};{'sheet1'!A1:A3}=A1));"...
• 24.8k
1 vote

### Find latest entry of a duplicate row in Google Sheets

Assuming dates in Column A and document names in Column B: =query(A:B,"select B, max(A) group by B") Reference QUERY function
• 42.3k

Only top scored, non community-wiki answers of a minimum length are eligible