COUNTAIF Function

Contents:

Expand all Collapse all

Contents:

Generates the count of non-null values for rows in each group that meet a specific condition.

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 counting of non-nulls without conditionals, use the COUNTA function. See COUNTA Function.

Basic Usage

pivot value: COUNTAIF(entries, entryValidation == 'Ok') group:City limit:1

Output: Generates a two-column table containing the unique values for City and the count of non-null values in the entries column for that City value when the entryValidation value is 'Ok'. The limit parameter defines the maximum number of output columns.

Syntax and Arguments

pivot value:COUNTAIF(col_ref, test_expression) [group:group_col_ref] [limit:limit_count]

Argument	Required?	Data Type	Description
col_ref	Y	string	Reference to the column you wish to evaluate.
test_expression	Y	string	Expression 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 Pivot Transform.

col_ref

Name of the column whose values you wish to use in the calculation. Column must be a numeric (Integer or Decimal) type.

Usage Notes:

Required?	Data Type	Example Value
Yes	String that corresponds to the name of the column	`myValues`

test_expression

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

Usage Notes:

Required?	Data Type	Example Value
Yes	String expression that evaluates to `true` or `false`	`(LastName == 'Mouse' && FirstName == 'Mickey')`

Examples

Tip: For additional examples, see Common Tasks.

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:

EmployeeId	Date	Sales
S001	1/23/17	25
S002	1/23/17	40
S003	1/23/17	48
S001	1/24/17	81
S002	1/24/17	11
S003	1/24/17	25
S001	1/25/17	9
S002	1/25/17	40
S003	1/25/17
S001	1/26/17	77
S002	1/26/17	83
S003	1/26/17
S001	1/27/17	17
S002	1/27/17	71
S003	1/27/17	29
S001	1/28/17
S002	1/28/17
S003	1/28/17	14
S001	1/29/17	2
S002	1/29/17	7
S003	1/29/17	99

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:

pivot value:COUNTIF(Sales < 50) group:EmployeeId limit:1

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 type:single 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:

pivot value:COUNTAIF(Sales, DayOfWeek<6) group:EmployeeId limit:1

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:

EmployeeId	countaif_Sales
S001	5
S002	4
S003	4

See Also for COUNTAIF Function:

Send Feedback

Page tree