COUNTIF Function

Generates the count of rows in each group that meet a specific condition. Generated value is of Integer type.

NOTE: When added to a transform, this function is applied to the current sample. If you change your sample or run the job, the computed values for this function are updated. Transforms that change the number of rows in subsequent recipe steps do not affect the values computed for this step.

To perform a simple count of rows without conditionals, use the COUNT function. See COUNT Function.

Basic Usage

aggregate value: COUNTIF(failed_deliveries >= 10) group:postal_code

Output: Generates a two-column table containing the unique values for postal_code and the count of records for that postal_code value in which the value of the failed_deliveries column is greater than or equal to 10.

Syntax

aggregate value:COUNTIF(test_expression) [group:group_col_ref]

ArgumentRequired?Data TypeDescription
test_expressionYstringExpression that is evaluated. Must resolve to true or false

For more information on syntax standards, see Language Documentation Syntax Notes.

For more information on the group parameter, see Aggregate Transform.

test_expression

This parameter contains the expression to evaluate. This expression must resolve to a Boolean (true or false) value.

Usage Notes:

Required?Data TypeExample Value
YesString expression that evaluates to true or false(LastName == 'Mouse' && FirstName == 'Mickey')

Examples

Example - COUNTIF Functions

This section provides simple examples for how to use the COUNTIF and COUNTIFA functions. These functions include the following:

  • COUNTIF - Count the number of values within a group that meet a specific condition. See COUNTIF Function.
  • COUNTAIF - Count the number of non-null values within a group that meet a specific condition. See COUNTAIF Function.

Source:

The following data identifies sales figures by salespeople for a week:

EmployeeIdDateSales
S0011/23/1725
S0021/23/1740
S0031/23/1748
S0011/24/1781
S0021/24/1711
S0031/24/1725
S0011/25/179
S0021/25/1740
S0031/25/17
S0011/26/1777
S0021/26/1783
S0031/26/17
S0011/27/1717
S0021/27/1771
S0031/27/1729
S0011/28/17
S0021/28/17
S0031/28/1714
S0011/29/172
S0021/29/177
S0031/29/1799

Transform:

You are interested in the count of dates during the week when each salesperson sold less than 50 units, not factoring the weekend. First, you try the following:

aggregate value:COUNTIF(Sales < 50) group:EmployeeId

You notice, however, that the blank values, when employees were sick or had vacation, are being counted. Additionally, this step does not filter out the weekend. You must identify the weekend days using the WEEKDAY function:

derive value:WEEKDAY(Date) as:'DayOfWeek'

If DayOfWeek > 5, then it is a weekend date. For further precision, you can use the COUNTAIF function to remove the nulls:

aggregate value:COUNTAIF(Sales, DayOfWeek<6) group:EmployeeId

The above counts the non-null values in Sales when the day of the week is not a weekend day, as grouped by individual employee.


Results:

EmployeeIdcountaif_Sales
S0015
S0024
S0034

Was this page helpful? Let us know how we did:

Send feedback about...

Google Cloud Dataprep Documentation