Generisk formel
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Sammendrag
For å oppsummere de nederste n-verdiene i et utvalg som samsvarer med kriteriene, kan du bruke en matriseformel basert på SMALL-funksjonen, pakket inn i SUM-funksjonen. I den generiske formen av formelen (ovenfor) representerer område1 rekkevidden til celler sammenlignet med kriteriene , område2 inneholder numeriske verdier som bunnverdiene hentes fra, og N representerer "nth".
I eksemplet inneholder den aktive cellen denne formelen:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Hvor farge er det navngitte området B5: B12 og verdien er det navngitte området C5: C12.
Merk: dette er en matriseformel og må angis med kontroll + skift + enter.
Forklaring
I sin enkleste form returnerer SMALL den "Nth minste" verdien i et område med denne konstruksjonen:
=SMALL (range,N)
Så for eksempel:
=SMALL (C5:C12,2)
vil returnere den 2. minste verdien i området C5: C12, som er 5 i eksemplet som vises.
Imidlertid, hvis du leverer en "matrisekonstant" (f.eks. En konstant i formen (1,2,3)) til SMALL som det andre argumentet, vil SMALL returnere en matrise med resultater i stedet for et enkelt resultat. Så formelen:
=SMALL (C5:C12, (1,2,3))
vil returnere 1., 2. og 3. minste verdi C5: C12 i en matrise som dette: (4,5,7).
Så, trikset her er å filtrere verdiene basert på farge før SMALL kjører. Vi gjør dette med et uttrykk basert på IF-funksjonen:
IF(color=E5,value)
Dette bygger en rekke verdier som blir matet inn i SMALL. I hovedsak er det bare verdier som er knyttet til fargen rød som gjør det til matrisen. Der fargen er lik "rød", inneholder matrisen et tall, og der fargen ikke er rød, inneholder matrisen FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
SMALL-funksjonen ignorerer FALSE-verdiene og returnerer de 3 minste verdiene i matrisen: (4,8,10). SUM-funksjonen returnerer det endelige resultatet, 22.
