I. Intro
The SUMIF function returns the sum of values that meet specified conditions in the specified range. It is commonly used for statistics in fields such as human resources management, sales, finance, and more. For example, this function can be used to calculate the total sales of specific products or the salary of certain types of employees.
Arguments:
SUMIF(List, Expression)
- List: The target field or table for summation.
- Expression: The conditions for evaluating the data within the selected range.
II. Steps
- Open the base and select a table. Click the + icon on the right side of the table to add a new field, enter the title, and change the field type to Formula.
250px|700px|reset
- In the formula editor, enter SUMIF, select the table or field that contains the values you want to sum, and specify the conditions you need.
- For example, if you want to find a product that generated revenue above $4500, use the following formula: SUMIF([Sales revenue],CurrentValue>4500). As shown in the image below, revenue for product B and product E are $5000 and $8000 respectively, so their revenue is returned.
250px|700px|reset
250px|700px|reset
- If you want to find the total revenue for products that generated revenue above $4500, use the following formula: SUMIF([table].[sales],CurrentValue>4500). As shown in the image below, revenue for product B and product E are $5000 and $8000 respectively, so the sum of their revenue is returned.
Note: Using the [table].[field] in the formula will retrieve all records in the table that meet the specified conditions, which will serve as the basis for the calculation. For more details, see Reference data in Base formulas.
250px|700px|reset
250px|700px|reset
III. Use cases
You can use a combination of the FILTER function and the SUM function to calculate values that meet specific conditions. We will use some tables below to demonstrate some common scenarios.
For example, in the sales records table for your team, there are fields for product name, sales rep, sales volume, sum, time of sale, and more. To conduct data analysis for the team, you can do the following depending on your needs:
250px|700px|reset
Calculate the sum based on one condition
Scenario: Calculate the total sales amount for each member of the team.
Formula: [Sales records].FILTER(CurrentValue.[Person]=[Name]).[Sales sum].SUM()
250px|700px|reset
Calculate the sum based on multiple conditions
Scenario: Calculate the sales sum for 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
Calculate the sum of non-blank values
Scenario: Calculates the total for records in which the sales person field is not empty.
Formula: [Sales records].FILTER(CurrentValue.[Sales person]!="").[Sales sum].SUM()
Calculate the total for a specific period
Scenario: Calculate the total sales for November 2021.
Formula: [Sales records].FILTER(CurrentValue.[Sales time]>=TODATE("2021/11/1")&& CurrentValue.[Sales time]<=TODATE("2021/11/30")).[Sales sum].SUM()