countif() (aggregation function)

Article
08/18/2022
2 minutes to read

Counts the rows for which Predicate evaluates to true.

Note

This function is used in conjunction with the summarize operator.

Syntax

countif (Predicate)

Arguments

Name	Type	Required	Description
Predicate	string	✓	Expression used for aggregation calculation. Predicate can be any scalar expression with a return type of bool (evaluating to true/false).

Returns

Returns a count of rows for which Predicate evaluates to true.

Example of counting storms by state

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

[Click to run query]

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

Results

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

Example of counting based on string length

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

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