Summer.Hvis.Sett – litt i dybden

with Ingen kommentarer
Hyggelig om du deler:
Share on Facebook
Facebook
Share on Google+
Google+
Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin

Summer.Hvis.Sett

Jeg har fått mange henvendelser om Summer.Hvis.Sett, hvor ønsket er å få summert verdier hvor f.eks verdien i kolonne A er enten «Per» eller «Pål». Detter er fullt mulig, men ikke rett ut av boksen.

Slik virker Summer.Hvis.Sett

summer.hvis.sett

Summer.Hvis.Sett summerer et område når ALLE kriterier er oppfylt, altså når kriterium 1 OG kriterium 2 er oppfylt. I eksemplet over summeres alle tall i kolonne C hvor kolonne A og B er henholdsvis «Akershus» og «Amalie» på samme linje.

Du kan sette inn så mange kriterier du ønsker i Summer.Hvis.Sett

Utfordringen

Henvendelsene jeg har fått har samme type utfordring. De ønsker summen for både Akerhus og Østfold, og prøver seg frem med et kriterium til:

=SUMMER.HVIS.SETT(C:C;A:A;"Akershus";A:A;"Østfold";B:B;"Amalie")

 

Dette funker ikke. Excel forsøker å finne en celle i A-kolonnen som både er lik «Akershus» OG «Østfold» samtidig, og det finnes jo ikke. Resultatet er at formelen returnerer null.

… og løsningen

Det betyr ikke at vi har tapt. Vi kan bruke matrisefunksjonaliteten i Excel for å få formelen til å sjekke på begge verdier.

Jeg pusser litt på formelen over og skriver den slik:

=SUMMER.HVIS.SETT(C:C;A:A;{"Akershus"\"Østfold"};B:B;"Amalie")

 

Matrisen {«Akershus»\»Østfold»} forteller Excel at vi ønsker at formelen skal utføres en gang for verdien «Akershus» og en gang for «Østfold». Men formelen returnerer fortsatt bare resultatet for den første beregningen. Jeg må summere resultatet av begge beregningene:

=SUMMER(SUMMER.HVIS.SETT(C:C;A:A;{"Akershus"\"Østfold"};B:B;"Amalie"))

 

Vipps, så har vi fått summen av alle beregningene.

Det er upraktisk å skrive inn verdiene manuelt, og normalt vil vi henvise til et område i stedet for å skrive inn {«Akershus»\»Østfold»}, f.eks E2:E10 og kanskje teste på flere navn fra et annet område, F2:F10

=SUMMER(SUMMER.HVIS.SETT(C:C;A:A;E2:E10;B:B;F2:F10))

Trøbbel igjen. Formelen returnerer 0. I det forrige eksemplet skriver vi inn parantesene {} manuelt, slik at Excel får informasjon om at dette skal behandles som en matrise. Når vi setter inn referanser til områder, må vi avslutte formelen med CTRL+SHIFT+ENTER for å gjøre dette til en matriseformel. Du vil se at formelen får dette formatet:

={SUMMER(SUMMER.HVIS.SETT(C:C;A:A;E2:E10;B:B;F2:F10))}

 

… og at den nå returnerer riktig sum.

Matriseformler – utrolig kraftfullt – og litt skummelt…

Matriseformler gir en rå funksjonalitet. Det eneste som er skummelt er at det er lett å glemme å trykke CTRL+SHIFT+ENTER når vi har vært inne og redigert formelen. I og med at formelen ikke viser noen feil, men gir resultatet 0, kan det være et pes å finne feilen.

Last ned regnearket her:  Summer-Hvis-Sett-ELLER.xlsx (246 downloads)