# Excel conditional sum and count functions

> Conditional functions total, count or average only the rows that match a rule: sales for one region, tickets still open, orders over $1,000.

Source: https://spreadsheetpractice.com/functions/category/conditional · Updated October 29, 2026

Conditional functions total, count or average only the rows that match a rule: sales for one region, tickets still open, orders over $1,000. They are the formula version of a filtered total, and the ones that end in S handle several conditions at once.

## Which one to use

| You want to | Use | Why |
|---|---|---|
| Count rows that match one condition | [COUNTIF](https://spreadsheetpractice.com/functions/countif) | Give it a range and a criterion like "Open" or ">1000". |
| Count rows that match several conditions | [COUNTIFS](https://spreadsheetpractice.com/functions/countifs) | Every pair of range and criterion must be true for a row to count. |
| Add up the values for one category | [SUMIF](https://spreadsheetpractice.com/functions/sumif) | Checks one column and adds the matching rows from another. |
| Add up values that meet several conditions | [SUMIFS](https://spreadsheetpractice.com/functions/sumifs) | Sum range first, then as many range-and-criterion pairs as you need. |
| Average the values for one group | [AVERAGEIF](https://spreadsheetpractice.com/functions/averageif) | Averages only the rows that match, and AVERAGEIFS takes more conditions. |
| Find the largest or smallest value in a group | [MAXIFS](https://spreadsheetpractice.com/functions/maxifs) | MAXIFS and MINIFS work like SUMIFS. They need Excel 2019 or later. |

## All 8 conditional sum and count functions

| Function | What it does | Available in |
|---|---|---|
| [AVERAGEIF](https://spreadsheetpractice.com/functions/averageif) | AVERAGEIF averages only the values that meet one condition: the average order from one region, response times for chat tickets, scores above zero. | Every current version |
| [AVERAGEIFS](https://spreadsheetpractice.com/functions/averageifs) | AVERAGEIFS averages the values where every condition is true, such as Algebra scores from the fall term or passing scores only. | Every current version |
| [COUNTIF](https://spreadsheetpractice.com/functions/countif) | COUNTIF counts the cells in a range that meet one condition: tickets marked "Open", scores of 70 or more, product names containing "lamp". | Every current version |
| [COUNTIFS](https://spreadsheetpractice.com/functions/countifs) | COUNTIFS counts the rows where every condition is true: orders that are from the East region and shipped, deals between $500 and $1,000. | Every current version |
| [MAXIFS](https://spreadsheetpractice.com/functions/maxifs) | MAXIFS returns the largest value among the rows that meet one or more conditions: the biggest deal in a region, the latest order date for a customer. | Excel 2019 and later, including Microsoft 365 |
| [MINIFS](https://spreadsheetpractice.com/functions/minifs) | MINIFS returns the smallest value among the rows that meet one or more conditions: the cheapest quote for a part, the earliest due date in a project, the lowest score above zero. | Excel 2019 and later, including Microsoft 365 |
| [SUMIF](https://spreadsheetpractice.com/functions/sumif) | SUMIF adds up values for the rows that meet one condition: total travel expenses, revenue for one product, every invoice over $500. | Every current version |
| [SUMIFS](https://spreadsheetpractice.com/functions/sumifs) | SUMIFS adds the values where every condition is true: East region widget sales, February revenue, large orders from one customer. | Every current version |

## Comparisons

- [COUNTIF vs COUNTIFS](https://spreadsheetpractice.com/functions/compare/countif-vs-countifs): COUNTIF counts cells that meet one condition. COUNTIFS counts rows that meet several conditions at once.
- [SUMIF vs SUMIFS](https://spreadsheetpractice.com/functions/compare/sumif-vs-sumifs): SUMIF adds the values that meet one condition; SUMIFS handles several.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
