Orders per region with COUNTIF
PremiumIn F2, count the orders for the region in E2 with COUNTIF. Lock the region range with $ so the formula can be filled down.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Region | Orders | ||
| 2 | North | Shoes | 420 | North | |||
| 3 | South | Shirts | 310 | South | |||
| 4 | North | Shirts | 150 | West | |||
| 5 | West | Shoes | 500 | ||||
| 6 | South | Shoes | 260 | ||||
| 7 | North | Shoes | 380 | ||||
| 8 | West | Shirts | 190 | ||||
| 9 | South | Shirts | 240 | ||||
| 10 |
Premium lesson
Unlock Summary tables and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Next with premium:SUMPRODUCTVLOOKUP
It comes up in this mock test: Excel test for budgeting and planning
Need help?
Why this works
In a pivot table this is "Count of Sales by Region". COUNTIF counts the rows of A2:A9 that say North, which is 3. Next to the totals from SUMIF, the counts show whether a region sells a lot or just sells often.
Common mistake: Using COUNT or COUNTA for a group. They count the whole column, not the rows of one region.
The answer
- F2
=COUNTIF(1$A$2:$A$9,2E2)
In plain words: Count the cells 1in A2:A9 that match 2the region in E2.