Conditional Summation and Count

In Excel:

Conditional counting (COUNTIF/COUNTIFS): used to count the number of data records that meet specified condition(s).

Conditional summation (SUMIF/SUMIFS): used to calculate the summation of values that meet specified condition(s).

In FineBI, you can also perform conditional summation and counting using function combinations.

Note:

The solutions provided in this document do not support cross-table calculations and are limited to operations within a single data table.

In Component

Note:

If you only want to count occurrences without deduplication, you can use the COUNT_AGG function for simple counting.

For example, you can calculate the number of users who log in on the current day (duplicating data of telephone numbers for the users).

Example data: User Retention Analysis.xlsx

Procedure

Create an analysis subject, upload the example data User Retention Analysis, click Save and Update, and then click Component in the lower left corner to enter the component editing page.

Click the icon, select Calculation Field, name the field Number of Logged-in Users on the Activation Day, enter the formula COUNTD_AGG(IF(DATEDIF(The Earliest Activation Date,Login Date,"D")=0,Telephone Number,null)), and click OK, as shown in the following figure.

The following table describes the formula in detail.

Effect Display

Select a group table, drag the The Earliest Activation Date field to the Dimension bar, click the icon, and set its group to Year-Month. Drag the Number of Logged-in Users on the Activation Day field to the Indicator bar. In this way, the count of users who logged in on their activation date will be displayed, as shown in the following figure.

Note:

To count only non-empty values, you can use a formula similar to COUNTD_AGG(IF(Field1!= "", Field2, null).

In Data

You can use the Group Summary function to achieve deduplication counting and regular counting, as shown in the following figure.

For example, you can calculate the total refund of Company 1 in Beijing.

Example data: Regional Data Analysis.xlsx

Procedure

Click Component to enter the component editing page, click , set Field Name to Refund of Company A in Beijing, enter the formula SUM_AGG(IF(Province="Beijing"&&Customer="Company 8",Refund,0)), and click OK, as shown in the following figure.

The following table describes the formula in detail.SUM_AGG(IF(Province="Beijing"&&Customer="Company   8",Refund,0))

Effect Display

After you save the added field Refund of Company A in Beijing, drag the field into the Indicator bar. In this way, the refund in the specific conditions are displayed, as shown in the following figure.