Edit

Share via

countif() (aggregation function)

Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

Counts the rows in which predicate evaluates to true.

Note

This function is used in conjunction with the summarize operator.

Syntax

countif (predicate)

Learn more about syntax conventions.

Parameters

Name Type Required Description
predicate string ✔️ The expression used for aggregation calculation. The value can be any scalar expression with a return type of bool.

Returns

Returns a count of rows in which predicate evaluates to true.

Examples

Count storms by state

This example shows the number of storms with damage to crops by state.

Run the query

StormEvents
| summarize TotalCount=count(),TotalWithDamage=countif(DamageCrops >0) by State

The results table shown includes only the first 10 rows.

State TotalCount TotalWithDamage
TEXAS 4701 72
KANSAS 3166 70
IOWA 2337 359
ILLINOIS 2022 35
MISSOURI 2016 78
GEORGIA 1983 17
MINNESOTA 1881 37
WISCONSIN 1850 75
NEBRASKA 1766 201
NEW YORK 1750 1
... ... ...

Count based on string length

This example shows the number of names with more than four letters.

Run the query

let T = datatable(name:string, day_of_birth:long)
[
   "John", 9,
   "Paul", 18,
   "George", 25,
   "Ringo", 7
];
T
| summarize countif(strlen(name) > 4)

Output

countif_
2

Related content