Excel SUMIF, COUNTIF, and AVERAGEIF

SUMIF, COUNTIF, and AVERAGEIF are conditional versions of the basic SUM, COUNT, and AVERAGE functions. Instead of calculating across all values in a range, they only include values that meet a specific condition. These functions are essential for analyzing data based on categories, criteria, or conditions — without needing to filter or sort the data first.

COUNTIF Function

What It Does

Counts the number of cells in a range that meet a specified condition.

Syntax

  =COUNTIF(range, criteria)

• range: The group of cells to check.
• criteria: The condition that must be met to count the cell. Can be a number, text, expression, or cell reference.

Example

  | A          | B        |
1 | Salesperson | Region  |
2 | Alice       | North   |
3 | Bob         | South   |
4 | Carol       | North   |
5 | David       | East    |
6 | Eva         | North   |

  How many salespeople are in the North region?
  =COUNTIF(B2:B6, "North")
  Result: 3

COUNTIF with Numbers and Expressions

  Scores in C2:C10.

  Count scores greater than 70:
  =COUNTIF(C2:C10, ">70")

  Count scores equal to 100:
  =COUNTIF(C2:C10, 100)

  Count scores not equal to 0:
  =COUNTIF(C2:C10, "<>0")

Wildcard Characters in COUNTIF

• * (asterisk): Matches any sequence of characters.
• ? (question mark): Matches any single character.

  Count names starting with "A":
  =COUNTIF(A2:A6, "A*")

  Count 4-letter names:
  =COUNTIF(A2:A6, "????")

SUMIF Function

What It Does

Adds values in a range, but only where a corresponding cell in another range meets a specified condition.

Syntax

  =SUMIF(range, criteria, [sum_range])

• range: The cells to check against the criteria.
• criteria: The condition to match.
• sum_range (optional): The cells to add up. If omitted, the range itself is summed.

Example

  | A           | B      | C      |
1 | Salesperson | Region | Sales  |
2 | Alice       | North  | 5000   |
3 | Bob         | South  | 8000   |
4 | Carol       | North  | 6500   |
5 | David       | East   | 4000   |
6 | Eva         | North  | 7200   |

  Total sales from the North region:
  =SUMIF(B2:B6, "North", C2:C6)

  Explanation:
  → Check column B for "North"
  → Add the corresponding values from column C

  Result: 5000 + 6500 + 7200 = 18700

SUMIF with a Numeric Condition

  Sum all sales greater than 6000:
  =SUMIF(C2:C6, ">6000")

  Result: 8000 + 6500 + 7200 = 21700

SUMIF with a Cell Reference as Criteria

  Region to filter is in cell E2 (e.g., "South"):
  =SUMIF(B2:B6, E2, C2:C6)

  → This allows the filter value to be changed dynamically without editing the formula.

AVERAGEIF Function

What It Does

Calculates the average of values in a range, but only where a corresponding cell meets a specified condition.

Syntax

  =AVERAGEIF(range, criteria, [average_range])

Example

  Using the same data as above:

  Average sales from the North region:
  =AVERAGEIF(B2:B6, "North", C2:C6)

  → Adds sales from North: 5000 + 6500 + 7200 = 18700
  → Divides by count of North entries: 3
  Result: 6233.33

AVERAGEIF with Number Condition

  Average only sales greater than 5000:
  =AVERAGEIF(C2:C6, ">5000", C2:C6)

  → Qualifying values: 8000, 6500, 7200
  Result: 7233.33

COUNTIFS, SUMIFS, AVERAGEIFS (Multiple Criteria)

The plural versions of these functions allow multiple conditions to be applied simultaneously. All conditions must be met for a cell to be included.

COUNTIFS Syntax

  =COUNTIFS(range1, criteria1, range2, criteria2, ...)

SUMIFS Syntax

  =SUMIFS(sum_range, range1, criteria1, range2, criteria2, ...)

Note: In SUMIFS, the sum_range comes first (the opposite of SUMIF).

AVERAGEIFS Syntax

  =AVERAGEIFS(average_range, range1, criteria1, range2, criteria2, ...)

Example: SUMIFS with Two Conditions

  | A           | B      | C    | D      |
1 | Salesperson | Region | Year | Sales  |
2 | Alice       | North  | 2023 | 5000   |
3 | Bob         | South  | 2023 | 8000   |
4 | Carol       | North  | 2024 | 6500   |
5 | David       | East   | 2023 | 4000   |
6 | Eva         | North  | 2024 | 7200   |

  Total sales from North in 2024:
  =SUMIFS(D2:D6, B2:B6, "North", C2:C6, 2024)

  → Matches row 4 (Carol: North, 2024, 6500)
  → Matches row 6 (Eva: North, 2024, 7200)
  Result: 13700

Example: COUNTIFS with Two Conditions

  Count salespeople from North with sales above 6000:
  =COUNTIFS(B2:B6, "North", D2:D6, ">6000")

  → Row 4: North, 6500 → qualifies
  → Row 6: North, 7200 → qualifies
  Result: 2

Practical Summary Example: Sales Report

  Dataset: Salesperson, Region, Month, Sales

  Useful formulas:
  Total all sales:
  =SUM(D2:D100)

  Count of "East" entries:
  =COUNTIF(B2:B100, "East")

  Total sales for "South":
  =SUMIF(B2:B100, "South", D2:D100)

  Average sales for "North" in 2024:
  =AVERAGEIFS(D2:D100, B2:B100, "North", C2:C100, "2024")

  Count of entries where sales > 10000 AND region = "West":
  =COUNTIFS(D2:D100, ">10000", B2:B100, "West")

Summary

• COUNTIF(range, criteria): Counts cells that match a condition.
• SUMIF(range, criteria, sum_range): Sums values where a condition is met.
• AVERAGEIF(range, criteria, avg_range): Averages values where a condition is met.
• The plural versions — COUNTIFS, SUMIFS, AVERAGEIFS — accept multiple conditions that all must be true simultaneously.
• Criteria can be text (in quotes), numbers, expressions like ">50", wildcards (* and ?), or cell references.