Start » Excelformler, trix och tips » Excel - Formler och Funktioner » SUBTOTAL/DELSUMMA – Summering av filtrerade listor i Excel

SUBTOTAL/DELSUMMA – Summering av filtrerade listor i Excel





Filtrering av listor i Excel är nog något som de flesta av oss Excelanvändare ofta använder oss av. I den här texten tänkte jag visa på två olika sätt att summera kolumner i filtrerade listor, men också att det faktiskt lätt kan bli fel ibland.

Video som visar SUBTOTAL/DELSUMMA

(Ta upp videon i helskärm för bästa bild!)

 

Lista i Excel med vanlig summaformel

Vi tänker oss följande lista i Excel:

 

Vi har i listan ett antal produkter med olika kvantiteter. Under kolumnen för antal finns en summaformel som summerar raderna 4 till 11. En helt vanlig lista i Excel med andra ord.

Sedan filtrerar någon kolumnen…

Nu tänker vi oss att någon (t ex en kollega som vi distribuerat Excelfilen till) applicerar ett filter på kolumnen Produkt.

 

Som du kan se i bilden ovan så summerar vår initiala summaformel fortfarande alla celler i rader 4 till 11, vilket i och för sig är korrekt – problemet är att summan med stor sannolikhet kommer att misstolkas. Om Excelfilen skulle handla om t ex banköverföringar i miljonklassen som ska utföras så börjar det hela nu att bli lite svettigt.

Lösning: SUBTOTAL/DELSUMMA

I syfte att förekomma det fel som beskrivits ovan, dvs att “fel” summa vissa vid filtrering i Excel, så kan vi använda oss av formeln SUBTOTAL (DELSUMMA på svenska). I nedanstående bild så återgår vi till ursprungsexemplet, dvs en ofiltrerad lista, men där vi även lägger till en summering via SUBTOTAL-formeln:

 

I fallet med en ofiltrerad lista i Excel så ger alltså SUM (SUMMA) och SUBTOTAL (DELSUMMA) samma utfall, i detta fallet 37 stycken produkter.

Om vi nu filtrerar listan på “Droppen” så kommer formlerna att ge följande utfall:

 

Slutsatsen är enkel: SUBTOTAL-formeln visar “rätt” summa vid filtrering. Observera att den vanliga summaformeln inte begår något fel i och med filtreringen – den är helt enkelt programmerad att summera alla ingående rader oavsett om de för tillfället visas eller ej.

Slutsats: SUM eller SUBTOTAL?

Som vi har sett ovan så har dessa två formler två olika sätt att arbeta på:

SUM (SUMMA): Summerar alla ingående celler oavsett om de visas eller ej
SUBTOTAL (DELSUMMA): Summerar alla ingående celler som för tillfället ej har filtrerats bort

Det viktigaste är nog att vara medveten om skillnaden mellan dessa två formler, samt att man förstår vad konsekvenserna kan bli. Som jag ser det så kan man i princip alltid använda SUBTOTAL (DELSUMMA) vid den här typen av summeringar. Emellertid så är just den formeln något krångligare att skriva in än den vanliga summaformeln.

Fördjupning kring SUBTOTAL (DELSUMMA)

Som du sett ovan så ser SUBTOTAL-formeln ut enligt följande:

=SUBTOTAL(9;C4:C11)

Det första argumentet i formeln (dvs i föreliggande fall “9″) styr vilken typ av beräkning som formeln kommer att utföra på efterföljande cellmatris. Just siffran 9 betyder att formeln summerar (och inte t ex räknar antalet poster) vilket också är den i särklass vanligaste tillämpningen av formeln.

Vidare så kan nämnas att om du t ex trycker på knappen för AUTOSUM i en redan filtrerad lista så kommer Excel med automatik att lägga ut SUBTOTAL-formeln istället för den vanliga summaformeln.

 

 

 

 





19 kommentarer till “SUBTOTAL/DELSUMMA – Summering av filtrerade listor i Excel”

  1. Leif Jönsson

    Hej!
    Bra förklaring, men hur går jag tillväga för att beräkna antalet celler i min filtrerade lista? I fallet med droppen skulle svaret blivit 3.

  2. Excelkungen

    Hej Leif!

    Enkelt, för att räkna antalet celler (istället för att summera cellerna) byter du ut “9″ till “2″ (eller möjligen “3″), dvs:

    =SUBTOTAL(2;C4:C11)

    Det första argumentet i formeln SUBTOTAL/DELSUMMA (siffran “9″ i formeln ovan) avgör vilken typ av operation som utförs:

    1 = AVERAGE (MEDEL)
    2 = COUNT (ANTAL)
    3 = COUNTA (ANTALV)
    4 = MAX (MAX)
    5 = MIN (MIN)
    9 = SUM (SUMMA)

    /Anders

  3. Leif Jönsson

    Tack, Anders!
    Det funkade bra (med 3 i Exel 2003). Du räddade min lektion.
    Mvh
    Leif

  4. Kristian Gleisner

    Hej. Jag försöker få till en beräkning av antalet unika tal, dvs i ditt exempel ovan skall det bli 2. I nedan exempel blir det också 2 (dvs det finns två tal, 11 och 22)
    nummer
    11
    11
    22
    22
    22
    22

    Mvh, Kristian

  5. Excelkungen

    Kristian,

    Jag har inget bra förslag på hur man kan räkna antalet unika tal i en FILTRERAD lista, hur som helst så kan följande formel användas för att beräkna antalet unika tal i en lista (i exemplet A2:A6)

    =SUMPRODUCT((A2:A6<>“”)/COUNTIF(A2:A6;A2:A6))

    SUMPRODUCT = PRODUKTSUMMA
    COUNTIF = ANTAL.OM

    /Anders
    ExcelKungen.com

  6. Fredrik

    Hej,

    Jag försöker få excel att automatiskt räkna om födelsedatum till antal år. I cell D3 har jag födelsedatum och i D4 vill jag att personens ålder ska synas.

    Har försökt med =DATEDIF(D3;Idag;”y”) utan att få det att fungera. Får felmeddelande “#NAMN?”

    Vad gör jag fel?

  7. Fredrik

    En annan fråga. Hur skriver jag en formel där jag vill få fram medelvärdet i en filtrerad kolumn?

    Hälsningar

    Fredrik

  8. Excelkungen

    Medelvärdet i en filtrerad kolumn kan du få fram via justering av SUBTOTAL-formeln: istället för en “9″ sätter du en “1″.

    Exempel:

    =SUBTOTAL(1;C4:C11)

    /Anders
    Excelkungen.com

  9. Excelkungen

    Angående beräkning av en persons ålder i antal år:

    Följande formel tror jag ger ett hyfsat bra estimat på en persons år:

    =INT((TODAY()-A1)/365,25)

    (personens födelsedatum i cell A1)

    /Anders
    ExcelKungen.com

  10. Fredrik

    Hej,

    Tack för svaret.

    Gällande min första fråga har jag försökt detta genom formeln =DELSUMMA(1;AR3:AR3500)

    Får dock 0 i värde där det vid mina beräkningar borde vara 86,5.

    Antog därför att Excel tog med alla 0-värden i tabellen (trots att dessa inte är filtrerade) och testade även därför formeln =DELSUMMA(1;AR3:AR3500;”>0″)

    Denna formeln godtogs dock inte av Excel.

    Vad gör jag för fel?

    Med vänliga hälsningar

    Fredrik

  11. Fredrik

    Aha, nu hittade jag det. Testade =DELSUMMA(101;AR3:AR3500) och då funkade det bättre.

    Tack för hjälpen!

    Med vänliga hälsningar

    Fredrik

  12. Thomas Nilsson

    Toppen-bra förklaringar!

  13. Ola

    Hej

    Kan man använda sig av antal.om i delsumma?
    Jag vill inte att formeln ska räkna med 0 när jag filtrerar

    Tack
    Vänligen
    Ola

  14. Sandra

    Hej! 

    Har haft stora problem med detta och era svar har hjälpt mycket. 

    Undrade bara hur man på samma sätt beräknar median på filtrerade listor?

    Tacksam för snabbt svar

  15. Sandra

    Glömde nämna att jag behöver veta hur man beräknar standardavvikelse också.

    MVH

  16. Per

    Finns det något sätt att använda SUMMA.OMF på bara filtret, dvs en funktion som skulle hetat DEFSUMMA.OMF om den fanns?

  17. Bea

    Finns det att du kan låsa en cell med en delsumma i till just den katigorin du har filtrerat fram?

  18. Excelkungen

    Hej Bea – kan du förklara lite ytterligare hur du menar?

    Anders/ExcelKungen

  19. Anna

    Tack för "subtotal"-visningen! Jag skulle vilja använda den och summera flera olika filtrerade poster på samma sida, Om jag använder din video som ex, så vill jag tex ha en summering på alla Koster och sedan vill jag kunna söka fram en ny sökning utan att summeringen ändrar sig på Kosters summering. Hur gör man då? Det jag är ute efter är en lista med summeringar över då exempelvis Koster, Toby, Droppen och oavsett vad som filtreras fram så skall dessa summor stå kvar. 

    Tack på förhand! 

    Anna

     

Lämna en kommentar