Conditional Sum and Count

1. Overview

In Excel:

"Condition count (COUNTIF/COUNTIFS)": count the number of data pieces that meet a certain condition.

Conditional summation (SUMIF/SUMIFS): used to sum the values of specified conditions.

In BI, you can also use combinatorial functions to realize conditional summation and counting.

Note: cross data table calculation cannot be realized in BI, you can only calculate in one data table.

2. Condition count

Note: if you don't need de duplication statistics and only need to record the times, use COUNT_AGG count is enough.

Example: calculate the number of users logged in on the activation date (de count the contact information of users logged in on the current day)

Sample data:

412.User retention analysis.xlsx

2.1 Operation steps

After uploading data, create a component, select the "User retention analysis" data set and enter the component editing interface.

Create a calculation field and enter the field name and formula: COUNTD_AGG(IF(DATEDIF(${Earliest activation date},${Login time},"D")=0,${TEL},null)), then click "OK", as shown in the following figure:

Formula description:

2.2 View the effect

Drag the "Earliest activation time" into the horizontal axis and set it as "Year Month". Drag the "Number of logged in users on the activation day" field into the vertical axis to see the number of logged in users on the activation day, as shown in the following figure:

3. Conditional summation

Example: calculate the total amount of "profit" with "area" as "Beijing", and "sales channel" as "supermarket".

Sample data:

316.AC-sales.xlsx

3.1 Operation steps

Create a dashboard, select the "AC-sales" dataset, create a "calculation indicator", and enter the field name and formula: SUM_AGG(IF(${area}="Beijing"&&${sales channel}="supermarket",${profit},0)), as shown in the following figure:

Formula description:

3.2 Effect view

After saving the indicator, drag the "Beijing Supermarket Profit" indicator into the "indicator" field to see the total collection amount under the specified conditions, as shown in the figure below: