Denne vejledning viser, hvordan du bruger Eksklel SUMIF og SUMIFS Functioner i Excel og Google Sheets for at opsummere data, der opfylder visse kriterier.
SUMIF Funktionsoversigt
Du kan bruge SUMIF -funktionen i Excel til at summe af celler, der indeholder en bestemt værdi, summe celler, der er større end eller lig med en værdi osv.
(Bemærk hvordan formelindgangene vises)
SUMIF -funktionssyntaks og argumenter:
| 1 | = SUMIF (område, kriterier, [sum_range]) |
rækkevidde - Celleområdet, som du vil anvende kriterierne mod.
kriterier - De kriterier, der bruges til at bestemme, hvilke celler der skal tilføjes.
sum_range - [valgfrit] Cellerne, der skal tilføjes sammen. Hvis sum_range udelades, tilføjes cellerne i området i stedet.
Hvad er SUMIF -funktionen?
SUMIF -funktionen er en af de ældre funktioner, der bruges i regneark. Det bruges til at scanne gennem en række celler, der kontrollerer et specifikt kriterium og derefter tilføje værdier i et område, der svarer til disse værdier. Den oprindelige SUMIF -funktion var begrænset til kun et kriterium. Efter 2007 blev SUMIFS -funktionen oprettet, som tillader en lang række kriterier. Det meste af den generelle brug forbliver den samme mellem de to, men der er nogle kritiske forskelle i syntaksen, som vi vil diskutere i hele denne artikel.
Hvis du ikke allerede har gjort det, kan du gennemgå meget af den lignende struktur og eksempler i COUNTIFS -artiklen.
Grundlæggende eksempel
Lad os overveje denne liste over registrerede salg, og vi vil gerne vide den samlede indkomst.
Fordi vi havde en udgift, den negative værdi, kan vi ikke bare lave en grundsum. I stedet vil vi kun opsummere de værdier, der er større end 0. "Større end 0" er, hvad der vil være vores kriterier i en SUMIF -funktion. Vores formel til at angive dette er
| 1 | = SUMIF (A2: A7, "> 0") |
Eksempel på to kolonner
Mens den originale SUMIF -funktion var designet til at lade dig anvende et kriterium på det talinterval, du vil summere, skal du meget af tiden anvende et eller flere kriterier på andre kolonner. Lad os overveje denne tabel:
Hvis vi nu bruger den originale SUMIF -funktion til at finde ud af, hvor mange bananer vi har (angivet i celle D1), skal vi angive det område, vi vil sum som det sidste argument, og sådan ville vores formel være
| 1 | = SUMIF (A2: A7, D1, B2: B7) |
Da programmerere til sidst indså, at brugerne ønskede at give mere end ét kriterium, blev SUMIFS -funktionen oprettet. For at oprette en struktur, der fungerer for et vilkårligt antal kriterier, kræver SUMIFS, at sumintervallet er angivet først. I vores eksempel betyder dette, at formlen skal være
| 1 | = SUMIFS (B2: B7, A2: A7, D1) |
BEMÆRK: Disse to formler får det samme resultat og kan ligne hinanden, så vær meget opmærksom på, hvilken funktion der bruges til at sikre, at du angiver alle argumenterne i den korrekte rækkefølge.
Arbejde med datoer, flere kriterier
Når du arbejder med datoer i et regneark, mens det er muligt at indtaste datoen direkte i formlen, er det bedste fremgangsmåde at have datoen i en celle, så du bare kan referere til cellen i en formel. For eksempel hjælper dette computeren med at vide, at du vil bruge datoen 27.5.2020 og ikke tallet 5 divideret med 27 divideret med 2022.
Lad os se på vores næste tabel, der registrerer antallet af besøgende på et websted hver anden uge.
Vi kan angive start- og slutpunkter for det område, vi vil se på i D2 og E2. Vores formel til at summere antallet af besøgende i dette område kan være:
| 1 | = SUMIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2) |
Bemærk, hvordan vi var i stand til at sammenkoble sammenligningerne af “=” med cellereferencerne for at oprette kriterierne. Selvom begge kriterier blev anvendt på det samme område af celler (A2: A7), skal du skrive området to gange ud, én gang pr. Kriterium.
Flere kolonner
Når du bruger flere kriterier, kan du anvende dem på det samme område, som vi gjorde med tidligere eksempel, eller du kan anvende dem på forskellige intervaller. Lad os kombinere vores eksempeldata i denne tabel:
Vi har opsat nogle celler, så brugeren kan indtaste det, han vil søge efter i cellerne E2 til G2. Vi har derfor brug for en formel, der summerer det samlede antal æbler, der blev plukket i februar. Vores formel ser sådan ud:
| 1 | = SUMIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2) |
SUMIFS med OR -logik
Indtil dette tidspunkt har de eksempler, vi har brugt, alle været OG -baseret sammenligning, hvor vi leder efter rækker, der opfylder alle vores kriterier. Nu overvejer vi sagen, når du vil søge efter muligheden for, at en række opfylder et eller andet kriterium.
Lad os se på denne salgsliste:
Vi vil gerne lægge det samlede salg sammen for både Adam og Bob. For at gøre dette har du et par muligheder. Det enkleste er at tilføje to SUMIFS sammen, sådan:
| 1 | = SUMIFS (B2: B7, A2: A7, "Adam")+SUMIFS (B2: B7, A2: A7, "Bob") |
Her har vi fået computeren til at beregne vores individuelle scores, og derefter tilføjer vi dem sammen.
Vores næste mulighed er god til, når du har flere kriterier, således at du ikke ønsker at skulle omskrive hele formlen gentagne gange. I den foregående formel fortalte vi manuelt computeren at tilføje to forskellige SUMIFS sammen. Du kan dog også gøre dette ved at skrive dine kriterier inde i en matrix, sådan her:
| 1 | = SUM (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"})) |
Se hvordan matrixen er opbygget inde i de krøllede beslag. Når computeren evaluerer denne formel, ved den, at vi vil beregne en SUMIFS -funktion for hvert element i vores array og dermed oprette en matrix af tal. Den ydre SUM -funktion tager derefter den række tal og gør den til et enkelt tal. Når man går gennem formelevalueringen, ser det sådan ud:
| 123 | = SUM (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"}))= SUM (27401, 43470)= 70871 |
Vi får det samme resultat, men vi var i stand til at skrive formlen lidt mere kortfattet ud.
Beskæftiger sig med emner
Nogle gange vil dit datasæt have tomme celler, som du enten skal finde eller undgå. Opsætning af kriterierne for disse kan være lidt vanskelig, så lad os se på et andet eksempel.
Bemærk, at celle A3 virkelig er tom, mens celle A5 har en formel, der returnerer en nul-længde streng med "". Hvis vi vil finde den samlede sum af virkelig tomme celler, ville vi bruge et kriterium "=", og vores formel ville se sådan ud:
| 1 | = SUMIFS (B2: B7, A2: A7, "=") |
På den anden side, hvis vi ønsker at få summen for alle celler, der visuelt ser tomme ud, ændrer vi kriterierne til at være "", og formlen ligner
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Lad os vende det rundt: hvad nu hvis du vil finde summen af ikke-tomme celler? Desværre vil det nuværende design ikke lade dig undgå nullængden. Du kan bruge et kriterium "", men som du kan se i eksemplet, indeholder det stadig værdien fra række 5.
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Hvis du ikke skal tælle celler, der indeholder strenge med nul længde, vil du overveje at bruge LEN -funktionen inde i et SUMPRODUCT
SUMIF i Google Sheets
SUMIF -funktionen fungerer nøjagtig det samme i Google Sheets som i Excel:
