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. They are the formula version of a filtered total, and the ones that end in S handle several conditions at once.
- Functions
- 8
- With a graded exercise
- All 8
- Start with
COUNTIF
Which one should I use?
| If you need to… | Use | Why |
|---|---|---|
| Count rows that match one condition | COUNTIF | Give it a range and a criterion like "Open" or ">1000". |
| Count rows that match several conditions | COUNTIFS | Every pair of range and criterion must be true for a row to count. |
| Add up the values for one category | SUMIF | Checks one column and adds the matching rows from another. |
| Add up values that meet several conditions | SUMIFS | Sum range first, then as many range-and-criterion pairs as you need. |
| Average the values for one group | AVERAGEIF | Averages only the rows that match, and AVERAGEIFS takes more conditions. |
| Find the largest or smallest value in a group | MAXIFS | MAXIFS and MINIFS work like SUMIFS. They need Excel 2019 or later. |
Try COUNTIF live
The most useful conditional sum and count function, on a sample sheet you can edit. Change a value and watch the results update.
G2=COUNTIF(B2:B8,"Open") 4 Matching ignores case, so "open" counts tooG3=COUNTIF(C2:C8,">=3") 4 A comparison goes inside the quotesG4=COUNTIF(D2:D8,"*login*") 3 Wildcards: * matches any characters around the word
All 8 conditional sum and count functions
| Function | What it does | Practice exercise |
|---|---|---|
AVERAGEIF function | AVERAGEIF averages only the values that meet one condition: the average order from one region, response times for chat tickets, scores above zero. | Average order by region |
AVERAGEIFS function | AVERAGEIFS averages the values where every condition is true, such as Algebra scores from the fall term or passing scores only. | Conditional average |
COUNTIF function | COUNTIF counts the cells in a range that meet one condition: tickets marked "Open", scores of 70 or more, product names containing "lamp". | Count by region |
COUNTIFS function | COUNTIFS counts the rows where every condition is true: orders that are from the East region and shipped, deals between $500 and $1,000. | Count with a threshold |
MAXIFS functionExcel 2019 and later | 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. | Last order date per customer |
MINIFS functionExcel 2019 and later | 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. | Best price per supplier |
SUMIF function | SUMIF adds up values for the rows that meet one condition: total travel expenses, revenue for one product, every invoice over $500. | Sum by region |
SUMIFS function | SUMIFS adds the values where every condition is true: East region widget sales, February revenue, large orders from one customer. | Two-condition total |
Head-to-head
- COUNTIF vs COUNTIFSCOUNTIF counts cells that meet one condition. COUNTIFS counts rows that meet several conditions at once.
- SUMIF vs SUMIFSSUMIF adds the values that meet one condition; SUMIFS handles several.
Learn them in order
These functions are taught step by step in the Logic & conditions track, which is free. Each lesson ends with graded exercises on real-looking data.
- COUNTIF & SUMIF8 min
- COUNTIFS, SUMIFS & AVERAGEIFS10 min
- Capstone: sales report15 min · Pro