Generička formula
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Sažetak
Da biste brojali vidljive retke samo s kriterijima, možete koristiti prilično složenu formulu koja se temelji na SUMPRODUCT, SUBTOTAL i OFFSET. U prikazanom primjeru formula u C12 je:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Predgovor
Funkcija SUBTOTAL može lako generirati zbrojeve i brojeve za skrivene i neskrivene retke. Međutim, ne može se nositi s kriterijima poput COUNTIF ili SUMIF bez neke pomoći. Jedno je rješenje korištenje SUMPRODUCT za primjenu funkcije SUBTOTAL (putem OFFSET) i kriterija. Pojedinosti ovog pristupa opisane su u nastavku.
Obrazloženje
U osnovi, ova formula djeluje postavljanjem dva polja unutar SUMPRODUCT-a. Prvi niz primjenjuje kriterije, a drugi niz obrađuje vidljivost:
=SUMPRODUCT(criteria*visibility)
Kriteriji se primjenjuju s dijelom formule:
=(C5:C8=C10)
Koji generira ovakav niz:
(FALSE;TRUE;FALSE;TRUE)
Gdje ISTINA znači "zadovoljava kriterije". Napomena, jer na ovom polju koristimo množenje (*), vrijednosti TRUE FALSE automatski će se matematičkim operacijama pretvoriti u 1 i 0, pa ćemo na kraju dobiti:
(0;1;0;1)
Filtar vidljivosti primjenjuje se pomoću SUBTOTAL-a, s funkcijom broj 103.
SUBTOTAL može izuzeti skrivene retke prilikom izvođenja izračuna, pa ga u ovom slučaju možemo koristiti za generiranje "filtra" za izuzeće skrivenih redaka unutar SUMPRODUCT-a. Problem je ipak što SUBTOTAL vraća jedan broj, dok nam je potreban niz rezultata da bismo ga uspješno koristili unutar SUMPRODUCT-a. Trik je u tome da se pomoću OFFSET doda SUBTOTAL jedna referenca po retku, tako da će OFFSET vratiti jedan rezultat po retku.
Naravno, to zahtijeva još jedan trik, a to je davanje OFFSET-a niza koji sadrži jedan broj po retku, počevši od nule. To radimo s izrazom izgrađenim na funkciji ROW:
=ROW(C5:C8)-MIN(ROW(C5:C8)
koji će generirati niz poput ovog:
(0;1;2;3)
Ukratko, drugi niz (koji obrađuje vidljivost pomoću SUBTOTAL) generira se ovako:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
I, konačno, imamo:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Koji vraća 1.
Više kriterija
Formulu možete proširiti tako da obrađuje više kriterija poput ovog:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Zbrajanje rezultata
Da biste vratili zbroj vrijednosti umjesto brojanja, možete prilagoditi formulu tako da uključuje raspon zbroja:
=SUMPRODUCT(criteria*visibility*sumrange)
Kriteriji i nizovi vidljivosti rade na isti način kao što je gore objašnjeno, isključujući stanice koje nisu vidljive. Ako vam je potrebno djelomično podudaranje, možete konstruirati izraz pomoću ISUMBER + SEARCH, kao što je ovdje objašnjeno.
