Generisk formel
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Sammanfattning
För att summera de nedre n-värdena i ett intervallmatchningskriterium kan du använda en matrisformel baserad på SMALL-funktionen, insvept i SUM-funktionen. I den generiska formen av formeln (ovan) representerar område1 cellintervallet jämfört med kriterier , intervall2 innehåller numeriska värden från vilka bottenvärden hämtas och N representerar "nth".
I exemplet innehåller den aktiva cellen följande formel:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Där färg är det namngivna området B5: B12 och värdet är det namngivna området C5: C12.
Obs: detta är en matrisformel och måste anges med kontroll + skift + enter.
Förklaring
I sin enklaste form returnerar SMALL värdet "Nth minste" i ett intervall med denna konstruktion:
=SMALL (range,N)
Så till exempel:
=SMALL (C5:C12,2)
returnerar det 2: a minsta värdet i intervallet C5: C12, vilket är 5 i exemplet som visas.
Men om du tillhandahåller en "arraykonstant" (t.ex. en konstant i formen (1,2,3)) till SMALL som det andra argumentet, kommer SMALL att returnera en matris med resultat istället för ett enda resultat. Så formeln:
=SMALL (C5:C12, (1,2,3))
returnerar det 1: a, 2: a och 3: e minsta värdet C5: C12 i en sådan matris: (4,5,7).
Så tricket här är att filtrera värdena baserat på färg innan SMALL körs. Vi gör detta med ett uttryck baserat på IF-funktionen:
IF(color=E5,value)
Detta bygger en rad värden som matas in i SMALL. I grund och botten är det bara värden som är associerade med färgen röd som gör det till matrisen. Om färgen är lika med "röd" innehåller matrisen ett tal och där färgen inte är röd innehåller matrisen FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
SMALL-funktionen ignorerar FALSKA värden och returnerar de tre minsta värdena i matrisen: (4,8,10). SUM-funktionen returnerar slutresultatet, 22.
