Lesson 2 of 8 · 7 min · Formulas

SUMIFS, COUNTIFS and conditional summaries

Covers: Microsoft 365 Excel. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • SUMIFS, COUNTIFS and AVERAGEIFS with several conditions
  • Operators and wildcards inside conditions
  • Date-range summaries
  • The older SUMIF and COUNTIF
  • UNIQUE and FILTER for lists that update themselves

In simple words

Most analyst questions sound like "total sales for Hyderabad in October" or "how many orders were refunded". The question has a total and some conditions. SUMIFS adds up the numbers that pass all your conditions, and COUNTIFS counts the rows that do. They are the Excel version of WHERE plus SUM in SQL.

Words to know

  • Criteria: the condition a cell must meet, such as "Hyderabad" or ">500".
  • Criteria range: the column of cells where Excel checks the condition.
  • Sum range: the column of numbers to add up.
  • Wildcard: a symbol such as * that stands for any text.

The sample data

Imagine an Excel Table named Sales with columns Date, City, Product, Status and Amount and a few thousand rows.

SUMIFS

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

Note that the sum range comes first. Total for Hyderabad:

=SUMIFS(Sales[Amount], Sales[City], "Hyderabad")

Add more conditions. All must be true (they are combined with AND):

=SUMIFS(Sales[Amount], Sales[City], "Hyderabad", Sales[Status], "Paid")

Instead of typing the text, point to a cell so the formula is reusable: =SUMIFS(Sales[Amount], Sales[City], $A2) where A2 holds a city name. Copy it down a list of cities for an instant summary.

COUNTIFS and AVERAGEIFS

=COUNTIFS(Sales[City], "Hyderabad", Sales[Status], "Refunded")
=AVERAGEIFS(Sales[Amount], Sales[Product], "Notebook")

COUNTIFS has no sum range; it counts rows meeting all conditions. AVERAGEIFS returns #DIV/0! if nothing matches, so wrap it: =IFERROR(AVERAGEIFS(...), 0).

Operators and wildcards

For conditions other than "equals", write the operator and value inside quotes:

Criteria Meaning
">500" greater than 500
">=500" 500 or more
"<>Paid" not equal to "Paid"
"<>" not blank
"" blank
"Hyd*" starts with Hyd (* is any number of characters)
"*pack" ends with pack
"?at" ? is exactly one character

To combine an operator with a cell value, join with &:

=COUNTIFS(Sales[Amount], ">" & $G1)

To match a real * or ?, put ~ before it.

Date ranges

Dates are numbers underneath, so use two conditions for a range, joining the operator and the date with &:

=SUMIFS(Sales[Amount], Sales[Date], ">=" & DATE(2025,10,1), Sales[Date], "<" & DATE(2025,11,1))

That is "all of October 2025". Using "on or after the 1st and before the next 1st" avoids problems with dates that include a time of day. With start and end dates in cells G1 and G2:

=SUMIFS(Sales[Amount], Sales[Date], ">=" & $G$1, Sales[Date], "<=" & $G$2)

SUMIF and COUNTIF

The older, single-condition versions: =SUMIF(range, criteria, sum_range) (here the sum range comes last) and =COUNTIF(range, criteria). They still work, but the "IFS" versions handle one or many conditions and have a consistent argument order, so prefer them.

An OR condition (Hyderabad or Pune) needs adding two results: =SUMIFS(..., "Hyderabad") + SUMIFS(..., "Pune"), or SUM(SUMIFS(Sales[Amount], Sales[City], {"Hyderabad","Pune"})).

UNIQUE and FILTER

Microsoft 365 and Excel 2021 have dynamic array functions that return a list that "spills" into the cells below:

=UNIQUE(Sales[City])                    list each city once
=SORT(UNIQUE(Sales[City]))              sorted
=FILTER(Sales, Sales[City]="Pune")      all rows for Pune
=FILTER(Sales[Amount], (Sales[City]="Pune")*(Sales[Status]="Paid"), "none")

In FILTER, multiply conditions for AND and add them for OR; the last argument is shown if nothing matches. A fast summary table:

A2:  =SORT(UNIQUE(Sales[City]))
B2:  =SUMIFS(Sales[Amount], Sales[City], A2#)

The # after a spill cell (A2#) means "the whole spilled range", so the totals column grows when a new city appears. If the cells below are not empty you get #SPILL!.

Building a quick report

  1. Turn the data into a Table.
  2. Make a list of categories with UNIQUE.
  3. Add SUMIFS for revenue, COUNTIFS for orders and AVERAGEIFS for average order.
  4. Add a percentage of total column: =B2/SUM($B$2:$B$10).
  5. Check: the column total must equal =SUM(Sales[Amount]). If it does not, some rows have a city spelt differently, which is a data cleaning job.

Common mistakes

  • Sum range and criteria range of different sizes, which gives #VALUE!.
  • Forgetting that the sum range comes first in SUMIFS but last in SUMIF.
  • Typing an operator without quotes (>500 instead of ">500").
  • Dates entered as text, so date conditions match nothing.
  • Trailing spaces in the data ("Pune " is not "Pune").

Try it yourself

  1. Compute the total amount per city with SUMIFS, and add a percentage of total column.
  2. Count refunded orders per product with COUNTIFS.
  3. Compute October 2025 sales using a start date and an end date from cells.
  4. List the unique products with UNIQUE, then give each its revenue using the spilled range.

Check yourself

What is the argument order of SUMIFS?

The range to sum first, then pairs of criteria range and criteria.

How do you write "greater than the value in cell G1" as a criteria?

">" & G1.

How do you count blank cells in a column with COUNTIFS?

=COUNTIFS(range, "").

Why prefer SUMIFS to SUMIF?

It supports one or many conditions with a consistent argument order, so you do not have to change formulas when conditions grow.

Interview questions

How would you get total sales per month and product?

Either a pivot table, or a grid of months and products using SUMIFS with date conditions and a product condition; a pivot is quicker, SUMIFS is better when the layout is fixed.

How do you count unique customers in a list?

=COUNTA(UNIQUE(Sales[Customer])) in Microsoft 365 or Excel 2021; in older versions use a pivot table with the distinct count option or SUMPRODUCT(1/COUNTIF(range, range)).

What does #SPILL! mean?

A dynamic array formula cannot show its full result because other cells below or beside it are not empty.

Summary

  • SUMIFS, COUNTIFS and AVERAGEIFS apply several conditions (AND) to a table.
  • Use operators in quotes, wildcards for patterns and & to join an operator to a cell.
  • For date ranges, use >= start and < the day after the end.
  • UNIQUE and FILTER build self-updating lists.

Going deeper

See how much of this you can explain

A free mock interview asks you new questions on Excel for analysts for Data Analyst and scores every answer, with what to fix.