# Tag Info

112

By using the QUERY function you can achieve your goal. Formula =QUERY(Data!A1:B6;"select A, sum(B), count(B) group by A") Screenshot Using other functions, makes it achievable as well. add this formula in A2: =UNIQUE(Data!A2:A) add the following formulas in B2 and C2 and copy down to B4 and C4 respectivey =SUM(FILTER(Data!\$B\$2:B;Data!\$A\$2:A=A2)) =...

59

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.

44

It is also possible to use Google Apps Scripts to achieve automatic in-place sorting of the data. This may be more difficult to achieve and more error-prone (I'd still go for William Jackson's solution, +1 BTW), but I thought it was interesting enough to show. I have a sheet that looks like this: I added a new script, using these steps: in the menu, go ...

43

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

43

17

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

10

You can format the date like this: =QUERY(B2:G124; "select * where B = date '" & text(Q4,"yyyy-MM-dd") & "'") To sum up the values in column C, do this: =QUERY(B2:G124; "select sum(C) where B = date '" & text(Q4,"yyyy-MM-dd") & "'") Not sure how to sum up multiple columns though - still pretty new to this myself!

10

Here's a generic script that will autosort based on the 1st column, and assumes a Header row. To create a script: In the menu, go to Tools -> Script Editor... In the empty code window, paste the following code, which will run automatically whenever a cell is edited: // LinkBack to this script: // http://webapps.stackexchange.com/questions/7211/how-can-...

10

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

8

You can use the following formula for that: INDEX(QUERY(Inkomster!A2:G;"SELECT SUM(C) WHERE G="&B3&" AND F='"&A3&"'"),2,0) The 2 references the row number and the 0 or 1 references the column number

8

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

8

=if(search("sites",B2)>0,1,0) =if(IFERROR(search("sites",B2)>0,0),1,0) Both work. The second writes a 0 if there's an error.

7

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.

7

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

7

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

6

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

6

I have had a similar issue, with trying to sort a UNIQUE dataset. the way i got around it was by creating another sheet and using the '=QUERY()' function to call the sheet with the UNIQUE list on it. This way you can add a ORDER BY statement to the query. so in your example, Expenses has the source data, the summary table has the Summary data (with the ...

6

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

6

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

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

5

It's a kind of tricky, but it is possible with a helper Range and some concatenation. What needs to be done: Create a named range, COLS, to carry the column letters like this: A B C D E ... Do it in a vertical way as shown. Assemble the query string like this: =QUERY( B:J, "SELECT AVG(" & INDEX(**COLS**, COLUMN(J1)) & ") GROUP BY " &...

5

You can transform the columns by using TRANSPOSE, i.e., assume "First member, Second member, Third member" are A1:A3, you can use function in somewhere, = TRANSPOSE(A1:A3) will fill column as below, First member Second member Third member To repeat the column before and after team members, you can try, = TRANSPOSE (SPLIT(REPT(CONCAT(A1, ","), ...

5

With the following little script you can do that. Code function mySQL(ref, dates) { var output = []; for(var i = 0, iLen = ref.length; i < iLen; i++) { var user = ref[i], begin = new Date(ref[i]), end = new Date(ref[i]); for(var j = 0, jLen = dates.length; j < jLen; j++) { var date = new Date(dates[j]); if(date >= ...

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