Capitolul 17 · 17.18
Adunarea doar a rândurilor filtrate, inclusiv cu o condiție suplimentară
Filtrați o listă de vânzări după o singură categorie, dar totalul de dedesubt rămâne suma tuturor rândurilor. Este corect: SUM adună și rândurile pe care filtrul le ascunde. Dacă doriți să adunați doar ce vedeți, folosiți SUBTOTAL. Iar dacă doriți o condiție suplimentară în cadrul rândurilor filtrate sau să ignorați valorile de eroare, există încă două formule utile.
Ca exemplu luăm lista de vânzări a florăriei Bloemisterij Het Zonnetje: în A data, în B produsul, în C categoria și în D suma, de la rândul 2 până la rândul 300. Pe rândul de antet este aplicat un filtru (consultați Sortare și filtrare).
Unde plasați formulele?
Nu sub listă: o formulă aflată direct sub listă poate fi ascunsă ea însăși la filtrare și se poate muta la sortare. Dar nici oriunde lângă listă: un filtru ascunde rânduri întregi, așa că o formulă din F3 dispare odată cu rândul 3 atunci când acesta este filtrat. Plasați-le de aceea pe rândul de antet (rândul 1, lângă titluri) sau pe o foaie de calcul separată. În exemplu, ele se află pe rândul 1, începând cu coloana F.
Suma rândurilor vizibile
=SUBTOTAL(109;D2:D300)
Primul număr alege calculul. 9 înseamnă sumă, la fel și 109, cu o singură diferență: 109 ignoră și rândurile pe care le-ați ascuns manual (clic dreapta, Ascunde). 9 ignoră doar rândurile ascunse de filtru. Folosiți deci 109 dacă doriți să adunați ce vedeți.
| Calcul | Filtrate | Inclusiv ascunse manual |
|---|---|---|
| Sumă | 9 | 109 |
| Număr de celule completate | 3 | 103 |
| Punct median | 1 | 101 |
| Maxim | 4 | 104 |
Numărul de rânduri vizibile îl obțineți cu =SUBTOTAL(103;B2:B300).

Vizibil și o condiție suplimentară
Ați filtrat după categoria Boeketten și doriți să adunați în cadrul acesteia doar aranjamentele funerare (Rouwstuk). SUMIFS nu ține cont de filtru, iar SUBTOTAL nu acceptă o condiție. Pe internet găsiți pentru aceasta o formulă cu SUMPRODUCT, SUBTOTAL și OFFSET. În Euro-Office, aceasta returnează valoarea de eroare #NUM!. Cu o coloană ajutătoare funcționează însă, și mai simplu:
- Adăugați lângă listă o coloană Poate vizualiza (Vizibil), aici coloana E. Selectați E2:E300, tastați =SUBTOTAL(103;D2) și apăsați Ctrl+Enter. Fiecare rând primește valoarea 1 cât timp este vizibil și 0 atunci când filtrul îl ascunde.
- Adunați cu SUMIFS și includeți coloana ajutătoare ca o condiție suplimentară: =SUMIFS(D2:D300;B2:B300;"Rouwstuk";E2:E300;1).
Dacă modificați filtrul, coloana ajutătoare și suma se recalculează imediat.
Ignorarea valorilor de eroare
Dacă undeva în coloană apare #N/A sau #DIV/0!, atunci și SUM și SUBTOTAL returnează o valoare de eroare. AGGREGATE poate ignora erorile:
=AGGREGATE(9;7;D2:D300)
9 înseamnă din nou suma, iar 7 înseamnă: ignorarea rândurilor ascunse și a valorilor de eroare.
Capcane
- Coloanele ascunse sunt totuși incluse în calcul: SUBTOTAL ține cont doar de rândurile ascunse.
