PowerApps CountIf Function with Examples

In this PowerApps Tutorial, We will discuss what is PowerApps CountIf function, What is its syntax. Also, By taking some simple scenarios, We will see how a user can use it in the PowerApps.

Also, We will discuss about these below topics that are related to PowerApps Countif() function as:

• PowerApps countif multiple conditions
• PowerApps countif sharepoint list
• PowerApps countif gallery
• PowerApps countif delegation
• PowerApps countif text
• PowerApps countif date
• PowerApps countif distinct
• PowerApps countif blank

PowerApps countif

• PowerApps CountIf function helps to count the total number of items or records in a table that are true (for a logical formula).
• If you use CountIf functions with filters, then there will be a 50K delegation limit.
• If you need a precise count and expect the result to be under the aggregate limit, you can bypass the cached count via CountIf(<Data Source>, True).

countif syntax PowerApps

Below represents the syntax for PowerApps CountRows function:

CountIf( Table, LogicalFormula )

Where,

• Table = It is required. Specify the table that you need to be count all the records within it.
• LogicalFormula = This is also required. Specify the formula to evaluate for each record of the table. Records that return true for this formula are counted. The formula can reference columns of the table.

Also, you may like, Microsoft PowerApps Radio Button Example.

PowerApps countif multiple conditions

Do you want to know how to work the PowerApps CountIf function with multiple conditions, then refer to this below scenario.

• There is a SharePoint list named Products. This list has some different columns with different data types. Among them, It has a Title (By default it is a single line text data type) and a Comments column (Multiline data type).
• Here I would like to count the total number of records based upon the multiple conditions using the PowerApps CountIf function.
• Suppose, in that list, there are two SmartPhone that is having the same Comments as Good. Now I will count it using the CountIf function and that will display in the Label control.

• Add a Label control and apply this below formula on it’s Text property as:

Text = "Total Count of multiple values: " & CountIf (
    Products,
    Comments = "Good" && Title = "SmartPhone"
)

Where,

• “Total Count of multiple values: ” = This is the text that will display in the label control
• Products = SharePoint list name
• Comments, Title = SharePoint list column name
• “Good”, “SmartPhone” = This is the column value that you want to count

Refer to the below screenshot.

• Save and Preview the app. You can see the result in the label control as like the above screenshot.

You may like, PowerApps submit form to SharePoint Online list.

PowerApps countif SharePoint list

y taking some simple scenarios, We will see how to work with the SharePoint list using the PowerApps CountIf function.

Example – 1:

• The below screenshot represents a SharePoint list named MarkSheet. This list has some columns with different data types. Among them, it has one Choice column named “Status“.
• This Status column has two choice values:
  • Pass
  • Fail

• Now I would like to count the total number of Fail students and the result will display in the label control.

• You can see there is a total of three failed students in the PowerApps vertical gallery control. To display in the label control, you can apply this below formula on Label’s Text property as:

Text = "Total Number of Failed Students: " & CountRows(
    Filter(
        MarkSheet,
        Status.Value = "Fail"
    )
)

Where,

1. “Total Number of Failed Students: “ = This is the text that I want to display in the label control
2. MarkSheet = SharePoint List name
3. Status.Value = Status is the SharePoint Choice column name that represents with the “.Value” in the app
4. “Fail” = Specify the value that you count

• Once you will save and preview the app, you can see the total number of failed students in the labe control as shown in the above screenshot.

Example – 2:

• There are two SharePoint lists named as:

1. Project Details
2. Client Project Details

• Both SharePoint lists are having some different columns with different data types. Although both lists are having the same column named Project Status with a choice column data type.
• This column is having the below status as:
  • Submitted
  • Approved
  • Rejected
  • Pending
• The below screenshot represents the Project Details list with the Project Status Column.

• Similarly, the below screenshot represents the Client Project Details list having the Project Status Column.

• Now I want to count the total number of records with a particular status (suppose Approved) on both lists.
• To do so, set the below formula on the screen’s OnVisible property as:

OnVisible = ClearCollect(
    List1Collection,
    'Project Details'
);
ClearCollect(
    List2Collection,
    'Client Project Details'
)

Where,

1. List1Collection = PowerApps Collection name for the first list
2. List2Collection = PowerApps Collection name for the second list
3. ‘Project Details‘, ‘Client Project Details‘ = SharePoint list name

• Next, Insert a Label control and apply this below formula on its Text property as:

Text = CountRows(
    Filter(
        List1Collection,
        'Project Status'.Value = "Approved"
    )
) + CountRows(
    Filter(
        List2Collection,
        'Project Status'.Value = "Approved"
    )
)

Where,

‘Project Status’.Value = This is the choice column from both Sharepoint lists. You need to specify the value as well. Here I need to count only the Approved value, so I have specified it as Approved in the formula.

• Now Save and Publish the app. Again open the app. You can see the total number of approved values (from both the SharePoint lists) in the Label control as in the above screenshot.

You may like, How to use PowerApps Table() Function.

PowerApps countif gallery

Here we will discuss how a PowerApps user will work with the PowerApps CountIf function in the gallery control.

• There is a SharePoint list named Project Details. This list has a Choice column named Employee Job with two choice values:
  • Permanent
  • Temporary

• On the PowerApps screen, I have a Gallery control having with specific SharePoint list Data source (Project Details).
• Here I want to count the total number of items where the Employee Job is Permanent only. The result will display in a label control.

• To do so, Insert a Label control and set this below code to its Text property as:

Text = "Total Count of Permanent Employees: " & CountIf(
    Gallery2.AllItems,
    'Employee Job'.Value = "Permanent"
)

Where,

1. “Total Count of Permanent Employees: ” = This is the text that will display in the label control
2. Gallery2 = Gallery control name
3. ‘Employee Job’.Value = Employee Job is the SharePoint Choice column name that represents the “.Value” in the app (you must need to specify the <.Value>)
4. “Permanent” = Specify the value that you count

• When you will Save and Preview the app, you can see there is a total three number of Permanent employees will display in the label control as in the above screenshot.

You may like, PowerApps Collection – How to create and use.

PowerApps countif delegation

• Suppose in your data source, there is a total of 5000 items or records. Here, I want to display all the items (from the Data source) on the PowerApps screen.
• But there is a limitation in the PowerApps app where it always returns only 500 items (from the data source). This is known as PowerApps Delegation.
• When you will create a PowerApps application, most of the time you can see there will be a warning sign on the formula, if the function is not delegable. The warning message states that: “Delegation warning: The “filter” part of this formula might not work correctly on large data sets.”
• Always remember, 500 is the PowerApps Default limit. We can extend this limitation from 500 to 2000 by using the following instructions:
  • Open your PowerApps app
  • Go to the File tab and then Settings
  • Select on Advanced Settings
  • Set the value to 2000 in the Data row limit for non-delegable queries

• In some cases, the 2000 record limit can satisfy the requirement. But most of the cases, also may not satisfy because maybe the data source has more than 2000 items (as in my data source it has 5000 records).
• So in this case, We will do another thing i.e. PowerApps Collection.
• If you will create a collection, then there will be no delegation limit and one collection can hold at most 10K items or records.
• At first, you need to save the data for collection. Let’s take an example. Set the below formula on the OnStart property of your app:

OnStart = ClearCollect(collectionname,SharePointlistname)

• Next, Use this collection to your specific requirement as like:

CountRows(colletionname) or CountIf(Condition with the collection)

PowerApps countif text

By taking a simple scenario, We will see how to work in PowerApps countif text using the CountIf function.

• I have a SharePoint list named Event Registration Details. This list has some columns with different data types. Also, it has a choice column named Country as shown in the below screenshot.

• On the PowerApps screen, there is a search box (which is a Text input control) that is having the Default property as “Enter Country Name” as below.
• Now I would like to count the total number of records based upon the text box. That means, if a user will find or search a country (like India), then it will count the total number of items that are present in the existing list.

• To do so, Insert a Label control and apply this below formula on its Text property as:

Text = CountIf(
    'Event Registration Details',
    Find(
        TextInput2.Text,
        Country.Value
    ) > 0
)

Where,

1. ‘Event Registration Details’ = SharePoint list name
2. TextInput2 = Text input control name
3. Country = SharePoint choice column name

Refer to the below screenshot.

• Save and Preview the app. Enter the country name that you need to count. Then you can see the total count of items based upon the country search as like the below screenshot.

You may also like, PowerApps: Create a navigation menu using the Gallery Control.

PowerApps countif date

By using these different scenarios, We will see how to work with PowerApps countif date using CountIf function.

Example – 1: (PowerApps countif function with the Date picker control value)

• There is a SharePoint list named Hospital Registration Details. This list has various columns (with different data types) including a Date column named Registration Date.
• Also, this list has some records as like the below screenshot.

• Here what happens is, When a user will select a Date from the date picker control, it will count the total number of records that have been created on the selected date.
• That means, if a user will select date as of “17/04/2021“, then it will count how many records are there that have been created on that 17th only.
• As you can see, there is a PowerApps Date picker control on the PowerApps screen that will help the user to select a specific date. It is having Today’s date on its DefaultDate property as shown below.

• Also, there is a Label control that will display the total count result. Apply this below formula on its Text property as:

Text = "Total Count of Records based upon the DatePicker: " & CountIf(
    'Hospital Registration Details',
    StartsWith(
        Text(
            DatePicker1.SelectedDate,
            "[$-de]dd/MM/yyyy"
        ),
        Text(
            'Registration Date',
            "[$-de]dd/MM/yyyy"
        )
    )
)

Where,

1. “Total Count of Records based upon the DatePicker: “ = This represents the text that will display in the label control
2. ‘Hospital Registration Details’ = SharePoint list name
3. DatePicker1 = Date picker control name
4. ‘Registration Date’ = SharePoint list Date column name

• Save and Preview the app. Select one date (from the date picker control) as per your need. Then you can see the total number of records based upon that user-selected date as like the below screenshot.

Example – 2: (PowerApps countif value is current date)

Here we will see how the PowerApps CountIf function will work with the Current date value.

• I have a SharePoint list named Event Registration Details and there is a Date Time column named Submit Date as shown below.
• Here I would like to count how many records that match today’s date or current date, as it will work for the project planning.

• On the PowerApps screen, I have a Gallery control that displays all the records from that specific SharePoint list data source (Event Registration Details).
• This gallery control is having the Layout as “Title, Subtitle and Body” where the Body represents the SharePoint Date column as Submit Date.
• To count the records that match today’s date, you can apply this below formula on Label’s Text property as:

Text = "Total Count of Records that is having Current Date: " & CountIf(
    Gallery5.AllItems,
    Text(
        'Submit Date',
        "[$-en-US]mm/dd/yyyy"
    ) = Text(
        Today(),
        "[$-en-US]mm/dd/yyyy"
    )
)

Where,

1. Gallery5 = Gallery control name
2. ‘Submit Date’ = SharePoint Date column name

• Save and Publish the app. Once you will reopen the app, you can see the result in the label control as in the above screenshot.

PowerApps countif distinct

Here we will see the PowerApps CountIf function that works with the PowerApps Distinct function.

• There is a SharePoint list named Products. This list has these many below columns as you can see in the below screenshot.
• Here I would like to count the total number of distinct Title value that is present in the SharePoint list.

• On the PowerApps screen, Add a Label control and apply this below formula on it’s Text property as:

Text = "Total Count of Distinct Items: " & CountRows(
    Distinct(
        Products,
        Title
    )
)

Where,

1. Products = SharePoint list name
2. Title = Specify the SharePoint column name that you want to count the distinct value

• Once you will save and preview the app, you can see the total distinct count value in the label control as shown in the above screenshot.

Read about, Power Apps Len Function.

PowerApps countif blank

Do you want to know what is CountIf Blank in PowerApps and how you can use it? First of all, you need to know the meaning of CountIf blank. Let us take a simple example.

• There is a SharePoint List named Products. This list has a column named Comments (with multiline text data type).
• Here I would like to count how many rows have the Comments field blank?
• For this, We can use the PowerApps countif and the IsBlank functions. Follow the below things to do so.

• On the PowerApps screen, there is a gallery control that displays all the product details as shown below. To count the total number of empty rows (of comments column), apply this below formula on Label’s Text property as:

Text = "Total Count of Empty Comments: " & CountIf(
    Products,
    IsBlank(Comments)
)

Where,

1. “Total Count of Empty Comments: “ = This is the text that will display in the label control
2. Products = SharePoint list name
3. Comments = Multiline text column where we need to count the total empty values

• Once you will save and preview the app, you can see the total empty rows in the label control as shown in the above screenshot.

Also, you may like these below PowerApps Tutorials as:

• PowerApps CountRows function with Examples
• PowerApps Count Function with Examples
• PowerApps CheckBox – How to use
• PowerApps First, FirstN, Last, and LastN function with examples
• PowerApps LastSubmit() with Examples
• PowerApps AddColumns Function with Examples
• PowerApps Patch Function with examples
• PowerApps ForAll Function with examples
• PowerApps Replace Function with examples
• PowerApps if statement with examples
• PowerApps Employee Directory
• PowerApps Pen input control (PowerApps signature)

In this PowerApps Tutorial, We discussed what is PowerApps CountIf function, What is its syntax. Also, By taking some simple scenarios, We saw how a user can use it in the PowerApps.

Also, We discussed about these below topics that are related to PowerApps Countif() function as:

• PowerApps countif multiple conditions
• PowerApps countif sharepoint list
• PowerApps countif gallery
• PowerApps countif delegation
• PowerApps countif text
• PowerApps countif date
• PowerApps countif distinct
• PowerApps countif blank