COUNTIFS — Conditional Counting

Version

Application Scenario

The COUNT function can count the items in an array or a data range. But sometimes, conditional counting is required to count the data records that meet certain conditions.

For example, you want to count the salespersons with a sales volume greater than 2000, as shown in the following figure.

For example, you want to count the months in the current year with a payable amount greater than 2500, as shown in the following figure.

Function Description

The COUNTIFS function is added in version 11.0.4 to implement conditional counting.

Note:

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

Function Explanation

(1) Overview

Syntax	COUNTIFS(number,boolean)	Counting the cells that meet specified conditions
Parameter 1	number	Data range for counting
Parameter 2	boolean	Judgement condition, whose result is true or false. You can use && and || to separate multiple conditions. When this parameter is null, the execution effect of this function is the same as that of the COUNT function. Note: Currently, only the following judgement conditions are supported. (1) Comparison of numbers, for example, >10 (2) Comparison of strings, for example, a == "a" (3) Comparison of null values, for example, ==null

(2) Example

Example one: Cell A2 contains each salesperson, and cell B2 contains the total sales volume of each salesperson. Enter the formula COUNTIFS(A2,B2 > 2000) in cell C2, and set Left Parent Cell to None for cell C2. In this way, the salespersons with sales volume greater than 2000 will be counted. For details, see section "Example One."

Example two: Cell A2 contains each year, cell B2 contains each month, and cell C2 contains the total payable amount of each month. Enter the formula COUNTIFS(B2,C2>2500) in cell C3, and set Left Parent Cell to Default (that is, cell A2) for cell C3. In this way, the months in the current year with a payable amount greater than 2500 will be counted. For details, see section "Example Two."

Note:

Set Left Parent Cell for the cell where the formula is located according to the actual situation. Left Parent Cell needs to be set to None for the cell where the formula is located in example one, and be set to Default in example two.

Template Creation

Create a general report, as shown in the following figure.

Data Preparation

Create a database query ds1 with the SQL query statement SELECT * FROM Sales_Volume to extract all data from the Sales_Volume table, as shown in the following figure.

Report Design

(1) Enter text in cells A1 and B1 and drag the corresponding data columns to cells A2 and B2, as shown in the following figure.

(2) Select Summary and Sum in Data Setting for cell B2, as shown in the following figure.

(3) Enter text in cell C1, insert the formula COUNTIFS(A2,B2 > 2000) in cell C2, and set Left Parent Cell to None for cell C2, as shown in the following figure.

Effect Display

1. PC

Save the report and click Pagination Preview. The following figure shows the preview effect.

2. Mobile terminal

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

Template Creation

Create a general report, as shown in the following figure.

Data Preparation

Create a database query ds1 with the following SQL statement:

SELECT STRFTIME('%Y',SIGNDATE) AS Year,STRFTIME('%m',SIGNDATE) AS Month,PAIDFROM ORDERS_oldWHERE STRFTIME('%Y',SIGNDATE) IN ('2008','2009')

This indicates filtering the year 2008 or 2009 (extracting the year part from the SIGNDATE field, and naming it Year), month (extracting the month part from the SIGNDATE field, and naming it Month), and payable amount (the PAID field) from the ORDERS_old table, as shown in the following figure.

Report Design

(1) Enter text in cells A1 to C1, merge cells A2 and A3, and drag the corresponding data columns to cells A2 to C2, as shown in the following figure.

(2) Select Summary and Sum in Data Setting for cell C2, as shown in the following figure.

(3) Enter text in cell B3, insert the hierarchical coordinate formula COUNTIFS(B2,C2>2500) in cell C3, and set Left Parent Cell to Default (that is, cell A2) for cell C3. In this way, the months in the current year with a payable amount greater than 2500 will be counted, as shown in the following figure.

Effect Display

(1) PC

Save the report and click Pagination Preview. The following figure shows the preview effect.

(2) Mobile terminal

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

Download the completed template of example one by clicking COUNTIFS Example One.cpt.

Download the completed template of example two by clicking COUNTIFS Example Two.cpt.