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)))
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})\.")))