Глава 17 · 17.18
Сумиране само на филтрираните редове, включително с допълнително условие
Филтрирате списък с продажби по една категория, но сборът под него продължава да показва сумата на всички редове. Това е нормално: SUM брои и редовете, които филтърът скрива. Ако искате да сумирате само това, което виждате, използвайте SUBTOTAL. А ако искате в рамките на филтрираните редове да добавите още едно условие или да пропуснете стойностите за грешка, има още две полезни формули.
Като пример използваме списъка с продажби на Bloemisterij Het Zonnetje: в A е датата, в B е продуктът, в C е категорията и в D е сумата, от ред 2 до ред 300 включително. Върху заглавния ред има филтър (вижте Сортиране и филтриране @@OMSCHRИЙВАНЕ@@).
Къде да поставите формулите?
Не под списъка: формула непосредствено под списъка може сама да бъде скрита при филтриране и да се премести при сортиране. Но не и просто до списъка: филтърът скрива цели редове, така че формула в F3 изчезва заедно с ред 3, когато той бъде филтриран. Затова ги поставете в заглавния ред (ред 1, до заглавията) или на отделен работен лист. В примера те са в ред 1, от колона F нататък.
Сумата на видимите редове
=SUBTOTAL(109;D2:D300)
Първото число избира изчислението. 9 означава сума, 109 също, с една разлика: 109 пропуска и редовете, които сте скрили ръчно (десен бутон на мишката, Скрий). 9 пропуска само редовете, скрити от филтъра. Затова използвайте 109, когато искате да сумирате това, което виждате.
| Изчисление | Филтрирани | И ръчно скрити |
|---|---|---|
| Сума | 9 | 109 |
| Брой непразни клетки | 3 | 103 |
| Средна стойност | 1 | 101 |
| Максимален | 4 | 104 |
Броя на видимите редове получавате с =SUBTOTAL(103;B2:B300).

Видими и с допълнително условие
Филтрирали сте по категорията Boeketten (букети) и искате в нея да сумирате само траурните композиции (Rouwstuk). SUMIFS не взема предвид филтъра, а SUBTOTAL не поддържа условие. В интернет ще намерите за целта формула със SUMPRODUCT, SUBTOTAL и OFFSET. В Euro-Office тя връща стойността за грешка #NUM!. С помощна колона обаче се получава, и то по-лесно:
- Добавете до списъка колона Zichtbaar (видими), тук колона E. Изберете E2:E300, въведете =SUBTOTAL(103;D2) и натиснете Ctrl+Enter. Всеки ред получава 1, докато е видим, и 0, когато филтърът го скрие.
- Сумирайте със SUMIFS и включете помощната колона като допълнително условие: =SUMIFS(D2:D300;B2:B300;"Rouwstuk";E2:E300;1).
Когато промените филтъра, помощната колона и сумата се преизчисляват веднага.
Пропускане на стойности за грешка
Ако някъде в колоната има #N/A или #DIV/0!, SUM и SUBTOTAL също връщат стойност за грешка. AGGREGATE може да пропуска грешките:
=AGGREGATE(9;7;D2:D300)
9 отново означава сума, а 7 означава: пропускане на скритите редове и на стойностите за грешка.
Капани
- Скритите колони се включват в сметката: SUBTOTAL взема предвид само скритите редове.
