132

You can use REGEXMATCH: =IF(REGEXMATCH(A1, "sites"), 1, 0) To explain, REGEXMATCH returns true if and only if the argument is a substring of your string.


25

Please try: =QUERY('Form Responses 1'!A:N,"SELECT C,E,G,H where G is not null or H is not null order by C,E")


18

This returns a #VALUE! error if the search term isn't found: =IF(SEARCH("sites", B2) > 0, 1, 0) This uses the same logic but catches the error and returns a 1/0 for success/failure: =IF(IFERROR(SEARCH("sites", B2) > 0, 0), 1, 0)


16

=UNIQUE({A:A;B:B;C:C}) =ARRAYFORMULA(UNIQUE(QUERY(TO_TEXT({A:A;B:B;C:C}), "select Col1 where Col1 is not null")))


15

In addition to using the ever-useful UNIQUE function around FILTER, as user79865 suggests, you can also use UNIQUE around the original QUERY function (which may be more applicable and helpful in other scenarios to preserve the options and flexibility of QUERY when FILTER is not enough). Like so: =UNIQUE(QUERY(A1:B6,"SELECT A WHERE B = 'SubCatA'"))


14

Another option without a script is: =QUERY(A1:C3,"SELECT * ORDER BY C") The range is restricted (A1:C3) because where the order is ascending blank entries will appear first.


11

If you, as Ryan Shillington suggests, want to know the number of cells within a range that has "sites" in it, you might try: =COUNTIF(A1:A100;"*sites*") The * acts as a wildcard notion, so it will look for a match anywhere within the cell, not just an exact match for the whole cell. I've also done this with a cell reference instead of "sites", but ...


10

Is it at all possible to use column headers like this? Yes, it is possible. First, you need to use MATCH to get the column number of the column whose value matches 'Name'. Then you need to use ADDRESS to get the cell reference. Finally, you need to use SUBSTITUTE to remove the row number from the cell reference. =QUERY(Sheet1!A1:C,"SELECT A, B, C where "&...


9

In Google Spreadsheets, I would do it a bit different. Formula =COUNTA(SPLIT(A1, " ")) Explained The SPLIT function is only available in Google Spreadsheet and will split the cell's content on every space (" "). The COUNT function will simply count the instances. The Excel formula gives the same answer, but a bit more laborious: =LEN(TRIM(A1))-LEN(...


9

Why not using Pivot Table? It will give you more options and controls than writing a custom formula. Controls live Add Columns, Rows, Values, Filters


9

You need to add a helper column within the QUERY formula, like so: =QUERY( {A1:B6, A1:A6}, "SELECT Col1, COUNT(Col3) WHERE Col2 = 'SubCatA' GROUP BY Col1 LABEL COUNT(Col3) ''" ) Adding another QUERY formula will help to remove the helper column: =QUERY( QUERY( {A1:B6, A1:A6}, "SELECT Col1, COUNT(Col3) WHERE Col2 = 'SubCatA' GROUP BY ...


9

A solution without REGEX: =if(iserror(FIND("sites",B2)),0,1) capitalization matters and if logic must be reversed, given that answer is true when the string is not found.


8

The =QUERY() language WHERE clause support regex; so you can "simulate" IN functionality using a regex group - ie: =QUERY(A:B, "SELECT A,B WHERE A MATCHES '(2|3)'")


7

Make a new sheet inside your current spreadsheet. Put two columns in it: VoiceType Sort order SOPRANO 1 CONTRALTO 2 TENOR 3 BASS 4 In your main worksheet (the one with the list of people), in a new column put a formula like this: =vlookup(upper(B2),Sheet2!A$2:B$5,2,0) Note that I have used the UPPER() function in case some of the ...


7

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


7

Based on this Google Forum answer, I've nested the query: =QUERY( QUERY( final!H2:H, "SELECT H, COUNT(H) GROUP BY H ORDER BY COUNT(H) DESC" ), "WHERE Col2 > 1") Note that the column reference in the outer query is case sensitive, and must be exactly Col2.


6

The best result would be to use the QUERYfunction: =SORT(QUERY(Data!A1:B6;"select A, sum(B), count(B) group by A");2;FALSE) Since the sum already has been sorted ascendingly, the FALSE statement in the formula will sort descending using column 2.


6

The third parameter you speak of in the QUERY function, is meant to control the headers. If set to be -1, then the Google Spreadsheet will depict its own choice in choosing headers, based on the data available: I've used the following data set: If set to be 0, then no header will be used, leaving: If set to be 1, then the first row will be used, leaving: ...


6

Please try syntax of this kind: =QUERY(Responses!B1:I, "Select B where G contains '"&$B1&"'")


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


6

=unique(flatten(A1:Z)) I think this will work and it worked for me.


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