Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
14

There is no need to jump into GAS, standard spreadsheet functions handle this easily. Please paste the following into a cell in your example =iferror(filter($A$2:$A, match($A$2:$A, B2:$B , false)), "no match") The match function generates a cartesian product of the two ranges full of errors except where a match is found. It returns an index into the 2nd ...


11

=SUMIF(D:D,"bob",C:C) in C column are numbers, in D column are ("bob" or others) if you wish do not sum all column you can use for example =SUMIF(D2:D6,"bob",C2:C6) Note: Text is case sensitive (Bob is not equal bob)


10

I bashed up a comparison sheet for the formulas between MS Excel 2010 and Google Spreadsheet. Comparison was based on the names only. See snapshot of the current situation: If you want to make sure that Google Spreadsheet files are properly converted and still work in MS Excel 2010, then don't use the following formulas: ADD ARRAYFORMULA CONCAT CONTINUE ...


10

Try Shift+Ctrl+: and Ctrl+; Those are the keyboard shortcuts for "insert time" and "insert date" respectively. (based on pnuts' comment)


7

This will work, based on a table with upper-left corner in A1, modify to suit actual: =INDEX(A1:E7, MATCH("Course 3", A:A, 0), MATCH("Pablo", 1:1, 0))


7

Solved with: =INDIRECT( ADDRESS( 4, COLUMN() ) ) ADDRESS gives me a string like "$A$1" and INDIRECT makes it a cell reference.


5

You can use the INDIRECT function. Here is how it works: And watch it live at this link


5

I've been able to find a solution for my problem. Using =ARRAYFORMULA(Sheet1!A1:A) in the top of a column Google Docs will update the whole column, without the need for functions in each cell. Thus, deleted or inserted rows will not cause trouble anymore. I found this answer (by brettathds) at the Google Support forums.


5

Short answer QUERY() allow the use of a subset of SQL but this subset does not include the FROM clause. QUERY() require a single array as the source table, so it could not be used, instead use ARRAYFORMULA(), TRANSPOSE(), JOIN(), SPLIT(), REPT(), FILTER(), COUNTA(), SORT() and the division (/) and concatenate (&) operators to build a "flexible ...


5

First, I'd split the data from column A into two columns. In this case it looks like the length is consistent so it makes it easier (I can demo the other if need be). Get the left chunk with =left(A2,11) Get the right chunk with =right(A2,10) Now you have Element 1: | Value 1.1 To flip the unique elements use =transpose(unique(B2:B7)) Now you have ...


5

=AVERAGEIF(A1:Z1,"<>") "<>" on its own means "not equal to an empty string" in Google Sheets.


5

You could use INDEX without a row parameter to return a column For example, =INDEX(GOOGLEFINANCE("TSLA", "price", TODAY()-30, TODAY(), "DAILY"),,2) will return the second column fully. However, To fully manipulate the array, you can use QUERY: =QUERY(GOOGLEFINANCE("TSLA", "price", TODAY()-30, TODAY(), "DAILY"), "Select Col1,Col2 where Col2>355 ...


4

Answered I received in the Google Support Forum: =counta(iferror(filter(B:B,A:A="name1",B:B="yes")))


4

I bashed up a comparison sheet for the formulas between MS Excel 2013 and the new Google Spreadsheet. Comparison was based on the names only. See snapshot of the current situation. Screenshot If you want to make sure that the new Google Spreadsheet files are properly converted and still work in MS Excel 2013, then don't use the following formulas. ...


4

Google Docs has size limits. There can be 2 million cells containing formulas. You can compare the Excel functions with this Google Spreadsheet function list. While there are some common functions, Google Spreadsheet does have its own set of formulas Google spreadsheets also have complexity limits. Every time a cell is updated, any cell that references it ...


4

This is an old post, but I was looking into this recently and figured I'd chime in. Depending on you needs, the easiest answer I could think of is by creating a drop down menu and referencing a function on another cell. Example: Select A10:A20 > right click > Data Validation Criteria = "List from range" > F2 Formula in cell F2: =mult(now(),1) To ...


4

Formula Instead of =FILTER( Recherche!B4:N , Recherche!O4:O1002 = "Beleggersprofielen"| Recherche!C4:C1002 = "11 - 20" ) Note: Added a breakline for readability. This haven't affect how the formula works. Try =FILTER( Recherche!B4:N1002, (Recherche!O4:O1002="Beleggersprofielen")+(Recherche!C4:C1002="11 - 20")) Note: FILTER require that the ...


4

Formula =ArrayFormula(FILTER(A4:C7,ISERROR(match(A4:A7,D4:D5,0)))) Explanation MATCH returns an error if the invited doesn't appear on the attended list. ISERROR converts errors to TRUE and values to FALSE This result is used as the filtering criteria. The result is, the list of absents: | 001 |Tim |Smith | 003 |Amy |Wilson Just add the ...


4

Try: =D5*index(GoogleFinance("CURRENCY:USDINR","price","23/07/2017"),2,2) The error message seems clear enough to me: Function MULTIPLY parameter 2 expects number values. But 'Date' is a text and cannot be coerced to a number. Why 'Date'? ... because, as mentioned for GOOGLEFINANCE: Historical data, even for a single day, will be returned as an expanded ...


4

Please try: =day(today())-1 DAY TODAY


3

Ultimately, you need to do what you already predicted: adding a leading zero. The TEXT function will make that happen like this: =TEXT(A2, "00") I've created a formula that takes on the complete column, filter for empty cells, brings together the MONTH and DAY and SORTs the lot. Formula =SORT(ARRAYFORMULA(TEXT(MONTH(FILTER(A2:A;A2:A<>""));"00") &...


3

This little script will compare two ranges, as a formula: function COMPARE(array1, array2) { var array = []; for(i=0; i<array1.length; i++) { for(j=0; j<array2.length; j++) { if(array1[i][0] == array2[j][0]) { // the extra square brackets will make it a 2D array, // aligning it vertically array.push([array1[i][...


3

If you use SUBTOTAL function in place of your SUM functions then subsequent SUBTOTAL functions will ignore the numbers returned by other SUBTOTAL functions, hence avoiding double counting. For example if A5 has this formula =SUBTOTAL(9,A1:A4) [the first argument dictates the function, 9 = SUM] then if you use this formula in A6 =SUBTOTAL(9,A1:A5) you ...


3

Figured it out... took me a while... INDIRECT() has to wrap the entire reference. In order to achieve what I needed, the formula looked like this: =INDIRECT("Sheet2!A"&A1)


3

Let's look first at why the function you've posted isn't working. VLOOKUP needs a two-dimensional data range to work with. It looks for your search_key (param 1) in the first column of range specified (has to be the first column - an unfortunate limitation of VLOOKUP), and returns a value from the matching row. The column returned is specified by param 3, ...


3

You can do this with a combination of the INDIRECT and MATCH functions. In the last column of your table add the following formula in line with the data that you would like to determine the max of: Google Spreadsheet =INDIRECT(ADDRESS([Name_Row], MATCH(MAX([Data_Span]), [Data_Span], 0) + [Column_Offset])) Excel =INDIRECT(ADDRESS([Name_Row], MATCH(MAX([...


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