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.
It comes up in this mock test: Excel test for budgeting and planning
Your first month for $9.50 $19
50% off your first month when you join today.
Then $19/month. Cancel any time, and get a full refund within 14 days if it isn't for you.
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.