Chapter 17 · 17.16
Showing the top 5 per category
The best-selling products per department, the biggest customers per region, the busiest days per month: you don't want the top 5 of the whole list, but the top 5 within one category. LARGE works on the whole column and returns the same product twice when amounts are equal. In Euro-Office you solve this with a single formula.
As an example, we use the annual revenue per product of Bloemisterij Het Zonnetje: column A holds the product, B the category (Rozen, Tulpen, Planten) and C the revenue, from row 2 to row 18. F1 contains the category you want to see: Rozen.
The top 5 in one formula
In H2, type:
=TAKE(SORT(FILTER(A2:C18,B2:B18=F1),3,-1),5)
Read it from the inside out:
- FILTER(A2:C18,B2:B18=F1) keeps only the rows of the category in F1, with all three columns.
- SORT(...,3,-1) sorts those rows by the third column (the revenue), from high to low (-1).
- TAKE(...,5) keeps the top 5 rows.
Press Enter: the five products with their category and revenue appear in H2 to J6. If you type Planten in F1, the list changes straight away.

Equal amounts are not a problem: in the example, Grand Prix and Ecuadoriaanse roos both have a revenue of 3,400, and both appear in the list. Using LARGE twice with INDEX and MATCH would show Grand Prix twice.
Just the amounts, with categories side by side
If you only want the five highest amounts, for example for several categories side by side, use LARGE on a filtered list. Put the categories in F1, G1 and onwards, select F2:G6 and type:
=IFERROR(LARGE(FILTER($C$2:$C$18,$B$2:$B$18=F$1),ROWS($1:1)),"")
then press Ctrl+Enter. FILTER keeps only the amounts of the category in row 1. ROWS($1:1) is 1 in the first row and becomes 2, 3, 4 and 5 in the rows below: the largest, the second largest and so on. IFERROR leaves the cell empty if a category has fewer than five products; Tulpen has only four.
Please note: Excel websites often show =LARGE(IF($B$2:$B$18=F$1,$C$2:$C$18),ROWS($1:1)). In Euro-Office, this gives the wrong result if you just press Enter: IF then doesn't look at the whole column, so you get the largest amounts of all categories, or #NUM!. If you confirm it with Ctrl+Shift+Enter, the formula gets curly brackets {…} and the result is correct. Using FILTER as shown above is simpler.
Just the highest amount
For a single number, it's simpler: =MAXIFS(C2:C18,B2:B18,F1) returns the highest revenue within the category, here 6,150.
Pitfalls
- Fewer than 5 products in a category: without IFERROR, LARGE returns #NUM! for the rows that don't exist. TAKE has no trouble with this and simply shows fewer rows.
- The formula with TAKE stays empty: there is something in the 5 rows and 3 columns where the result needs to go. See Lists that update themselves with FILTER, UNIQUE and SORT.
- A colleague using Excel 2019 or older will see #NAME? with TAKE, SORT and FILTER.
