Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 93 ยท Logical and Conditional Formulas

SUMIF, COUNTIF, AVERAGEIF and Their IFS Versions

Total, count and average only the rows that match: Excel criteria syntax, wildcards, operators, and the multi-condition SUMIFS, COUNTIFS and MAXIFS.

SUM, COUNT and AVERAGE treat every cell the same. Real questions are more selective: "What did the East region sell?", "How many orders are over 100?", "What is the average score of the Year 2 students?". The conditional aggregate functions answer them in one cell, with no filtering and no helper columns: SUMIF, COUNTIF and AVERAGEIF for one condition, and SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS and MINIFS for several.

You should know the plain statistics functions from SUM, AVERAGE, COUNT, MIN and MAX. The formulas below are real Excel; the live examples run on a JavaScript stand-in engine with a simplified criteria matcher (text equality, wildcards and the numeric operators), and a pandas version at the end.

Match first, then total

Range The cells to test.
Criteria The rule a cell must meet.
Sum range The cells to add up.

=SUMIF(range, criteria, [sum_range])

1 / 2

The single-condition trio

TEXT
=SUMIF(range, criteria, [sum_range])
=COUNTIF(range, criteria)
=AVERAGEIF(range, criteria, [average_range])

Excel checks each cell of range against criteria. For SUMIF and AVERAGEIF the matching rows are then taken from sum_range (or average_range); if you leave it out, the cells in range themselves are used. COUNTIF just counts the matches.

TEXT
=SUMIF(A2:A9, "East", E2:E9)       total amount for the East region
=COUNTIF(D2:D9, ">=10")            how many rows have units of 10 or more
=AVERAGEIF(A2:A9, "West", E2:E9)   average amount in the West region

Writing criteria

The criteria argument is where most of the power, and most of the mistakes, live. It can be a number, a cell reference, or text, and text can carry an operator in front.

Criteria Matches
"East" or A1 cells equal to East (case does not matter)
10 cells equal to the number 10
">=10" numbers greater than or equal to 10
"<>East" cells not equal to East (blank cells included)
"Pen*" text starting with Pen (* means any run of characters)
"?ook" four-letter text ending in ook (? means exactly one character)
"~*" a literal asterisk (~ escapes * and ?)
"" or "=" blank cells
"<>" cells that are not blank

Operators are written inside the quotes, so a comparison with another cell needs concatenation: ">"&D2 means "greater than whatever D2 holds". Writing ">D2" would compare against the literal text D2. Wildcards work only with text, not with numbers.

JS SUMIF, COUNTIF and AVERAGEIF with operators and wildcards

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Look at the three Pen-related lines: "Pen" matches only exact Pen (3 rows), while "Pen*" also catches Pen Set (4 rows). "ana" matches Ana, because criteria ignore case. AVERAGEIF with no matching rows is #DIV/0!, since there is nothing to average; wrap it in IFERROR when that is a normal situation.

Several conditions: the IFS family

When a row must meet all of several conditions, switch to the plural functions:

TEXT
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
=MAXIFS(max_range, criteria_range1, criteria1, ...)
=MINIFS(min_range, criteria_range1, criteria1, ...)

Two rules to remember. First, the result range comes first in SUMIFS, AVERAGEIFS, MAXIFS and MINIFS, the opposite of SUMIF, where it is last. Second, all the ranges must have the same size and shape, otherwise the result is #VALUE!. Conditions are combined with AND. To get OR, add two results (SUMIFS(...,"East")+SUMIFS(...,"West")) or use SUM(SUMIFS(..., {"East","West"})). MAXIFS and MINIFS need Excel 2019 or Microsoft 365, and return 0 when no row matches.

A useful pattern is a numeric band: COUNTIFS(D2:D9, ">=3", D2:D9, "<=10") applies two conditions to the same column.

JS SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS and MINIFS

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The last two lines show the edge cases: ranges of different sizes (A2:A8 against E2:E9) give #VALUE!, and MAXIFS with no match returns 0, not an error, so a zero might mean "nothing found".

The same questions in pandas

These functions are a spreadsheet version of what analysts do with groupby and boolean filters in pandas. The stand-in below runs in real Python (pandas) and answers the same questions:

Python The same totals with pandas groupby and masks

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The advantage of groupby is that it produces every region in one step, whereas a SUMIF cell answers one question per formula. In Excel the all-at-once version is a PivotTable or a dynamic-array report.

Practical tips

  • Put the criteria in cells. =SUMIF(A2:A9, H1, E2:E9) lets you change the region by typing in H1, and the same formula can be copied down a list of regions. Anchor the ranges with $ (see cell references).
  • Use whole table columns such as Sales[Amount] in an Excel Table so ranges grow with the data.
  • Watch for numbers stored as text. A number-looking text cell does not meet ">=10"; clean the data first.
  • Criteria strings are limited to 255 characters; longer text gives #VALUE!.
  • Check for real data surprises: trailing spaces make "East " differ from "East". See Cleaning Messy Data.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which formula adds the amounts in E2:E9 for rows where column A is "East" and column C is "Book"?

    SUMIFS takes the sum range first, followed by pairs of criteria range and criteria. SUMIF takes only one condition.
  2. 2. How do you count cells in D2:D9 that are greater than the value in cell G1?

    The operator is part of the criteria text, so you join it to the cell value with &. ">G1" would compare against the literal text G1.
  3. 3. What does COUNTIF(C2:C9, "Pen*") count?

    • is a wildcard that stands for any run of characters, including none, so it matches Pen, Pen Set and Pencil.
  4. 4. What does AVERAGEIF return when no cell meets the criteria?

    There is nothing to average, so AVERAGEIF returns #DIV/0!. MAXIFS and MINIFS return 0 in the same situation.

Summary

  • SUMIF, COUNTIF and AVERAGEIF apply one condition; the โ€ฆIFS versions apply several and combine them with AND.
  • In the plural functions the result range comes first; all ranges must have the same size.
  • Criteria are case-insensitive; operators (>=, <>) live inside the quotes, joined to cells with &; *, ? and ~ are wildcards for text.
  • AVERAGEIF with no match is #DIV/0!; MAXIFS/MINIFS return 0.
  • Keep criteria in cells and ranges anchored, and watch out for text that only looks like numbers.

Next: SUMPRODUCT and Array-Style Calculations.