sumif() (aggregation function)

Article
08/12/2024

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

Calculates the sum of expr in records for 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.

You can also use the sum() function, which sums rows without predicate expression.

Syntax

sumif(expr,predicate)

Learn more about syntax conventions.

Parameters

Name	Type	Required	Description
expr	`string`	✔️	The expression used for the aggregation calculation.
predicate	`string`	✔️	The expression used to filter rows. If the predicate evaluates to `true`, the row will be included in the result.

Returns

Returns the sum of expr for which predicate evaluates to true.

Example showing the sum of damages based on no casualty count

This example shows the sum total damage for storms without casualties.

Run the query

StormEvents
| summarize DamageNoCasualties=sumif((DamageCrops+DamageProperty),(DeathsDirect+DeathsIndirect)==0) by State

Output

The results table shown includes only the first 10 rows.

State	DamageNoCasualties
TEXAS	242638700
KANSAS	407360000
IOWA	135353700
ILLINOIS	120394500
MISSOURI	1096077450
GEORGIA	1077448750
MINNESOTA	230407300
WISCONSIN	241550000
NEBRASKA	70356050
NEW YORK	58054000
...	...

Example showing the sum of birth dates

This example shows the sum of the birth dates for all names that have more than 4 letters.

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