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

Add up only the filtered rows, even with an extra condition

You filter a sales list on one category, but the total below it still shows the amount for all rows. That is correct: SUM also adds up the rows the filter hides. If you only want to add up what you see, use SUBTOTAL. And if you want an extra condition within the filtered rows, or want to skip error values, there are two more handy formulas.

As an example, we use the sales list of Bloemisterij Het Zonnetje: the date in A, the product in B, the category in C and the amount in D, from row 2 to row 300. There is a filter on the header row (see Sort and filter).

Where do you put the formulas?

Not below the list: a formula directly below the list can itself become hidden when you filter, and move along when you sort. But not just anywhere next to the list either: a filter hides entire rows, so a formula in F3 disappears too when row 3 is filtered out. So put them in the header row (row 1, next to the headings), or on a separate worksheet. In the example they are in row 1, from column F onwards.

The sum of the visible rows

=SUBTOTAL(109,D2:D300)

The first number chooses the calculation. 9 is the sum, and so is 109, with one difference: 109 also skips rows you have hidden manually (right-click, Hide). 9 only skips rows hidden by the filter. So use 109 if you want to add up what you see.

CalculationFilteredAlso manually hidden
Sum9109
Number of non-empty cells3103
Average1101
Maximum4104

You count the number of visible rows with =SUBTOTAL(103,B2:B300).

SUBTOTAL only counts the visible rows
Filtered on Boeketten (bouquets): SUBTOTAL(109,...) in G1 only adds up the visible amounts in the Bedrag (amount) column (1); at the bottom it says 40 of 83 records filtered. In K1 the formula with OFFSET (#NUM!), in M1 the SUMIFS with the helper column Zichtbaar (visible)

Visible and an extra condition

You have filtered on the category Boeketten (bouquets) and within that you only want to add up the funeral arrangements (product Rouwstuk). SUMIFS ignores the filter, and SUBTOTAL has no condition. Online you will find a formula for this using SUMPRODUCT, SUBTOTAL and OFFSET. In Euro-Office, that gives the error value #NUM!. With a helper column it does work, and more simply:

  1. Add a column Visible next to the list, here column E. Select E2:E300, type =SUBTOTAL(103,D2) and press Ctrl+Enter. Each row gets a 1 as long as it is visible, and a 0 when the filter hides it.
  2. Add up with SUMIFS and include the helper column as an extra condition: =SUMIFS(D2:D300,B2:B300,"Rouwstuk",E2:E300,1).

If you change the filter, the helper column and the sum recalculate straight away.

Skipping error values

If there is a #N/A or #DIV/0! somewhere in the column, SUM and SUBTOTAL also return an error value. AGGREGATE can skip errors:

=AGGREGATE(9,7,D2:D300)

The 9 is the sum again, and the 7 means: skip hidden rows and error values.

Pitfalls

  • Hidden columns are still included: SUBTOTAL only looks at hidden rows.

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…