journal

Click above to watch a video demonstration

If you have spent a lot of time working in spreadsheets there is likely little chance that you haven't heard of the countifs or sumifs function and a far more likely chance you've actually used both.

And for good reason too.

These functions are super simple, but really powerful because you can use them to quickly filter your data and calculate some basic metrics that allow you to better understand a large data set.

And you might have already guessed, but the same holds true for these sorts of calculations in Onspring as well.

For example let's say that I have a super simple projects app and each project in my app can have many related task records. Something like this:

When the number of tasks related to the project is small it is pretty easy to just pop open the content record and get an idea of what is going on with the project, but as the number of related records grow it would probably be helpful to have some metrics that quickly summarize the state of things.

Perhaps some simple counts that tell us...

• How many total tasks there are
• How many of those tasks are complete
• How many of those tasks are in progress
• How many of those tasks are of a particular type

Something like this:

Which is pretty straightforward to do because each of those counts are based upon no more than a single criteria and Onspring already gives us a really handy CountIf function to work with. Below is an example of the CountIf function at work for counting the number of complete tasks.

CountIf({:Tasks::Status}==[:Complete], {:item::Record Id});

But what if I want to continue building these task counts and subdiving them not just by their status or type, but by both. Meaning I want to create my counts using multiple criteria. This is when you'd most likely default to reaching for something like countifs or sumifs.

However in Onspring there are not built-in countifs and sumifs functions and if you look at the documentation for the CountIf or SumIf functions you'll see they only accept a single criteria parameter. For example:

This might be where an admin isn't sure what to do next and it's where I'd like to help. Turns out this problem is completely solvable and the answer really isn't all that complex. It just requires looking at things from a different perspective.

Let's start by introducing a formula field at our task records that will allow each individual task record to identify itself as having met the criteria or not to be included in a particular count at the project record.

Take for example that I want to have a count at the project record of all the incidental tasks that are in progress. So let's add a formula field at the task record which indicates whether the task is exactly that - incidental and in progress.

Here is an example of that field and it's formula syntax:

if ({:Type}==[:Incidental] && {:Status}==[:In Progress]) return [:True];
return [:False];

With that in place at our task records our project records will be able to use that field to identify which related task records have met a particular criteria and therefore should be included as part of a particular count.

And here is an example of implementing a count of all in progress incidental tasks at the project record.

CountIf({:Tasks::IsIncidentalAndIsInProgress}==[:True], {:item::Record Id});

This type of pattern can then be repeated as many times as you need to get at all the different sums and counts you need. You can also use as complex a criteria as you want to identify the records to include or exclude from your calculations.

I hope this is helpful and makes your job as an admin just a little easier.