SUMIFS interview question: summarise sales by region
Turn a raw sales list into a summary by region and product, without a pivot table. The formula interviewers expect, the $ signs that make it copy, and a sheet that grades yours.
COUNTIFS function
COUNTIFS counts the rows that meet every condition you give it, such as orders that are from the East region and still open.
Available in Excel 2007 and later, Excel for the web and Google SheetsOn this page
COUNTIFS syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Argument | Required | What it means |
|---|---|---|
criteria_range1 | Yes | The range to test. |
criteria1 | Yes | The condition: "Open", ">100", "<>", or a cell. |
criteria_range2, criteria2 … | No | More pairs. A row counts only when it meets all of them. |
Examples
Open orders in one region
Rows where the region is East and the status is Open.
| Row | A | B | C |
|---|---|---|---|
| 1 | Order | Region | Status |
| 2 | #1001 | East | Open |
| 3 | #1002 | West | Open |
| 4 | #1003 | East | Closed |
| 5 | #1004 | East | Open |
| 6 | #1005 | North | Open |
| 7 | |||
| 8 | Open in East | 2 |
=COUNTIFS(B2:B6,"East",C2:C6,"Open") → 2Count values above a number
How many invoices are over 500. The operator and the number go inside the quotes together.
| Row | A | B |
|---|---|---|
| 1 | Invoice | Amount |
| 2 | INV-01 | 320 |
| 3 | INV-02 | 780 |
| 4 | INV-03 | 1,250 |
| 5 | INV-04 | 500 |
| 6 | ||
| 7 | Over 500 | 2 |
=COUNTIFS(B2:B5,">500") → 2Common mistakes
- Expecting ">500" to include 500. It doesn't. Use ">=500" to count 500 itself.
- Ranges of different sizes. All criteria ranges must have the same number of rows.
- Counting blanks. Use "" to count empty cells and "<>" to count non-empty ones.
FAQ
What's the difference between COUNTIF and COUNTIFS?
COUNTIF takes one condition, COUNTIFS one or more. COUNTIFS covers both, so it's the one to learn.
How do I count cells between two numbers?
Use the same range twice: =COUNTIFS(B2:B5,">=100",B2:B5,"<=500").
Guides that use COUNTIFS
Worked problems with practice sheets.
#VALUE! error in Excel: what it means and how to fix it
#VALUE! means a formula got the wrong kind of value, most often text where it needs a number. The usual causes and fixes, with a practice sheet.
Know COUNTIFS? Prove it on a real sheet.
Practise COUNTIFS and the functions around it in a live grid that grades your result and explains your mistakes.
Runs in your browser · Graded on your result · No install