Kapitel 17 · 17.18
Læg kun de filtrerede rækker sammen, også med en ekstra betingelse
Du filtrerer en salgsliste på én kategori, men totalen nedenunder viser stadig beløbet for alle rækker. Det er korrekt: SUM tæller også de rækker med, som filteret skjuler. Vil du kun lægge det sammen, du kan se, så brug SUBTOTAL. Og vil du have en ekstra betingelse inden for de filtrerede rækker eller springe fejlværdier over, er der to andre praktiske formler.
Som eksempel bruger vi salgslisten fra Bloemisterij Het Zonnetje: datoen i A, produktet i B, kategorien i C og beløbet i D, fra række 2 til og med 300. Der er et filter på overskriftsrækken (se Sortering og filtrering).
Hvor skal formlerne stå?
Ikke under listen: en formel lige under listen kan selv blive skjult, når du filtrerer, og flytte med, når du sorterer. Men heller ikke bare ved siden af listen: et filter skjuler hele rækker, så en formel i F3 forsvinder også, hvis række 3 bliver filtreret fra. Sæt dem derfor i overskriftsrækken (række 1, ved siden af overskrifterne) eller på et separat regneark. I eksemplet står de i række 1, fra kolonne F.
Summen af de synlige rækker
=SUBTOTAL(109;D2:D300)
Det første tal vælger beregningen. 9 er summen, det er 109 også, med én forskel: 109 springer også rækker over, som du selv har skjult (højreklik, Skjul). 9 springer kun rækker over, som filteret skjuler. Brug derfor 109, hvis du vil lægge det sammen, du kan se.
| Beregning | Filtreret | Også skjult manuelt |
|---|---|---|
| Sum | 9 | 109 |
| Antal udfyldte celler | 3 | 103 |
| Gennemsnit | 1 | 101 |
| Maksimum | 4 | 104 |
Antallet af synlige rækker tæller du med =SUBTOTAL(103;B2:B300).

Synlig og en ekstra betingelse
Du har filtreret på kategorien Boeketten og vil inden for den kun lægge begravelsesbinderierne (Rouwstuk) sammen. SUMIFS ser ikke på filteret, og SUBTOTAL kender ingen betingelse. På internettet finder du en formel til det med SUMPRODUCT, SUBTOTAL og OFFSET. Den giver fejlværdien #NUM! i Euro-Office. Med en hjælpekolonne lykkes det, og det er enklere:
- Lav en kolonne Synlig ved siden af listen, her kolonne E. Markér E2:E300, skriv =SUBTOTAL(103;D2) og tryk på Ctrl+Enter. Hver række får et 1, så længe den er synlig, og et 0, når filteret skjuler den.
- Læg sammen med SUMIFS, og brug hjælpekolonnen som ekstra betingelse: =SUMIFS(D2:D300;B2:B300;"Rouwstuk";E2:E300;1).
Ændrer du filteret, beregner hjælpekolonnen og summen straks igen.
Spring fejlværdier over
Står der et sted i kolonnen #N/A eller #DIV/0!, giver SUM og SUBTOTAL også en fejlværdi. AGGREGATE kan springe fejl over:
=AGGREGATE(9;7;D2:D300)
9 er igen summen, og 7 betyder: spring skjulte rækker og fejlværdier over.
Faldgruber
- Skjulte kolonner tæller med: SUBTOTAL ser kun på skjulte rækker.
