Generička formula
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Sažetak
Da biste zbrojili donjih n vrijednosti u rasponu koji odgovara kriterijima, možete upotrijebiti formulu niza koja se temelji na funkciji SMALL, umotanoj unutar funkcije SUM. U generičkom obliku formule (gore), raspon1 predstavlja raspon stanica u usporedbi s kriterijima , raspon2 sadrži numeričke vrijednosti iz kojih se preuzimaju donje vrijednosti, a N predstavlja "n-tu".
U primjeru aktivna ćelija sadrži ovu formulu:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Gdje je boja imenovani raspon B5: B12, a vrijednost imenovani raspon C5: C12.
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter.
Obrazloženje
U svom najjednostavnijem obliku, SMALL vraća "N-u najmanju" vrijednost u rasponu s ovom konstrukcijom:
=SMALL (range,N)
Tako, na primjer:
=SMALL (C5:C12,2)
vratit će 2. najmanju vrijednost u rasponu C5: C12, što je 5 u prikazanom primjeru.
Međutim, ako kao drugi argument SMALL-u dostavite "konstantu niza" (npr. Konstantu u obliku (1,2,3)), SMALL će vratiti niz rezultata umjesto jednog rezultata. Dakle, formula:
=SMALL (C5:C12, (1,2,3))
vratit će 1., 2. i 3. najmanju vrijednost C5: C12 u nizu poput ovog: (4,5,7).
Dakle, trik je ovdje filtriranje vrijednosti na temelju boje prije nego što se SMALL pokrene. To radimo s izrazom koji se temelji na funkciji IF:
IF(color=E5,value)
Ovo gradi niz vrijednosti unesenih u SMALL. U osnovi u polje ulaze samo vrijednosti povezane s crvenom bojom. Gdje je boja jednaka "crvenoj", niz sadrži broj, a tamo gdje boja nije crvena, niz sadrži FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
Funkcija SMALL zanemaruje FALSE vrijednosti i vraća 3 najmanje vrijednosti u polju: (4,8,10). Funkcija SUM vraća konačni rezultat, 22.
