Lesson 2 of 8 · 7 min · Formulas
SUMIFS, COUNTIFS and conditional summaries
What you will learn
SUMIFS,COUNTIFSandAVERAGEIFSwith several conditions- Operators and wildcards inside conditions
- Date-range summaries
- The older
SUMIFandCOUNTIF UNIQUEandFILTERfor 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
- Turn the data into a Table.
- Make a list of categories with
UNIQUE. - Add
SUMIFSfor revenue,COUNTIFSfor orders andAVERAGEIFSfor average order. - Add a percentage of total column:
=B2/SUM($B$2:$B$10). - 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
SUMIFSbut last inSUMIF. - Typing an operator without quotes (
>500instead of">500"). - Dates entered as text, so date conditions match nothing.
- Trailing spaces in the data (
"Pune "is not"Pune").
Try it yourself
- Compute the total amount per city with
SUMIFS, and add a percentage of total column. - Count refunded orders per product with
COUNTIFS. - Compute October 2025 sales using a start date and an end date from cells.
- 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,COUNTIFSandAVERAGEIFSapply 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. UNIQUEandFILTERbuild self-updating lists.
Going deeper
- SUMIFS function: syntax and examples.
- COUNTIFS function: counting with multiple criteria.
- FILTER function: dynamic arrays.
- UNIQUE function: distinct lists.
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.