Google Spreadsheet Advanced Formulas

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:


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



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


  • =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!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: