SUMIFS, COUNTIFS and AVERAGEIFS

Most business questions are of the form “total of something, for rows that match some conditions”. The ...IFS family answers them.

SUMIFS

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

The range to add comes first, followed by pairs of range and condition. A row is included only if it meets every condition.

Revenue in the North region:

=SUMIFS(Sales[Revenue],Sales[Region],"North")

Revenue in the North region for the Computers category:

=SUMIFS(Sales[Revenue],Sales[Region],"North",Sales[Category],"Computers")

Conditions with operators

Put an operator and value inside quotation marks:

=SUMIFS(Sales[Revenue],Sales[Units],">=5")

To compare against a cell, join the operator to the cell reference with &:

=SUMIFS(Sales[Revenue],Sales[Date],">="&L1,Sales[Date],"<="&L2)

With a start date in L1 and an end date in L2, this totals revenue between the two dates.

Wildcards

* matches any number of characters and ? matches exactly one:

=SUMIFS(Sales[Revenue],Sales[Product],"*board*")

COUNTIFS and AVERAGEIFS

COUNTIFS has no sum range. It only counts rows:

=COUNTIFS(Sales[Region],"South",Sales[Units],">5")

AVERAGEIFS follows the same pattern as SUMIFS:

=AVERAGEIFS(Sales[Revenue],Sales[Rep],"Ben")

MAXIFS and MINIFS, available in Excel 2019 and later, work the same way.

Build a summary table

List the regions in a column, for example N2:N4, and put this beside the first one:

=SUMIFS(Sales[Revenue],Sales[Region],N2)

Copy it down. You now have revenue by region that updates whenever the table changes. This is the idea behind every report: a list of categories and a formula that summarises each.

SUMIF or SUMIFS?

SUMIF is the older, single-condition version, and its arguments are in a different order. SUMIFS handles one condition or many, so it is simpler to learn and use only SUMIFS.

Practice

Count the orders placed in February 2025 that had revenue above 300.