Summerprodukt() med ELLER

Summerprodukt() er en svært kraftfull funksjon i Excel. Funksjonen sjekker området du angir og returnerer antallet celler i området ditt som stemmer med argumentene dine. Du må alltid ha med minst to argumenter.

=SUMMERPRODUKT((Område1=x)*(Område2=y))

Område1 og Område2 må ha lik størrelse. Hvis Område1 er A2:A101, kan Område2 være B2:B101, men ikke B2:B50 – da feiler formelen. Parentesene er viktige. Betingelsene du setter opp må stå innenfor parenteser, slik at * alltid er mellom parenteser )*(

Prinsippet

Vi tar et lite eksempel hvor vi trekker ut menn og kvinner over 50:

Jeg markerer det første argumentet og trykker F9 for å se resultatet av formelen. Den skal plukke ut radene med ‘Mann’ i kolonne A, og resultatet blir de syv verdiene SANN.SANN.USANN.USANN.SANN.SANN,USANN

Så gjør jeg det samme med det andre argumentet. Her skal formelen plukke ut radene som har verdier over 50. Resultatet blir USANN.SANN.USANN.USANN.USANN.SANN.SANN

Setter vi resultatene i en tabell, ser vi at det er to tilfeller hvor både ‘Mann’ og >50 er SANN, så resultatet av formelen blir to.

Så over til moroa – SUMMERPRODUKT med ELLER

Hvor mange ansatte har vi på de forskjellige avdelingene fra måned til måned?

I Excelboka har vi to faner, en med ansatte og en hvor vi teller hvor mange vi har i de forskjellige funksjonen på de forskjellige avdelingene.

Det vi må sjekke er

• Hvilken avdeling hører de til?
• Hvilken funksjon har de?
• Har de begynt på jobb denne måneden?
• Har de sluttet denne måneden?

Vi tar for oss formelen i C2:

Formelen i C2:

=HVIS($A2=0;0;SUMMERPRODUKT((Ansatte!$D$2:$D$101=$A2)*(Ansatte!$E$2:$E$101=$B2)*(Ansatte!$B$2:$B$101<C$1)*((Ansatte!$C$2:$C$101=0)+(Ansatte!$C$2:$C$101>C$1))))

I D-kolonnen i Ansatte har vi avdeling, og i E-kolonnen har vi funksjonen, og her teller vi antallet som tilhører Avdeling Administrasjon (A2) og Funksjon (B2):

(Ansatte!$D$2:$D$101=$A2)*(Ansatte!$E$2:$E$101=$B2)

(Lurer du på dollartegnene, anbefaler jeg at du ser på denne artikkelen )

Så har vi dette med ELLER, da. Grunnen til at vi trenger ELLER, er at cellen for sluttdato kan være tom ELLER inneholde en dato. En tom celle i Excel har verdien 0, og på datoformat blir datoen 00.01.1900. Sjekker vi bare om sluttdatoen er mindre enn datoen i C-kolonnen, vil alle ansatte som har en tom celle bli oppfattet som sluttet tidlig på 1900-tallet. De vi skal ha med i oversikten, må enten ha 0 i sluttet-dato, eller en sluttdato som er frem i tid. Formelen vi jobber med her i C2, må ha sluttdato 0 eller en dato som er større enn 31.01.24 (C1).

Når vi skal bruke ELLER, bruker vi + mellom argumentene. Her må du passe på at de to ELLER-argumentene er omsluttet av parenteser.
*((Område1=x)+(Område2=y)) er korrekt, mens *(Område1=x)+(Område2=y) blir bare tøys:

((Ansatte!$C$2:$C$101=0)+(Ansatte!$C$2:$C$101>C$1))

Så er det den første delen av formelen:

=HVIS($A2=0;0;SUMMERPRODUKT(....

Vi undersøker radene fra 2 til 101, og blant disse er det mange tomme rader. Disse radene har Avdeling 0 og Funksjon 0, og i eksemplet returneres det 60 tommer rader hvis vi ikke har med denne HVIS’en, som sørger for at resultatet blir 0 for alle rader som har Avdeling 0.

Andre formler å sjekke ut i Excelboka

• Formelen i A2 er ganske morsom
• Formlene i D1, E1, F1 osv som gir siste dag i hver måned er også litt morsom

Hvis du har lest helt hit, skulle du nesten hatt premie.

Last ned Excelboken her: