Många gånger vill vi beräkna några värden per månad. Som hur mycket försäljning gjordes under en relevant månad. Detta kan du enkelt göra med pivottabeller, men om du försöker ha en dynamisk rapport än kan vi använda en SUMPRODUCT- eller SUMIFS -formel för att summera per månad.
Låt oss börja med SUMPRODUCT -lösning.
Här är den generiska formeln för att få summa per månad i Excel
= SUMPRODUCT (sum_range,-(TEXT (date_range, "MMM") = month_text))
Sum_range : Det är intervallet som du vill summera per månad.
Datumintervall : Det är datumintervallet du kommer att titta på i månader.
Månadstext: Det är månaden i textformat som du vill summera värden för.
Låt oss nu se ett exempel:
Exempel: Summa värden per månad i Excel
Här har vi något värde i samband med datum. Dessa datum är januari, februari och mars månad 2019.
Som du kan se i bilden ovan är alla datum för år 2019. Nu behöver vi bara summera värden i E2: G2 med månader i E1: G1.
Nu för att summera värden enligt månader skriver du denna formel i E2:
= SUMPRODUCT (B2: B9,-(TEXT (A2: A9, "MMM") = E1)))
Om du vill kopiera det i angränsande celler använder du absoluta referenser eller namngivna intervall som du i bilden.
Detta ger oss den exakta summan av varje månad.
Hur det fungerar?
Börja inifrån, låt oss titta på TEXT (A2: A9, "MMM") del. Här extraherar TEXT -funktionen månad från varje datum i intervall A2: A9 i textformat till en array. Översätta till formel till = SUMPRODUCT (B2: B9,-({"Jan"; "Jan"; "Feb"; "Jan"; "Feb"; "Mar"; "Jan"; "Feb"} = E1) )
Därefter TEXT (A2: A9, "MMM")= E1: Här jämförs varje månad i array med text i E1. Eftersom E1 innehåller "Jan", omvandlas varje "Jan" i array till TRUE och andra till FALSE. Detta översätter formeln till = SUMPRODUCT ($ B $ 2: $ B $ 9,-{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})
Nästa -(TEXT (A2: A9, "MMM") = E1), omvandlar SANT FALSKT till binära värden 1 och 0. Formeln översätts till = SUMPRODUCT ($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}).
Till sist SUMPRODUKT($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}): SUMPRODUCT -funktionen multiplicerar motsvarande värden i $ B $ 2: $ B $ 9 till array {1; 1; 0; 1; 0; 0; 1; 0} och lägger till dem. Därför får vi summan av värdet som 20052 i E1.
SUMMA OM Månader från olika år
I exemplet ovan var alla datum från samma år. Tänk om de var från olika år? Ovanstående formel summerar värden per månad oavsett år. Till exempel kommer Jan 2018 och Jan 2019 att läggas till om vi använder ovanstående formel. Vilket är fel i de flesta fall.
Detta kommer att hända eftersom vi inte har några kriterier för året i exemplet ovan. Om vi lägger till årskriterier också kommer det att fungera.
Den generiska formeln för att få summa efter månad och år i Excel
= SUMPRODUCT (sum_range,-(TEXT (date_range, "MMM") = month_text),-(TEXT (date_range, "yyyy") = TEXT (year, 0)))
Här har vi lagt till ytterligare ett kriterium som kontrollerar året. Allt annat är detsamma.
Låt oss lösa exemplet ovan, skriv denna formel i cell E1 för att få summan av Jan år 2017.
= SUMPRODUCT (B2: B9,-(TEXT (A2: A9, "MMM") = E1),-(TEXT (A2: A9, "yyyy") = TEXT (D2,0)))
Innan du kopierar i nedanstående celler använder du namngivna intervall eller absoluta referenser. I bilden har jag använt namngivna områden för kopiering i de intilliggande cellerna.
Nu kan vi också se summan av värdet i månader och år.
Hur fungerar det?
Den första delen av formeln är densamma som föregående exempel. Låt oss förstå den extra delen som lägger till årskriterierna.
-(TEXT (A2: A9, "åååå") = TEXT (D2,0)): TEXT (A2: A9, "åååå") konverterar datum i A2: A9 som år i textformat till en array. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
För det mesta är året skrivet i talformat. För att jämföra ett tal med text vi har jag konverterat år int text med TEXT (D2,0). Därefter jämförde vi detta textår med en rad år som TEXT (A2: A9, "åååå") = TEXT (D2,0). Detta returnerar en array med true-false {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}. Därefter konverterade vi true false till tal med - operator. Detta ger oss {0; 0; 1; 1; 0; 1; 0; 1}.
Så slutligen kommer formeln att översättas till = SUMPRODUCT (B2: B9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 }). Där första arrayen är värden. Nästa matchas månad och tredje är år. Slutligen får vi vår summa värden som 2160.
Använd SUMIFS -funktionen för att summera efter månad
Generisk formel
= SUMIFS (sum_range, date_range, ”> =” & startdate, date_range, ”<=” & EOMONTH (start_date, 0))
Här,Sum_range : Det är intervallet som du vill summera per månad.
Datumintervall : Det är datumintervallet du kommer att titta på i månader.
Start datum : Det är startdatumet från vilket du vill summera. För det här exemplet är det 1: a i den angivna månaden.
Exempel: Summa värden per månad i Excel
Här har vi ett värde kopplat till datum. Dessa datum är januari, februari och mars månad 2019.
Vi behöver bara summera dessa värden med månaden där. Nu var det enkelt om vi hade månader och år separat. Men det är de inte. Vi kan inte använda någon hjälpkolumn här.
Så för att förbereda rapporten har jag utarbetat ett rapportformat som innehåller månad och summa värden. I månadskolumnen har jag faktiskt ett månadens startdatum. För att bara se månaden, välj startdatum och tryck på CTRL+1.
Skriv "mmm" i anpassat format.
Nu har vi våra data redo. Låt oss summera värdena efter månad.
Skriv denna formel i E3 för att summera per månad.
= SUMMER (B3: B10, A3: A10, "> =" & D3, A3: A10, "<=" & EOMONTH (D3,0))
Använd absoluta referenser eller namngivna intervall innan du kopierar ner formeln.
Så äntligen fick vi resultatet.
Så, hur fungerar det?
Som vi vet kan SUMIFS -funktionen summera värden på flera kriterier.
I exemplet ovan är det första kriteriet summan av alla värden i B3: B10 där datum i A3: A10 är större än eller lika med datum i D3. D3 innehåller 1-jan. Detta översätter också.
= SUMIFIER (B3: B10, A3: A10, "> =" & “1-jan-2019”, A3: A10, "<=" EOMONTH (D3,0))
Nästa kriterier är summan endast om datum in A3: A10 är mindre än eller lika med EOMONTH (D3,0). EOMONTH -funktionen returnerar bara serienumret för det sista datumet i månaden. Slutligen översätts formeln också.
= SUMIFIER (B3: B10, A3: A10, "> = 1-jan-2019”, A3: A10, "<= 31-jan-2019”)
Därför får vi summan per månad i excel.
Fördelen med denna metod är att du kan justera startdatumet för att summera värdena.
Om dina datum har andra år än det är bäst att använda pivottabeller. Pivottabeller kan hjälpa dig att separera data i årliga, kvartalsvisa och månatliga format är enkelt.
Så ja killar, så här kan du summera värden per månad. Båda sätten har sina egna specialiteter. Välj vilket sätt du vill.
Om du har några frågor angående den här artikeln eller andra Excel- och VBA -relaterade frågor är kommentarsfältet öppet för dig.
Relatera artiklar:
Hur man använder SUMIF -funktionen i Excel
SUMIFIER med datum i Excel
SUMIF med icke-tomma celler
Hur man använder SUMIFS -funktionen i Excel
SUMMER med OCH-ELLER-logik
Populära artiklar
50 Excel -genväg för att öka din produktivitet: Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.
Hur man använder than VLOOKUP -funktion i Excel: Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
Så här använder du funktionen COUNTIF i Excel: Räkna värden med villkor med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel: Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.