SUMIFS — Conditional Summing

Version

Application Scenario

The SUM function supports summing arrays or data ranges, but sometimes conditional summing is needed, which means summing data that meets certain conditions.

For example, you want to sum sales volumes greater than 2000, as shown in the following figure.

For example, you want to sum payable amounts greater than 2500 in the current year, as shown in the following figure.

Function Description

The SUMIFS function is added in version 11.0.4 to implement conditional summing.

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	SUMIFS(number,boolean)	Summing data that meets certain conditions
Parameter 1	number	Data range for summing
Parameter 2	boolean	Judgment 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 SUM function. Note: Currently, only the following judgment 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 SUMIFS(B2,B2 > 2000) in cell C2, and set Left Parent Cell to None for cell C2. In this way, the sales volumes greater than 2000 will be summed. 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 SUMIFS(C2,C2>2500) in cell C3, and set Left Parent Cell to Default (that is, cell A2) for cell C3. In this way, the payable amounts greater than 2500 in the current year will be summed. 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 SUMIFS(B2,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, and enter 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 SUMIFS(C2,C2>2500) in cell C3, and set Left Parent Cell to Default (that is, cell A2) for cell C3. In this way, the payable amounts greater than 2500 in the current year will be summed, 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 SUMIFS Example One.cpt.

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