Skip to content

Cookie settings

Choose which kinds of cookies you allow. You can change your choice at any time via Cookie settings at the bottom of every page. Privacy statement

Necessary

Always on

Needed to log in, keep your cart and remember your choices. These are always on.

Which cookies?
  • weboke_sessie, weboke_klant, weboke_remember: your session and staying logged in
  • weboke_basket: your cart
  • wkBtwIncl: showing prices with or without VAT
  • wok_cookies: your cookie choice, 12 months

Measures which ad leads to a visit or order, so we do not pay for ads that do nothing.

Which cookies?
  • Google Ads via Google Tag Manager (_gcl_au, _gcl_aw), up to 90 days
  • OpenAI Ads, ads in ChatGPT
  • wok_aff: which partner referred you to us (affiliate programme), 60 days

4.6

Country and language

Each country has its own site, with prices and VAT for that country.

View

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:

  1. FILTER(A2:C18,B2:B18=F1) keeps only the rows of the category in F1, with all three columns.
  2. SORT(...,3,-1) sorts those rows by the third column (the revenue), from high to low (-1).
  3. 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.

The top 5 of the Rozen category
The category is in F1 (1). SORT sorts by the Omzet column (2). F2:G6 shows the five highest amounts per category using LARGE and FILTER, H2:J6 shows the full top 5 using TAKE, SORT and FILTER

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.

One moment…

Set up your domains

For each domain, arrange the nameservers, the link to your hosting and who becomes the owner.

Loading your domains…