Poglavlje 17 · 17.18
Zbrajanje samo filtriranih redaka, i s dodatnim uvjetom
Filtrirate popis prodaje po jednoj kategoriji, ali zbroj ispod popisa i dalje prikazuje iznos svih redaka. To je ispravno: SUM zbraja i retke koje filtar skriva. Ako želite zbrojiti samo ono što vidite, upotrijebite SUBTOTAL. Ako unutar filtriranih redaka želite dodati još jedan uvjet ili preskočiti pogreške, postoje još dvije korisne formule.
Kao primjer uzimamo popis prodaje cvjećarnice Bloemisterij Het Zonnetje: u stupcu A je datum, u B proizvod, u C kategorija, a u D iznos, od retka 2 do retka 300. Na retku zaglavlja postavljen je filtar (pogledajte Sortiranje i filtriranje).
Gdje postaviti formule?
Ne ispod popisa: formula neposredno ispod popisa može se i sama sakriti pri filtriranju te pomaknuti pri sortiranju. No ni bilo gdje pokraj popisa: filtar skriva cijele retke, pa formula u F3 nestaje ako se redak 3 odfiltrira. Zato ih postavite u redak zaglavlja (redak 1, pokraj naslova stupaca) ili na zaseban radni list. U primjeru se nalaze u retku 1, od stupca F nadalje.
Zbroj vidljivih redaka
=SUBTOTAL(109;D2:D300)
Prvi broj određuje vrstu izračuna. 9 je zbroj, kao i 109, uz jednu razliku: 109 preskače i retke koje ste ručno sakrili (desni klik, Hide). 9 preskače samo retke koje skriva filtar. Dakle, upotrijebite 109 ako želite zbrojiti ono što vidite.
| Izračun | Filtrirano | I ručno sakriveno |
|---|---|---|
| Zbroj (SUM) | 9 | 109 |
| Broj popunjenih ćelija | 3 | 103 |
| Prosjek (AVERAGE) | 1 | 101 |
| Maksimum (MAX) | 4 | 104 |
Broj vidljivih redaka izračunajte formulom =SUBTOTAL(103;B2:B300).

Vidljivo i dodatni uvjet
Filtrirali ste po kategoriji Boeketten i unutar nje želite zbrojiti samo pogrebne aranžmane. SUMIFS ne uzima u obzir filtar, a SUBTOTAL ne poznaje uvjete. Na internetu ćete za to pronaći formulu sa SUMPRODUCT, SUBTOTAL i OFFSET. U Euro-Officeu ona daje pogrešku #NUM!. S pomoćnim stupcem to ipak uspijeva, i to jednostavnije:
- Pokraj popisa dodajte stupac Zichtbaar (vidljivo), ovdje stupac E. Odaberite E2:E300, upišite =SUBTOTAL(103;D2) i pritisnite Ctrl+Enter. Svaki redak dobiva 1 dok je vidljiv, a 0 kada ga filtar sakrije.
- Zbrojite pomoću SUMIFS i pomoćni stupac uključite kao dodatni uvjet: =SUMIFS(D2:D300;B2:B300;"Rouwstuk";E2:E300;1).
Kada promijenite filtar, pomoćni stupac i zbroj odmah se ponovno izračunavaju.
Preskakanje pogrešaka
Ako se negdje u stupcu nalazi #N/A ili #DIV/0!, i SUM i SUBTOTAL također vraćaju pogrešku. AGGREGATE može preskočiti pogreške:
=AGGREGATE(9;7;D2:D300)
9 je ponovno zbroj, a 7 znači: preskoči sakrivene retke i pogreške.
Zamke
- Sakriveni stupci ipak se uračunavaju: SUBTOTAL uzima u obzir samo sakrivene retke.
