Hot answers tagged

7

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


6

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 counted. Our example would give us 9 From this number we subtract 7 since you want the average of the last 7 values: =COUNT(A:A)-7. This will give us a 2. Now ...


4

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'!O:O ="NO" ) Explanation Google Sheets doesn't have a built-in function called CONTAINS. To evaluate that a date-time value met a date only criteria, round ...


3

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-handle operation relies on gsheets to find a pattern in your selection and repeat or continue it. If it doesn't find a pattern it repeats the elements you have ...


2

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 using sort() limit the output of that column to 7 rows (using ARRAY_CONSTRAIN) Take the average ... EDIT: Sorry for misunderstanding. The above averages the ...


2

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


2

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 appear to be trying to analyse data that has already been analysed. A more conventional solution may be to collect all the data together in one place (sheet) ...


2

@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

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 any word "golf" or "ski". If you want to check more then two words, the easy way is to write them into a separate list, say K1:K3: golf ski badminton and then ...


2

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 resulting date column as dates, since by default is will use the raw number format of the data (i.e., the number of days since December 30, 1899, which will be ...


2

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)


2

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)=false))} Meaning, you get circular reference error because you refer to column A by using ISBLANK(A:A) in your formula. Your formula will work if you place your ...


1

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) of certain length (5) from a starting position, in this case the result of find plus 10 characters.


1

short formulas: =REGEXEXTRACT(A1;"_1""\: ""(.{5})") =REGEXEXTRACT(A1;"_1""\: (.{7})") =REGEXEXTRACT(A1;"rev2_1""\: ""(.{5})") =REGEXEXTRACT(A1;"rev2_1""\: (.{7})") =REGEXEXTRACT(A1;"rev2_1""\: ""(.+)\"", ""rev2_2") =REGEXEXTRACT(A1;"rev2_1""\: (.+)\, ""rev2_2") =REGEXEXTRACT(A1;"_1""\: ""(.+)\"", ""rev2_2") =MID(A1;32;5) this will search for what is ...


1

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),"rev2_2",""),CHAR(34),""),",",""))


1

=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, B20:B="rock")))


1

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 be to have all data in hidden columns of the first sheet and then call for them from the second sheet with =ARRAYFORMULA({Names!A:B,Names!D:D})


1

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, 0))) =FILTER(A1:A, NOT(COUNTIF(B1:B, A1:A))) in case you need to use FILTER formula to return evaluation between two ranges, and those two ranges are of ...


1

=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 Col1 is not null", 0), ARRAYFORMULA(IF(QUERY({ TRANSPOSE(SPLIT(REPT(CONCATENATE(C2:C&CHAR(9)), COUNTA(D2:D)), CHAR(9))), TRANSPOSE(SPLIT(CONCATENATE(REPT(...


1

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 '.*[0]+.*'"; 0)


1

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


1

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 drag it to the right and down


1

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 #REF! error if D column exists: =ARRAY_CONSTRAIN(FILTER(A2:B; ISERROR(MATCH(A2:A; B2:B; 0))); 1000; 1)


1

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));"True";"False")


1

You should move your 3 unique sport values to 3 additional columns: Ski Badminton Golf where you could filter any sport with values: true/false select Cell formula for the additional columns: =IF(IFERROR(FIND(E$3,$B4)),TRUE(),FALSE()) my example UPD: Second solution - Additional one column with unique left-column value and filter by search I've ...


1

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


1

It would seem from the description that you need all non-empty values of this column. In this case perhaps this formula would give you what you want: =UNIQUE('Sheet Name'!A$2:A) or if you want not unique, but non-blanks simply =FILTER('Sheet Name'!A$2:A, NOT(ISBLANK('Sheet Name'!A$2:A)))


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