=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:
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>)
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).
More powerful JOIN implementation for multiple conditions:
- =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:
parse an european date: