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

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.

Les også artikkelen Summerprodukt med ELLER

(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 (178 downloads )