COUNT Function

Function Role

The function counts the number of items contained in an array or data area, such as Number of Regions and Number of Salespersons, as shown in the following figure.

The function can also be nested with other functions to perform different calculations. For example, you can use this function to perform a conditional counting to count the number of salespersons other than Jack, as shown in the following figure.

In report design, you can use the COUNT function to count the number of cells, the number of cells extended from a cell, and the number of elements in an array.

Note: 

The distinct count can be achieved by combining the COUNT function with the UNIQUEARRAY() function, such as count(UNIQUEARRAY(A1)).

Function Explanation

Example: 

If five cells with data are expanded from cell A1, the execution result of count(A1) is 5. 

If data exists in cells from A1 to A8, the execution result of count(A1:A8) is 8. 

The execution result of count(2,3,4,5,7) is 5. 

Notes

Counting objects can be arrays or cells, and cells can be cell areas or extended cells.

The count function counts null values caused by empty strings, but it does not count null values caused by NULL values.

Resolvable text values, logical values, zero values, and blank cells in arrays or reference parameters will be involved in the calculation. 

Unresolvable text values in arrays or reference parameters will not be involved in the calculation.

Template Preparation

Create a template, enter the SQL statement SELECT * FROM Sales_Volume to create a dataset named ds1, set the following template style, and drag the Region and Salesperson fields in ds1 into cell A2 and cell B2, respectively. Enter the formulas =count(A2) and =count(B2) in cell C2 and cell D2, respectively, as shown in the following figure.

Data Column Display Setting

The COUNT function can only count the number of cells. Since the duplicate data exists in the data columns of Region and Salesperson, if you want to count the number of regions and salespersons, you need to set the values of Data Setting to Group for cell A2 and cell B2, equivalent to the operation of distinct count, as shown in the following figure.

Note: 

If you select List as the values of Data Setting while pursuing the result of the distinct count, you can use the formula count(UNIQUEARRAY()).

Cell Expansion Setting

Set Left Parent Cell to None for cell C2. Otherwise, cell C2 and cell D2 expand along with cell B2, resulting in incorrect counting. The following figure shows the setting method.

Effect Display

Save the template, and the effect is the same as that shown in section "Function Role."

When the parameter value in the COUNT function is an expanded cell, the function can be nested with other functions to achieve a conditional count. The format is count({}), and the content in {} is the counting condition.

For example, count(A1{A1!=0}) counts the number of cells that are expanded from cell A1 and contain data other than zero.  

count(A1{len(A1) != 0}) counts the number of cells that are expanded from cell A1 and contains data.

Enter the formula =count(B2{B2!='Jack'}) in cell E2 in the template to achieve the effect shown in section "Distinct Count", as shown in the following figure.

Save the template, and the effect is the same as that shown in section "Function Role."

If you need to make multi-conditional judgments, you can nest formulas. For example, if you want to count the number of salespersons (other than Jack) whose sales region is New York, you can use the formula: count(B2{B2!='Jack'&&A2='New York'}) or count(B2{AND(B2!='Jack',A2='New York')}), as shown in the following figure.

The following table describes the formula.

Note: 

1. You can use the OR function to count the number of items that meet any condition in the formula.

2. You can to use the COUNTIFS function in the 11.0.4 version.

For details, you can download the template COUNT Function.cpt