Date: Sun, 4 Jun 2023 07:14:15 +0000 (UTC) Message-ID: <1174484245.12825.1685862855878@3978a672e405> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_12824_1267346328.1685862855878" ------=_Part_12824_1267346328.1685862855878 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - COUNTIF Functions

# EXAMPLE - COUNTIF Functions

This section provides simple examples for how to use the ```C= OUNTIF``` and `COUNTIFA` functions. These function= s include the following:

• `COUNTIF` - Count the number of values within a group t= hat 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 sales= person sold less than 50 units, not factoring the weekend. First, you try t= he following:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name `Pivot columns` `EmployeeId` `COUNTIF(Sales < 50)` `1`
=20

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

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` `WEEKDAY(Date)` `'DayOfWeek'`
=20

If `DayOfWeek > 5`, then it is a weekend date. F= or further precision, you can use the `COUNTAIF` function t= o remove the nulls:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name `Pivot columns` `EmployeeId` `COUNTAIF(Sales, DayOfWeek<6)`=20 `1`
=20

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

------=_Part_12824_1267346328.1685862855878--