Pivot Tables

``````=query('demo-source'!A:Z;"select A,sum(C) group by A pivot B")
``````

Sum all available lines for arbitrary number of colums per line:

``````=MMULT('sourcesheet'!B2:Z,TRANSPOSE(ARRAYFORMULA(COLUMN('sourcesheet'!B2:Z)^0)))
``````

Solution Source

Query a source data range for values according to an arbitrary number of rows in the destination area:

``````=ARRAYFORMULA(MMULT(SIGN(<destination condition>=TRANSPOSE(<source condition>));<source data>)
``````

Example:

``````=ARRAYFORMULA(MMULT(SIGN('Worksheet'!A2:A=TRANSPOSE('Source'!A2:A));'Source'!B2:D)
``````

This is a pretty sick little piece of code – it allows you to match any type of data columns to an arbitrary list of things based on a unique key (like a sql join).
Solution Source

More powerful JOIN implementation for multiple conditions:

``````=arrayformula(if(len(A2:A)>0,sumif(‘other_sheet’!A:A&’other_sheet’!L:L, A2:A&“token“,‘other_sheet’!J:J),))
``````

Explained:

• =sumif can only accept one condition and =sumifs does not work with arrayformula
• arrayformula is needed so that the JOIN works for dynamic lists (for example form another =query())
• ‘other_sheet’!J:J is the column from which the values should be summed up
• ‘other_sheet’!A:A is the column the first condition should be applied to
• ‘other_sheet’!L:L is the column the second condition should be applied to
• A2:A is the first condition (here it means that the cell value should equal to the corresponding value in A2:A)
• “*token*” is the second condition (here it’s a kind of regular expression that finds the token in the cell content).
• two or more columns as well as conditions can be concatenated according to https://productforums.google.com/forum/?#!msg/docs/OrriYjusT6k/2XSeq1FhH3oJ

Use: This is great if you have a dynamic list (for example coming from a query() and you want to match each row with a value from another table (in this example from ‘other_sheet’) based on multiple conditions. The first condition normally makes comparisons based on a unique key so that the matched rows never exceed one row.

Use =query() to filter rows of a sheet that are empty:

``````=query(source, "select * where A = "&char(34)&char(34)
``````

Calculate remaining days in the current year or the current month:

``````=day(eomonth(today(),0))-day(today())
``````

and

``````=date(year(today()),12,31)-today()
``````

parse an european date:

``````=DATE(value(REGEXEXTRACT(D2,"^\d{2}\.\d{2}\.(\d{4})")),value(REGEXEXTRACT(D2,"^\d{2}\.(\d{2})")),value(REGEXEXTRACT(D2,"^(\d{2})\.")))
``````