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 |
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 |
|
|---|---|
Parameter: Row labels | EmployeeId |
Parameter: Values | COUNTIF(Sales < 50) |
Parameter: Max columns to create | 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 |
|
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | WEEKDAY(Date) |
Parameter: New column name | '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 |
|
|---|---|
Parameter: Row labels | EmployeeId |
Parameter: Values | COUNTAIF(Sales, DayOfWeek<6) |
Parameter: Max columns to create | 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 |