Poglavje 17 · 17.18
Seštevanje samo filtriranih vrstic, tudi z dodatnim pogojem
Prodajni seznam filtrirate po eni kategoriji, vendar skupni znesek pod njim še vedno prikazuje vsoto vseh vrstic. To je pravilno: SUM upošteva tudi vrstice, ki jih filter skrije. Če želite sešteti samo to, kar vidite, uporabite SUBTOTAL. Če pa želite znotraj filtriranih vrstic dodati še en pogoj ali preskočiti vrednosti napak, sta na voljo še dve uporabni formuli.
Za primer vzemimo prodajni seznam cvetličarne Bloemisterij Het Zonnetje: v stolpcu A je datum, v B izdelek, v C kategorija in v D znesek, od vrstice 2 do vključno 300. Na naslovni vrstici je filter (glejte Razvrščanje in filtriranje).
Kam vpišete formule?
Ne pod seznam: formula neposredno pod seznamom se lahko pri filtriranju sama skrije, pri razvrščanju pa premakne. Prav tako ne kar ob seznam: filter skrije cele vrstice, zato formula v F3 izgine, ko filter skrije vrstico 3. Zato jih vpišite v naslovno vrstico (vrstica 1, ob naslovih stolpcev) ali na ločen delovni list. V primeru so v vrstici 1, od stolpca F naprej.
Vsota vidnih vrstic
=SUBTOTAL(109;D2:D300)
Prvo število določa izračun. 9 pomeni vsoto, 109 prav tako, z eno razliko: 109 preskoči tudi vrstice, ki ste jih skrili ročno (desni klik, Skrij). 9 preskoči samo vrstice, ki jih skrije filter. Uporabite torej 109, če želite sešteti to, kar vidite.
| Izračun | Filtrirano | Tudi ročno skrito |
|---|---|---|
| Vsota | 9 | 109 |
| Število izpolnjenih celic | 3 | 103 |
| Povprečje | 1 | 101 |
| Največ | 4 | 104 |
Število vidnih vrstic preštejete z =SUBTOTAL(103;B2:B300).

Vidno in še dodaten pogoj
Filtrirali ste po kategoriji Boeketten in želite znotraj nje sešteti samo žalne aranžmaje. SUMIFS ne upošteva filtra, SUBTOTAL pa ne pozna pogojev. Na spletu boste za to našli formulo s SUMPRODUCT, SUBTOTAL in OFFSET. Ta v Euro-Office vrne vrednost napake #NUM!. S pomožnim stolpcem pa gre, in to preprosteje:
- Ob seznamu dodajte stolpec Zichtbaar (vidno), tukaj stolpec E. Izberite E2:E300, vnesite =SUBTOTAL(103;D2) in pritisnite Ctrl+Enter. Vsaka vrstica dobi 1, dokler je vidna, in 0, ko jo filter skrije.
- Seštejte s SUMIFS in pomožni stolpec dodajte kot dodaten pogoj: =SUMIFS(D2:D300;B2:B300;"Rouwstuk";E2:E300;1).
Ko spremenite filter, se pomožni stolpec in vsota takoj znova izračunata.
Preskakovanje vrednosti napak
Če je kje v stolpcu #N/A ali #DIV/0!, tudi SUM in SUBTOTAL vrneta vrednost napake. AGGREGATE lahko napake preskoči:
=AGGREGATE(9;7;D2:D300)
Število 9 spet pomeni vsoto, 7 pa pomeni: preskoči skrite vrstice in vrednosti napak.
Pasti
- Skriti stolpci se vseeno upoštevajo: SUBTOTAL gleda samo skrite vrstice.
