I. Intro
Statistical functions filter data according to specified conditions to perform subsequent calculations. For example, you can use statistical functions to filter stores that meet performance standards or calculate the total sales of goods in a given month.
Got a question?
Select the content to comment.
We improve our articles based on your feedback.
Got It
Types of statistical functions:
- Lookup fields: Based on the LOOKUP function's field type. For details, see Use lookup fields in Base.
- FILTER function: Supports filtering data based on specified conditions, often used in conjunction with other functions. For details, see Use the FILTER function for Base.
- SUMIF function: Used to sum the values that meet the conditions in a specified range. For details, see Use the SUMIF function for Base.
- COUNTIF function: Used to count the number of times a value that meets the conditions appears in the range. For more details, see Use the COUNTIF function for Base.
Note: SUMIFS and COUNTIFS functions are not supported in Base. To achieve the same effect, you can nest the SUM or COUNT function with the FILTER function.
II. Key concepts
- Formula rules: There are four different formula rules in Base, namely "referencing fields in the current table", "referencing fields in other tables", 'referencing [field]', and 'referencing [table].[field]'. For more details, see Overview of formula fields in Base.
- Data reference: In Base, every row is a record and every column is a field. While Excel supports formula references to specific cells or cell ranges, you need to fields to reference individual data or data sets in Base. For more details, see Reference data in Base formulas.
- CurrentValue: This is an argument specially developed for statistical functions in Base. When used in FILTER, SUMIF, and COUNTIF functions, CurrentValue references the individual values in the table.
- For example, if a table is set as the reference range, then CurrentValue represents each row in that table, and the returned values are a range of data. For instance, in [table].[Expert Level].FILTER(CurrentValue="4"), all records with Expert Level of 4 will be returned to each row.
- If a specific field in a table is set as the reference range, then CurrentValue represents each cell in that column, and the returned value is a single value. For instance, in [Expert Level].FILTER(CurrentValue="4"), the corresponding value will be returned for only rows with Expert Level of 4.
III. Examples
Apply CurrentValue to a table
When using FILTER, SUMIF, or COUNTIF functions and a table is set as the reference range, CurrentValue references each row of data in the table and returns all data that meets the conditions.
Scenario: In Table 2, use CurrentValue to get all values that meet a specific condition
Formula: [Table 2].FILTER(CurrentValue.[Data]>80).[Channel]
Description: Since the reference range is the entire table, the return values are all values in the table that meet the condition.
Note: In the formulas, you can enter CurrentValue or @CurrentValue to use CurrentValue as an argument.
250px|700px|reset
250px|700px|reset
Apply CurrentValue to a field
When using FILTER, SUMIF, or COUNTIF functions and a field is set as the reference range, CurrentValue references each row of data in the current field and returns all data that meets the conditions.
Note: The value returned from the calculation will be consistent with the original field type.
Scenario: In the multiple options field, CurrentValue is used to take all values that meet a certain condition
Formula: [Options].FILTER(CurrentValue="A")
Description: Since the referenced range is a field, the return values are all values in the field that meet the condition.
250px|700px|reset
250px|700px|reset
Sum up sales that meet specified conditions
Scenario: Calculate the total sales of all members in the first season.
Formula: [Sales records].FILTER(CurrentValue.[Name]=[Sales person]&& CurrentValue.[Season]="S1").[Sales sum].SUM()
250px|700px|reset
250px|700px|reset