countif() (aggregation function)

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

Counts the rows in which predicate evaluates to true.

Null values are ignored and don't factor into the calculation.

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 4 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

count() function, which counts rows without predicate expression.