While Calcapp has supported hundreds of functions since 2016, there have been conspicuous holes in our function coverage. Most notably, we have not supported the popular conditional functions AVERAGEIF, AVERAGEIFS, COUNTIF, COUNTIFS, MINIFS, MAXIFS, SUMIF and SUMIFS.
As you have probably gathered from the fact that the preceding paragraph is full of links to our formula documentation, we now have full support for these functions. We have also added support for PRODUCTIF and PRODUCTIFS, which are (as far as we know) exclusive to Calcapp and have been requested by spreadsheet users for years. They work precisely like the SUMIF and SUMIFS functions, but multiply numbers instead of adding them together.
It is possible to simulate these functions using just the IF function and regular calculations. However, that tends to produce very long formulas that are hard to maintain. With support for these conditional functions, we now enable many more spreadsheets to be easily converted to apps, with no hoops to jump through.
What can these functions be used for?
In their simplest form, these conditional functions accept an array as their first parameter and then apply an operation on its elements (like adding them together), but only if the element under consideration passes a test (a condition).
For instance, you can easily get the sum of all numbers of an array that are greater than 1. This formula does just that, using SUMIF:
The formula above returns 5 (2 + 32 + 3). It does not return 6 (1 + 2 + 31 + 2 + 3), as only 2 and 3 are greater than 1, as specified by the ">1"">1" parameter.
This formula returns the sum of all field values of the Field1:Field10Field1:Field10 range which are less than 3:
Before this release, an app author would have had to invoke IF multiple times to achieve the same result:
If you need multiple conditions, use the SUMIFS function:
The formula above returns 20, because that is the only element of the first array where both corresponding elements in the other arrays satisfy their conditions.
In the second array { 1, 2, 3 }{ 1; 2; 3 }, 2 and 3 satisfy the ">1"">1" condition, as they are both greater than 1. In the third array, { "Eve", "Eve", "Bill" }{ "Eve"; "Eve"; "Bill" }, the two first elements satisfy the "Eve""Eve" condition, requiring matching elements to be equal to “Eve”. In other words, the second and third elements of the second array match their corresponding condition, while the first and second elements of the third array match their corresponding condition. As such, only the second element of the first array is considered, resulting in a return value of 20.
As noted, the SUMIF and SUMIFS functions above calculate sums (adding numbers together). The PRODUCTIF and PRODUCTIFS functions calculate products (multiplying numbers together). AVERAGEIF and AVERAGEIFS calculate averages and COUNTIF and COUNTIFS count how many elements satisfy the given condition or conditions.
MINIFS finds the smallest number among a set of numbers that satisfy one or several conditions. Conversely, MAXIFS finds the largest number among a set of numbers that satisfy one or several conditions.
Comparing array elements to field values
The text strings that need to be given to these functions lend themselves well to comparing elements against constant values. However, what if you need to compare elements against field values (or any other values derived through formulas)?
In that common case, you need to use the text concatenation operator, &. Here’s a formula returning the sum of all values that are greater than the value of Field1:
This formula returns the sum of all field values of the Field2:Field10Field2:Field10 range which are less than or equal to Field1.ValueField1,Value:
The formula above uses a range to construct an array containing many elements. It is easy to lose track of what elements a range contains, though, which can have surprising results. This post explores this topic in depth.
A word on decimal separators
Calcapp allows you to write formulas using either decimal points or decimal commas. You might think that the decimal separator preference you have specified for formulas would apply to the text string given to conditional functions, but that is not the case.
Conditional functions like SUMIF and SUMIFS do support both decimal points and decimal commas. When they need to interpret a period or a comma in the text string you supply, they don’t rely on your decimal separator formula preference, they rely on the language of the app.
That means that if your app is in German, you must use a comma as a decimal separator, even if your preference for formulas calls for using a decimal point:
The same logic applies in reverse if you prefer using decimal commas in formulas, but your app is in US English:
This situation should be rare, but it’s something to be aware of if you get surprising results.
The reason we designed it this way is because when you join text strings like “<=” together with field values using &, Calcapp uses the decimal separator of the language the app has been configured to use when converting the field value to a text string. For that reason, the conditional functions must do the same.
Of course, if you want to sidestep this issue completely, you can use a formula fragment instead of a textual condition. That is the subject for our next section.
Using formula fragments instead of textual conditions
The conditional functions are quite limited in terms of what conditions you can express. With a function like SUMIF, you can only express a single condition. Functions like SUMIFS enable multiple conditions to be expressed, but all conditions need to match — there is no room for including a value which matches one of two conditions (logical or).
We have introduced extensions specific to Calcapp which enable you to use formula fragments to determine if an element should or should not be included. A formula fragment can use any function or operator, meaning that they are infinitely flexible.
In a formula fragment, the element under consideration is available
as the Element value. These
formulas are equivalent, with the second one using a formula
fragment:
Note that the formula fragment is not a text string, but just a regular part of the overall formula. That means that you can easily restrict elements to those that are greater than 1 or are odd (|| means “or”):
In practice, the formula above returns 6 (1 + 2 + 31 + 2 + 3), as 1 is included on account of being an odd number.
Refer to our documentation for these functions for many more examples.
When you use a formula fragment with ranges, you may encounter error messages if your range inadvertently contains, say, text boxes and buttons. This post explores this topic in depth.
Using FILTER instead of the specialized conditional functions
The work SUMIF performs can be divided into two parts: filtering out the unwanted elements from the array and then returning the sum of the elements which made the cut. Just like XLOOKUP invocations can be replaced by using both INDEX and XMATCH, SUMIF and the other conditional functions can be replaced by using the new FILTER function together with specialized functions calculating a result (for SUMIF, that specialized function is SUM — or REDUCE if you are feeling adventurous).
FILTER accepts an array as its first parameter and returns a version of this array which only contains elements that have passed a test you have supplied.
The most commonly used variant of FILTER expresses this test as a logical array given as the second parameter, which should contain the same number of elements as the array to filter. If an element of the second array is TRUE, the corresponding element of the array to filter is part of the returned array. If it is FALSE, the element is dropped.
This formula returns an array of fields whose values are greater than 4:
If we combine FILTER with SUM, we can replicate the SUMIF function. These formulas are equivalent:
Of course, the SUMIF formula is much shorter and is likely easier to read. By combining FILTER with SUM, though, we gain flexibility, and a deeper understanding of how the final result is calculated. Use whichever variant you are most comfortable with.
To explore these functions — and alternatives to using them — be sure to check out our full formula documentation. These functions are available in the Math and Statistical categories.