Count a cross-table with COUNTIFS
PremiumCount the orders with regions down and products across. In F3, use COUNTIFS for the region in E3 and the product in F2, with $E3 and F$2 so the formula can be filled down and across.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | |||||
| 2 | North | Shoes | 420 | Shoes | Shirts | |||
| 3 | South | Shirts | 310 | North | ||||
| 4 | North | Shirts | 150 | South | ||||
| 5 | West | Shoes | 500 | West | ||||
| 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
Why this works
This is a pivot table's "Count of Sales" with Region in rows and Product in columns. Each cell counts the rows that match its own region and product: North shoes appear twice. $E3 keeps reading column E as you fill right, and F$2 keeps reading row 2 as you fill down, so one formula fills all six cells.
Common mistake: Locking both parts of the labels, $E$3 or $F$2. Every cell then counts the same region or the same product.
The answer
- F3
=COUNTIFS(1$A$2:$A$9,2$E3,3$B$2:$B$9,4F$2)
In plain words: Count the rows where 1the region in A2:A9 is 2the region in column E and 3the product in B2:B9 is 4the product in row 2.