Summere måned og kvartal ut fra en datorekke med SummerProdukt

Jeg er sikkert ikke den eneste som har benyttet en hjelpekolonne for å fiske ut måneden av en datorekke for så å summere med summer.hvis.sett. I dette eksemplet skal vi se på hvordan vi kan gjøre det uten å bruke en hjelpekolonne ved å benytte SummerProdukt. Og vi skal se på noen andre lure triks.

(Artikkelen fortsetter under videoruten)

SUMMERPRODUKT – Summere en datorekke per måned

I eksemplet har vi datoene i A-kolonnen og beløpene i B-kolonnen

Formelen i E2 er

=SUMMERPRODUKT( (MÅNED($A$2:$A$1001)=MÅNED(D2&1)) * ($B$2:$B$1001) )

Vi deler den opp litt

MÅNED($A$2:$A$1001)

SummerProdukt har den egenskapen at vi kan bruke (enkelte) funksjoner på matrisen vi skal summere, i dette tilfellet Måned(). Vi er ikke interessert i datoen i området A2-A1001, men måneden de representerer. Det Summerprodukt gjør, er å sjekke dette området mot den neste biten av formelen:

=MÅNED(D2&1)

… og lage en serie med ettall for de linjene som tilfredsstiller betingelsen, og nuller for de som ikke gjør det. (Se lenger ned for en forklaring på D2&1)

Så ganges resultatet, altså enten 1 eller 0 med tallet i B-kolonnen på samme rad, og hele stasen summeres deretter.

SUMMERPRODUKT – Summere en datorekke per kvartal

I H2 bruker vi formelen

=SUMMERPRODUKT((AVRUND.OPP(MÅNED($A$2:$A$1001)/3;0)=G2)* ($B$2:$B$1001) )

Vi finner måneden som datoene representerer, deler den på tre og avrunder opp uten desimaler, og ønsker at resultatet skal stemme med kvartalnummeret i G2. Deretter ganger vi resultatet med verdiene i B2-B1001

Formel som finner kvartal ut fra måned.

Hvis vi har måneden i A2, kan vi finne kvartalet med formelen

=AVRUND.OPP(A2/3;0)

Altså måneden delt på tre avrundet opp uten desimaler. Du kan teste formelen her ved å endre måned:

Se også: Summer.Hvis.Sett – litt i dybden

Hvordan lure ut nummeret på måneden ut fra navnet på måneden

Funksjonen Måned() finner nummeret på måneden (1-12) ut fra en dato.

=Måned("01.01.2017")

vil returnere 1

Hvis vi forsøker oss med

=Måned("Desember")

får vi en feil…

Men hvis vi forsøker oss med

=Måned("Desember1")

tror Excel at vi har skrevet en dato, og returnerer 12 🙂

Hvis du har måneden din i D2, kan du altså fiske ut nummeret på måneden ved å skrive

=Måned(D2&1)

Hihi.

Funksjonen UKENR() kan ikke benyttes på matriser (f.eks A2-A1001), og fungerer ikke sammen med SUMMERPRODUKT

Last ned regnearket her:  OmsetningPerMaanedogKvartal.xlsx (44 downloads)