Hot answers tagged

46

Use the following formula to obtain the query you want. Formula =FILTER(A:A;(A:A="Combat Medic")+(A:A="Universal")) Explained In the reference it is explained by Ahab that + and * can be used as boolean expressions, as OR and AND. The solution with the IFERROR (see revision history) is of no use in this case, unless you want to find a particular string: ...


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

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


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

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

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

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

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

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

if your formula is: =FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10) then just don't include the last number of closing range: =FILTER(A2:B, A2:A > 5, D2:D < 10)


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

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

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


1

I assume you want to get the values in K that are not appearing in L ? If so (and assuming your data starts on row 2), try: =filter(K2:K, isna(match(K2:K, L2:L,0))) If you only want to see unique values, then try: =unique(filter(K2:K, isna(match(K2:K, L2:L,0))))


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