How to Use SUMIFS, COUNTIFS and AVERAGEIFS in Excel (Multiple Criteria): Excel learning resource
How to Use SUMIFS, COUNTIFS and AVERAGEIFS in Excel (Multiple Criteria)
Gharani builds up from plain SUMIF to SUMIFS on one running example, making the case for defaulting to SUMIFS even with a single condition since adding more criteria later never means rewriting the f…
How to Use SUMIFS, COUNTIFS and AVERAGEIFS in Excel (Multiple Criteria)
Leila Gharani
Open on YouTube (opens in a new tab)What this video actually covers
Gharani builds up from plain SUMIF to SUMIFS on one running example, making the case for defaulting to SUMIFS even with a single condition since adding more criteria later never means rewriting the formula's argument order. After a basic SUMIF, she adds a second and third criterion with SUMIFS, then extends the same formula with a wildcard for partial text matches and a comparison operator (greater-than-or-equal-to) in place of an exact match. The second half is the direct payoff for a SUMIFS/COUNTIFS exercise file: AVERAGEIFS reuses the identical argument order for averaging instead of summing, and COUNTIF/COUNTIFS drop the sum-range argument entirely since they only count matching rows rather than totaling a column. It closes by converting the source range into an Excel Table so the same formulas reference structured column names instead of cell ranges and keep working automatically as rows are added.
Where to jump in
What to practice while watching
- Write a single-criterion SUMIF on the practice dataset (for example, total sales for one region), then convert it to SUMIFS by adding a second criterion (region and product) without changing the sum-range argument.
- Add a third criterion (month) to the same SUMIFS formula, following the video's build-up from one condition to several.
- Swap one exact-text criterion for a wildcard (a product name starting with a given letter) and one numeric criterion for a comparison operator such as ">=1000".
- Duplicate the SUMIFS formula as AVERAGEIFS and separately as COUNTIFS against the same criteria, and confirm COUNTIFS needs one fewer argument since there is no sum range.
- Convert the dataset range into an Excel Table and rewrite one formula to reference table column names instead of cell ranges, the way the video does in its closing section.
Exercise file
Practice the steps above on a real workbook instead of a blank sheet — no sign-up required.
Download SUMIFS / COUNTIFS practice dataset (.xlsx)Recommended learning path
Start by watching the lesson once without pausing, then reopen Excel and rebuild the example with your own small dataset. Save one clean practice workbook before moving to the next topic.
Source details
- Channel: Leila Gharani
- Format: video
- Level: Mixed
- Topic: Formulas & Functions
Go deeper with this skill
Write formulas that are correct, readable, and resilient when the workbook changes. For this article, the goal is to practice: Gharani builds up from plain SUMIF to SUMIFS on one running example, making the case for defaulting to SUMIFS even with a single condition since adding more criteria later never means rewriting the f…
Practice workbook setup
Create a small table with clean headers, sample edge cases, and expected outputs you can check manually.
Practice workflow
- Write the business rule in plain English before writing the formula.
- Build the formula in small pieces and test each piece.
- Use absolute references, table references, or named ranges intentionally.
- Test blanks, missing values, duplicate matches, and unexpected text.
Quality checks
- The formula returns the expected result for normal and edge-case rows.
- References point to stable ranges or tables.
- Someone else can understand the formula after reading the labels around it.
Common mistakes
- Fixing one row without testing the rest of the column.
- Using approximate match or partial criteria without meaning to.
- Letting hidden spaces or text numbers cause false mismatches.
Next actions
- Create three test rows that should return different outcomes.
- Compare the result with an alternate formula or manual filter.
Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.
