I. Intro
The COUNTIF function can be used to count the number of records that meet a specific condition. It is commonly used for data analysis, sales management, financial statistics, and more. For example, you can use the COUNTIF function to calculate the sales amount of products that meet specified conditions.
Arguments
COUNTIF(LIST, expression)
- LIST: The target field or table to be counted.
- expression: The condition for assessing the data.
Note: COUNTIF is often used with CurrentValue. For more details, see Overview of statistical functions and CurrentValue in Base.
II. Steps
- Open the base, and click the + icon on the right side to create a new field. Enter a field title, and select Formula as the field type.
250px|700px|reset
- In the formula editor, enter the COUNTIF function, select the table or field, and enter the arguments as needed.
- For example, if you want to count the number of students who scored more than 80 on the exam, the following formula can be used: [Table].COUNTIF(CurrentValue.[score]>80). As shown in the image below, the total number of students with scores over 80 is 3.
250px|700px|reset
250px|700px|reset
III. Use cases
Count the number of items that meet condition(s)
Single condition
Scenario: Count the number of goods in all products with sales greater than 1000.
Formula: [Sales Table].COUNTIF(CurrentValue.[Sales Amount]>1000)
250px|700px|reset
250px|700px|reset
Multiple conditions
Scenario: Count all items with a sales sum greater than 1000 and with inventory of less than 500.
Formula: [Sales Table].COUNTIF(CurrentValue.[Sales Amount]>1000&&CurrentValue.[Inventory<500)
250px|700px|reset
250px|700px|reset
Check whether values match
Scenario: Check whether the inventory quantity matches the number in the system.
Formula: [Inventory].COUNTIF(CurrentValue=Quantity])
250px|700px|reset
250px|700px|reset
Check whether values are duplicated
Scenario: Check whether the product name is duplicated.
Formula: IF(AND[Sales Table].COUNTIF(CurrentValue.[Inventory Name]=[Inventory Name])>1,[Inventory Name].COUNTIF(CurrentValue!="")),"Duplicate",""）
250px|700px|reset
250px|700px|reset
Check whether a field is empty
Scenario: Check whether there are product names left empty.
Formula: [Product name].COUNTIF(CurrentValue!="")
250px|700px|reset
250px|700px|reset
IV. FAQs