Hot answers tagged

7 votes
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
6 votes

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
4 votes
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
3 votes
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
3 votes

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
2 votes

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
2 votes

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
2 votes

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 ...
2 votes
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
2 votes

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)))
2 votes

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
2 votes
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
2 votes
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
2 votes
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