⤴︎ How To Create Every Type Of Rollup With Formulas

The complete guide to creating rollups with formulas 🙌

Hi Notioneers!

I know, it’s been a while, I’m sorry, I’ve been busy making apps lately 🤓 Today’s post is more of a blog post than a newsletter but I’m sending it from Substack to make sure that you get it first.

As I’m sure you’ve heard by now, Notion’s just released a huge upgrade for their formula functionality that’s unlocked so many new opportunities.

An Intro To Notion Formulas 2.0

I recorded a quick summary of the changes and the new things that we can do with formulas now in this video -

I also just wanted to give a shout out to Benjamin Borowski. He’s a true Notion formulas wizard and will be creating lots more resources for formula creators soon.

Creating Rollups With Formulas

One of the most exciting additions to our toolkit is the ability to create rollups with formulas.

In the rest of this post, I’ll share how you can replicate all of the current rollup options with formulas so that you can quickly get started when you need to include rollups in your formulas. If you want to view any of these formulas in a Notion database or with nicer formatting, you can check these out here.

Obviously you can build on these formulas for all sorts of things, like this. But these will just be simple examples or as simple as possible anyway, check out the formula to calculate the median average 😅.

General

Rollups for every type of property.

Show original

map(prop("Tasks"), current.prop("Name"))

Count all

map(prop("Tasks"), current.prop("Name"))
.length()

Text, Numbers & Dates

Rollups for text, number and date properties.

Show unique values

map(prop("Tasks"), current.prop("Name"))
.unique()

Count values

map(prop("Tasks"), current.prop("Name"))
.filter(current)
.length()

Count unique values

map(prop("Tasks"), current.prop("Name"))
.unique()
.length()

Count empty

map(prop("Tasks"), current.prop("Name"))
.filter(current.empty())
.length()

Count not empty

map(prop("Tasks"), current.prop("Name"))
.filter(!current.empty())
.length()

Percent empty

let(
  /* Create variable */
  taskHours, map(prop("Tasks"), current.prop("Name")),
  /* Count empty */
  taskHours
  .filter(current.empty())
  .length()
  /
  /* Count total */
  taskHours
  .length()
)

Percent not empty

let(
  /* Create variable */
  taskHours, map(prop("Tasks"), current.prop("Name")),
  /* Count empty */
  taskHours
  .filter(!current.empty())
  .length()
  /
  /* Count total */
  taskHours
  .length()
)

Numbers

Rollups based on number properties.

Sum

map(prop("Tasks"), current.prop("Hours"))
.sum()

Average

map(prop("Tasks"), current.prop("Hours"))
.sum()
/
prop("Tasks").length()

Median

let(
  sortedHours,
  map(prop("Tasks"), current.prop("Hours"))
  .sort(),
  if(
    /* If true then the length of the list is an odd number
    so the list contains a middle number */
    mod(sortedHours.length(), 2),
    /* Get number from the middle of list */
    at(
      sortedHours,
      /* Get index for middle of list */
      floor(sortedHours.length() / 2
      )
    ),
    /* If false then we need to get the two numbers from the middle of the list
    add them together and divide the result by 2 */
    lets(
      mid1, at(sortedHours, sortedHours.length() / 2),
      mid2, at(sortedHours, sortedHours.length() / 2 - 1),
      (mid1 + mid2) / 2
    )
  )
)

Min

map(prop("Tasks"), current.prop("Hours"))
.min()

Max

map(prop("Tasks"), current.prop("Hours"))
.max()

Range

map(prop("Tasks"), current.prop("Hours"))
.max()
-
map(prop("Tasks"), current.prop("Hours"))
.min()

Dates

Earliest date

map(prop("Tasks"), current.prop("Due"))
.sort()
.first()

Latest date

map(prop("Tasks"), current.prop("Due"))
.sort()
.last()

Date range

let(
  /* You can change "days" to hours, weeks, months or even years */
  timeframe, "days",
  dateBetween(
    map(prop("Tasks"), current.prop("Due")).sort().last(),
    map(prop("Tasks"), current.prop("Due")).sort().first(),
    timeframe
  )
  + " "
  + timeframe
)

Checkboxes

Rollups based on checkbox properties.

Count checked

map(prop("Tasks"), current.prop("Complete"))
/* You could actually leave the brackets empty here,
as the filter will look for true by default */
.filter(current == true)
.length()

Count unchecked

map(prop("Tasks"), current.prop("Complete"))
/* You could actually leave the brackets empty here,
as the filter will look for true by default */
.filter(current == true)
.length()

Percent checked

/* Uncomment the comments in this formula to round the result to 0 decimal places */
/* round( */
map(prop("Tasks"), current.prop("Complete"))
.filter(current == true)
.length()
/
map(prop("Tasks"), current.prop("Complete"))
.length()
/* * 100) / 100 */

Percent unchecked

/* Uncomment the comments in this formula to round the result to 0 decimal places */
/* round( */
map(prop("Tasks"), current.prop("Complete"))
.filter(current == false)
.length()
/
map(prop("Tasks"), current.prop("Complete"))
.length()
/* * 100) / 100 */

I hope this is a useful reference point for you in the future. If you have any questions, please feel free to reply in the comments.

👋 until next time

Alex aka aNotioneer