COUNTIFS Function

Version

Application Scenario

The COUNT function can count the number of items contained in an array or data area, but sometimes you need to perform conditional counting, such as counting the number of salespersons with sales greater than 2000, as shown in the following figure.

Function Description

You can use the COUNTIFS function to perform conditional counting in the 11.0.4 version.

Note: 

The hierarchical coordinate function only supports cell calculations. The cross-sheet calculations, watermarks, JavaScript references, and default parameter values are not supported.

Function Explanation

1. Overview

Syntax	COUNTIFS(number,boolean)	To count the number of cells that meet specified conditions.
Parameter 1	number	The data range of counting.
Parameter 2	boolean	The judgement condition whose result is true or false. You can use && and || to make multi-conditional judgments. When the parameter is null, the execution effect is the same as that of the COUNT function. Note:  Currently, only the following judgement conditions are supported. 1. Comparison of numeric types, such as > 10. 2. Comparison of string equality, such as a == "a". 3. Comparison of null values, such as == null.

The values of cell A2, cell B2, and cell C2 are the year, the month, and the amount due.

 The formula COUNTIFS(B2,C2>2500) with cell A2 as the parent cell counts the number of the months whose amount due is greater than 2500 in different years.

The formula COUNTIFS(B2,C2>2500) with no parent cell counts the number of the months whose amount due is greater than 2500 in all the years.

3. Note

If a parent-child relationship exists between the cell where you enter the formula and the cell where the calculation object is located, a calculation problem will occur.

Data Preparation

Create a dataset named ds1, and enter the SQL query statement SELECT * FROM Sales_Volume in the database query dialog box to extract all data from the table Sales_Volume, as shown in the following figure.

Report Design

Design the report as shown in the following figure.

Set the Data Setting value to Summary for cell B2, as shown in the following figure.

Select None as Left Parent Cell for cell C2, enter the formula COUNTIFS(A2,B2 > 2000) in cell C2, and set the name of cell C1, as shown in the following figure.

Effect Display

PC

Save the report, and click Pagination Preview. The effect is the same as that shown in section "Application Scenario." 

Mobile Terminal

The preview effects on the DataAnalyst app and the HTML5 terminal are the same, as shown in the following figure.

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