# EXAMPLE - COUNTIF Functions

This example demonstrates how to count the number of values within a group, based on a specified conditional test.

Functions:

Item

Description

COUNTIF Function

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

COUNTAIF Function

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

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

Transformation:

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:

 Transformation Name Pivot columns EmployeeId COUNTIF(Sales < 50) 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:

 Transformation Name New formula Single row formula WEEKDAY(Date) 'DayOfWeek'

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

 Transformation Name Pivot columns EmployeeId COUNTAIF(Sales, DayOfWeek<6) 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