Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 29 ยท Working with Data

SUMIF, COUNTIF, AVERAGEIF and Friends

Add, count and average only the rows that match: SUMIF, COUNTIF, AVERAGEIF, SUMIFS and COUNTIFS in Google Sheets, with criteria, wildcards and dates.

Totals of everything are rarely what you need. You want the total for one region, the number of orders over $100, the average score of students whose name starts with A. The IF family of functions does exactly that: they apply SUM, COUNT or AVERAGE to only the cells that meet a condition.

In this lesson you will learn the single-condition functions (SUMIF, COUNTIF, AVERAGEIF), the multi-condition versions with an S on the end (SUMIFS, COUNTIFS, AVERAGEIFS), how to write criteria with operators, wildcards and dates, and the argument-order trap that catches almost everyone. Basic aggregates were covered in SUM, AVERAGE, MIN, MAX and COUNT.

Pick rows, then summarize

SUMIF Add matching rows.
COUNTIF Count matching rows.
AVERAGEIF Average matching rows.
1 / 2

The three single-condition functions

Imagine a table with Region in A2:A9, Product in B2:B9 and Sales in C2:C9.

TEXT
=COUNTIF(A2:A9, "East")             how many rows are East
=SUMIF(A2:A9, "East", C2:C9)        total sales for East
=AVERAGEIF(A2:A9, "East", C2:C9)    average sales for East

The pattern is (criteria_range, criterion, [range_to_use]). The first range is where Sheets looks for matches. For SUMIF and AVERAGEIF the optional third range holds the numbers to combine, and it must line up row by row with the first. If you leave it out, the first range itself is summed or averaged, which is how =SUMIF(C2:C9, ">500") adds only the large sales. COUNTIF just counts the matching cells, so it has no third range.

Matching text ignores upper and lower case: "east" matches East.

Google Sheets cannot run on this page. Python stand-ins below imitate these functions on a small table so you can try criteria and check the results; the formulas in plain blocks are the real ones.

Writing criteria

A criterion is a text string that can start with an operator:

Criterion Matches
"East" or "=East" Cells equal to East
"<>East" Cells not equal to East
">500", ">=500", "<100" Numbers compared with 500 or 100
"<>" Cells that are not empty
"" Empty cells
"A*" Text starting with A
"*pro*" Text containing pro
"???" Text of exactly three characters

* matches any run of characters and ? matches exactly one. To match a real asterisk or question mark, put ~ before it: "~*".

When the limit lives in a cell, join the operator and the cell with &:

TEXT
=COUNTIF(C2:C9, ">" & F1)           greater than the number in F1
=SUMIF(D2:D9, ">=" & DATE(2024,1,1), C2:C9)    sales on or after 1 Jan 2024
=COUNTIF(A2:A9, F1)                 equal to whatever F1 contains

The quotes matter: writing >500 without quotes is a syntax error, and ">F1" would look for the literal text F1. Dates work because they are numbers (see Dates and Times).

Python Stand-in: SUMIF, COUNTIF and AVERAGEIF

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.

Several conditions: SUMIFS and COUNTIFS

Add an S to require all conditions at once:

TEXT
=COUNTIFS(A2:A9, "East", C2:C9, ">200")
=SUMIFS(C2:C9, A2:A9, "East", B2:B9, "Pro")
=AVERAGEIFS(C2:C9, A2:A9, "West", C2:C9, ">=300")

Each condition is a pair: a range and a criterion, and all ranges must have the same size. Now the trap: in SUMIFS and AVERAGEIFS the range to add or average comes first, while in SUMIF and AVERAGEIF it comes last. COUNTIFS has nothing to add, so it is just pairs. Mixing up the order gives a wrong total or #VALUE!.

MAXIFS(range, criteria_range, criterion, ...) and MINIFS do the same for the largest and smallest matches. There is no ORIFS: to count rows that match either of two values, add two counts, or use SUM(COUNTIF(A2:A9, {"East","West"})).

For a between condition, use the same range twice: =COUNTIFS(C2:C9, ">=100", C2:C9, "<=300").

Python Stand-in: SUMIFS, COUNTIFS and OR by adding

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.

Practical patterns

  • A summary table: list the regions in column E, then fill down =SUMIF($A$2:$A$9, E2, $C$2:$C$9). The locked ranges stay put while E2 follows each row (see Relative, Absolute and Mixed References). Later you can build the same table with a pivot table in a few clicks.
  • Percent of total: =SUMIF(A2:A9, E2, C2:C9) / SUM(C2:C9).
  • Blank checks: =COUNTIF(B2:B50, "") counts empty cells in a column, and =COUNTIF(B2:B50, "<>") counts the filled ones.
  • Dates in a month: =COUNTIFS(D2:D99, ">=" & DATE(2024,3,1), D2:D99, "<" & DATE(2024,4,1)).

When nothing matches

SUMIFS and COUNTIFS return 0 when no row matches, which is a sensible answer. AVERAGEIF and AVERAGEIFS cannot average zero rows, so they return #DIV/0!. Wrap them when a missing combination is normal:

TEXT
=IFERROR(AVERAGEIFS(D2:D100, B2:B100, "North", C2:C100, "Widget"), "no sales")

Use a visible text or 0 as the fallback, not an empty string, so you notice. Also remember that the first argument of SUMIFS and AVERAGEIFS is the range to calculate, while COUNTIFS has no calculate range: all its arguments are range, criterion pairs. The ranges in one formula must all be the same size, or Sheets reports an error.

Common mistakes

  • Mismatched range sizes. A2:A9 with C2:C10 returns #VALUE!.
  • Numbers stored as text. ">500" only compares real numbers; text numbers never match. See Entering and Editing Data.
  • Forgetting that the criteria cell should be a plain value. If F1 holds >500 as text, use =SUMIF(C2:C9, F1) without extra quotes.
  • Wildcards you did not intend. A product named Pro* in the data is matched by the criterion Pro~*, not Pro*.
  • Hidden rows. These functions include hidden and filtered rows. Use SUBTOTAL when you need to skip them.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which formula adds the sales in C2:C9 for rows where A2:A9 is "East"?

    SUMIF takes the criteria range first, then the criterion, then the range to add.
  2. 2. Where does the range to add go in SUMIFS?

    SUMIFS puts the sum range first, followed by pairs of criteria range and criterion. SUMIF is the other way around.
  3. 3. What does =COUNTIF(A2:A50, "<>") count?

    "<>" means not equal to empty, so it counts the filled cells.
  4. 4. How do you use the number in F1 as a lower limit in a criterion?

    Join the operator text and the cell value with &. A quoted ">F1" would search for the literal text F1.

Summary

  • SUMIF, COUNTIF and AVERAGEIF apply one condition; their S versions require all of several conditions.
  • Criteria are text with operators (">500", "<>East"), wildcards (*, ?, ~ to escape) and joins such as ">" & F1.
  • In SUMIFS and AVERAGEIFS the range to total comes first; in SUMIF and AVERAGEIF it comes last.
  • For OR, add counts or sums; for between, use the same range twice.
  • Ranges must match in size, and hidden rows are still included.

Next: Pivot Tables