Excel SUMIFS, COUNTIFS and AVERAGEIFS Explained

SUMIFS, COUNTIFS and AVERAGEIFS are the Excel functions that add, count and average only the rows that meet conditions you set. SUMIFS totals a column for the rows that match, COUNTIFS counts them, and AVERAGEIFS averages them. Each condition is a pair of a range and a criterion, for example B2:B11, "North", and when there are several pairs a row must satisfy all of them. Older single-condition versions, SUMIF, COUNTIF and AVERAGEIF, still work.

This guide uses one small sales table throughout, shows how to write criteria (text, numbers, wildcards, dates, cell references), how to handle OR conditions and the AND-with-OR combination that SUMIFS does not do directly, and the traps that give quietly wrong totals. Every result was produced by running the formula in Microsoft Excel.

The sample data

All examples use this table of ten sales, in A1:E11. Amounts are in column D, so most formulas sum, count or average D2:D11:

SUMIF and SUMIFS

SUMIF(range, criteria, [sum_range]) adds the cells of sum_range where range meets one condition. SUMIFS handles one or more conditions, and it turns the arguments around: the range to add comes first. Each extra condition is another pair of range and criteria:

To filter dates, use two conditions on the same column, a start with >= and an end with < the first day of the next month. This also works when the dates contain times.

COUNTIFS, AVERAGEIFS, MAXIFS and MINIFS

The pattern is the same for counting and averaging. COUNTIFS takes only pairs of range and criteria, because there is nothing to add up. AVERAGEIFS takes the average range first, like SUMIFS. MAXIFS and MINIFS (Excel 2019 and Microsoft 365) find the biggest and smallest value among the matching rows:

Writing criteria

A criterion is a piece of text (or a number) that Excel interprets. These are the forms you will use:

Putting the criteria in cells

Typing "East" inside the formula means you must edit the formula to change it. Put the criteria in cells and refer to them, and the same formula can be reused. Here H1 holds the region and H2 the minimum amount:

Criteria in H1 and H2, results in H4 to H6

What is inside the cells

H4 adds all East sales (200 + 600 + 250). H5 adds only East sales above the minimum of 300, and H6 counts East sales of 300 or more. Note how the operator and the cell are joined: ">"&H2.

OR conditions

SUMIFS and COUNTIFS only know AND. There are three ways to get OR.

1. Add two results

North or East is the sum for North plus the sum for East. That is safe when a row cannot belong to both groups. A row cannot be in two regions at once.

2. Give an array constant

Write the alternatives in curly brackets and wrap the function in SUM: Excel runs the formula once per alternative and adds the results.

3. Combine AND with OR: where the brackets go

“North and (Pen or Bag)” keeps North as a fixed condition and lets the product vary. The array constant goes on the product only. The mirror rule, “(North and Pen) or Bag”, is a different question: it has to be two separate sums added together.

The double-counting trap in the fifth row is the one to remember. Adding two SUMIFS is only right when no row can satisfy both conditions. When the groups overlap, subtract the overlap, or use SUMPRODUCT.

Gotchas

Numbers and text that looks like numbers behave differently in criteria. A1 holds 5, A2 the text x, A3 the text 10, A4 the number 10 and A5 is empty:

Cheat sheet

Try it yourself

Use the sales table from above and work out each answer first.

1. What is the total of unpaid (No) South sales?

Show solution

=SUMIFS(D2:D11,B2:B11,"South",E2:E11,"No") gives 1350 (300 + 900 + 150).

2. How many sales are between 300 and 600, inclusive?

Show solution

=COUNTIFS(D2:D11,">=300",D2:D11,"<=600") gives 5. Two conditions on the same column are allowed.

3. What is the average North sale?

Show solution

=AVERAGEIFS(D2:D11,B2:B11,"North") gives 487.5 (1,950 divided by 4).

4. Total sales for North or East, in one formula.

Show solution

=SUM(SUMIFS(D2:D11,B2:B11,{"North","East"})) gives 3000.

Frequently asked questions

What is the difference between SUMIF and SUMIFS?

SUMIF takes one condition and puts the sum range last: SUMIF(range, criteria, sum_range). SUMIFS takes any number of conditions and puts the sum range first: SUMIFS(sum_range, range1, criteria1, ...).

How do I use SUMIFS with multiple criteria?

Add more pairs of range and criteria. A row must meet every condition. For example =SUMIFS(D:D, B:B, "North", C:C, "Pen") adds North pens.

How do I do OR in SUMIFS?

Add two SUMIFS when the groups cannot overlap, or use an array constant: =SUM(SUMIFS(D:D, B:B, {"North","East"})). If a row can belong to both groups, subtract the overlap.

How do I use a cell reference in a SUMIFS criterion?

For an exact match, use the cell directly: B:B, H1. With an operator, join it: D:D, ">"&H2.

How do I sum between two dates with SUMIFS?

Use two conditions on the date column: A:A, ">="&start, A:A, "<="&end, with the start and end dates in cells or DATE().

Why does SUMIFS return #VALUE!?

The ranges have different sizes. Every criteria range must have exactly the same number of rows and columns as the sum range.

Are COUNTIF and SUMIFS case sensitive?

No. "north" matches North. Use SUMPRODUCT with EXACT when you need case-sensitive matching.

How do I count cells that contain certain text?

Wrap the text in asterisks: =COUNTIF(A:A, "*invoice*") counts cells that contain the word anywhere. The asterisks match any characters, and text only.