Tämä opetusohjelma sisältää yksityiskohtaisen esittelyn SUMIF- ja SUMIFS-funktioiden käytöstä Google Sheetsissa kaavoineen ja esimerkkeineen.
SUMIF on yksi Google Sheetsin matemaattisista funktioista, jota käytetään solujen ehdolliseen summaamiseen. Periaatteessa SUMIF-funktio etsii tietyn ehdon solualueelta ja laskee sitten yhteen arvot, jotka täyttävät annetun ehdon.
Sinulla on esimerkiksi luettelo kuluista Google-taulukoissa ja haluat vain laskea yhteen ne kulut, jotka ylittävät tietyn enimmäisarvon. Tai sinulla on luettelo tilaustuotteista ja niitä vastaavista määristä ja haluat tietää vain tietyn tuotteen tilaussumman. Siinä SUMIF-toiminto on hyödyllinen.
SUMIF:ää voidaan käyttää arvojen summaamiseen numeroehtojen, tekstiehtojen, päivämääräehtojen, yleismerkkien sekä tyhjien ja ei-tyhjien solujen perusteella. Google Sheetsissä on kaksi funktiota, jotka summaavat arvot kriteerien perusteella: SUMIF ja SUMIFS. SUMIF-toiminto summaa luvut yhden ehdon perusteella, kun taas SUMIFS summaa lukuja useiden ehtojen perusteella.
Tässä opetusohjelmassa selitämme, kuinka Google Sheetsin SUMIF- ja SUMIFS-funktioita käytetään summaamaan tietyt ehdot täyttäviä lukuja.
SUMIF-funktio Google Sheetsissa – Syntaksi ja argumentit
SUMIF-toiminto on vain SUM- ja IF-toimintojen yhdistelmä. IF-funktio skannaa tietyn ehdon solualueen ja sitten SUM-funktio summaa ehdon täyttäviä soluja vastaavat luvut.
SUMIF-funktion syntaksi:
SUMIF-funktion syntaksi Google Sheetsissa on seuraava:
=SUMMA(väli, ehdot, [summa_väli])Argumentit:
valikoima - Solualue, josta etsimme kriteerit täyttäviä soluja.
kriteeri – Kriteerit, jotka määrittävät, mitkä solut on lisättävä. Voit perustaa kriteerin numeroon, tekstimerkkijonoon, päivämäärään, soluviittaukseen, lausekkeeseen, loogiseen operaattoriin, jokerimerkkiin sekä muihin funktioihin.
summa_alue – Tämä väite on valinnainen. Se on tietoalue, jonka arvot summataan, jos vastaava välimerkintä vastaa ehtoa. Jos et sisällytä tätä argumenttia, "väli" summataan sen sijaan.
Katsotaanpa nyt, kuinka SUMIF-funktiota käytetään arvojen summaamiseen eri kriteereillä.
SUMIF-funktio numerokriteereillä
Voit summata tietyt ehdot täyttäviä lukuja solualueella käyttämällä jotakin seuraavista vertailuoperaattoreista kriteerien määrittämiseen.
- suurempi kuin (>)
- vähemmän kuin (<)
- suurempi tai yhtä suuri kuin (>=)
- pienempi tai yhtä suuri kuin (<=)
- yhtä suuri kuin (=)
- ei ole yhtä suuri kuin ()
Oletetaan, että sinulla on seuraava laskentataulukko ja olet kiinnostunut kokonaismyynnistä, joka on 1000 tai enemmän.
Näin pääset SUMIF-toimintoon:
Valitse ensin solu, jossa haluat summan tulosteen näkyvän (D3). Summaaksesi B2:B12:n luvut, jotka ovat suurempia tai yhtä suuria kuin 1000, kirjoita tämä kaava ja paina 'Enter':
=SUMMA(B2:B12,">=1000",B2:B12)Tässä esimerkkikaavassa vaihteluväli- ja summa_alue-argumentit (B2:B12) ovat samat, koska myyntinumeroita ja kriteerejä sovelletaan samalla alueella. Ja kirjoitimme numeron vertailuoperaattorin eteen ja laitoimme sen lainausmerkkeihin, koska kriteerit tulee aina laittaa kaksoislainausmerkkeihin soluviittausta lukuun ottamatta.
Kaava etsi numeroita, jotka ovat suurempia tai yhtä suuria kuin 1000, ja sitten laskettiin yhteen kaikki vastaavat arvot ja näytti tuloksen solussa D3.
Koska väli- ja summa_alue-argumentit ovat samat, voit saavuttaa saman tuloksen ilman kaavan summa_alue-argumentteja seuraavasti:
=SUMMA(B2:B12,">=1000")
Tai voit antaa soluviittauksen (D2), joka sisältää numeron numeroehtojen sijaan, ja liittää vertailuoperaattorin kyseiseen soluviittaukseen kriteeriargumentissa:
=SUMMA(B2:B12,">="&D2)Kuten näet, vertailuoperaattori on edelleen lainausmerkeissä ja operaattori ja soluviittaus on yhdistetty et-merkillä (&). Ja sinun ei tarvitse laittaa soluviittauksia lainausmerkkeihin.
merkintä: Kun viittaat kriteerit sisältävään soluun, varmista, että et jätä solun arvoon alku- tai lopputilaa. Jos arvossasi on tarpeetonta tilaa ennen tai jälkeen arvon viitatussa solussa, kaava palauttaa tuloksena "0".
Voit myös käyttää muita loogisia operaattoreita samalla tavalla ehtojen määrittämiseen kriteeriargumentissa. Esimerkiksi alle 500:n arvojen summaamiseksi:
=SUMMA(B2:B12,"<500")
Summa, jos luvut ovat yhtä suuret
Jos haluat lisätä numeroita, jotka ovat yhtä suuria kuin tietty luku, voit joko kirjoittaa vain luvun tai syöttää luvun, jossa on yhtäläisyysmerkki kriteeriargumentissa.
Esimerkiksi, jos haluat laskea yhteen vastaavat myyntimäärät (sarake B) määrille (sarake C), joiden arvot ovat 20, kokeile jotakin seuraavista kaavoista:
=SUMMA(C2:C12,"=20",B2:B12)
=SUMMA(C2:C12"20",B2:B12)
=SUMMA(C2:C12;E2;B2:B12)
Summaa sarakkeen B luvut, joiden määrä ei ole yhtä suuri kuin 20 sarakkeessa C, kokeile tätä kaavaa:
=SUMMA(C2:C12"20",B2:B12)
SUMIF-toiminto tekstikriteereillä
Jos haluat laskea yhteen numeroita solualueella (sarake tai rivi), jotka vastaavat soluja, joissa on tietty teksti, voit yksinkertaisesti sisällyttää kyseisen tekstin tai tekstin sisältävän solun SUMIF-kaavasi kriteeriargumenttiin. Huomaa, että tekstimerkkijono tulee aina olla lainausmerkkien (" ") sisällä.
Jos esimerkiksi haluat myynnin kokonaismäärän "Länsi" -alueella, voit käyttää alla olevaa kaavaa:
=SUMMA(C2:C13,"Länsi",B2:B13)Tässä kaavassa SUMIF-funktio etsii arvoa "Länsi" solualueelta C2:C13 ja laskee yhteen vastaavan myyntiarvon sarakkeessa B. Sen jälkeen näyttää tuloksen solussa E3.
Voit myös viitata tekstiä sisältävään soluun sen sijaan, että käyttäisit tekstiä kriteeriargumentissa:
=SUMMA(C2:C12;E2;B2:B12)
Otetaan nyt kaikkien alueiden kokonaistulot paitsi "Länsi". Tätä varten käytämme kaavassa ei ole yhtä suuri kuin operaattori ():
=SUMMA(C2:C12,""&E2,B2:B12)
SUMIF jokerikorteilla
Yllä olevassa menetelmässä SUMIF-funktio tekstikriteereillä tarkistaa alueen tarkasti määritettyä tekstiä vasten. Sitten se summaa numerot täsmällisen tekstin kanssa ja jättää huomioimatta kaikki muut luvut, mukaan lukien osittain täsmäytystekstijono. Summaaksesi numerot osittain vastaavilla tekstijonoilla, sinun on räätälöitävä yksi seuraavista jokerimerkeistä ehdoissasi:
?(kysymysmerkkiä) käytetään vastaamaan mitä tahansa yksittäistä merkkiä missä tahansa tekstijonossa.*(tähti) käytetään etsimään vastaavia sanoja minkä tahansa merkkijonon kanssa.~(Tilde) käytetään yhdistämään tekstit kysymysmerkillä (?) tai tähtimerkillä (*).
Tämä esimerkkilaskentataulukko tuotteille ja niiden määrille lasketaan yhteen jokerimerkeillä:
Tähti (*) Jokerimerkki
Jos esimerkiksi haluat laskea yhteen kaikkien Applen tuotteiden määrät, käytä tätä kaavaa:
=SUMMA(A2:A14"omena*",B2:B14)Tämä SUMIF-kaava löytää kaikki tuotteet, joiden alussa on sana "Apple" ja minkä tahansa määrän merkkejä sen jälkeen (merkitty *-merkillä). Kun vastaavuus on löydetty, se tekee yhteenvedon Määrä numerot, jotka vastaavat vastaavia tekstijonoja.
Kriteereissä on myös mahdollista käyttää useita yleismerkkejä. Voit myös syöttää jokerimerkkejä soluviittauksilla suoran tekstin sijaan.
Tätä varten jokerimerkit on suljettava lainausmerkeissä (" ") ja ketjutettava soluviittauksiin:
=SUMMA(A2:A14"*"&D2&"*",B2:B14)Tämä kaava laskee yhteen kaikkien niiden tuotteiden määrät, joissa on sana "Redmi", riippumatta siitä, missä merkkijonossa sana sijaitsee.
Kysymysmerkki (?) Jokerimerkki
Voit käyttää kysymysmerkkiä (?) jokerimerkkiä yhdistämään tekstimerkkijonoja mitä tahansa yksittäisiä merkkejä.
Jos esimerkiksi haluat löytää määriä kaikkia Xiaomi Redmi 9 -versioita, voit käyttää tätä kaavaa:
=SUMIF(A2:A14"Xiaomi Redmi 9?",B2:B14)Yllä oleva kaava etsii tekstijonoja, joissa on sana "Xiaomi Redmi 9" ja mitä tahansa yksittäisiä merkkejä ja summaa vastaavat Määrä numeroita.
Tilde (~) Jokerimerkki
Jos haluat yhdistää varsinaisen kysymysmerkin (?) tai tähtimerkin (*), lisää aaltoviiva (~) ennen yleismerkkiä kaavan ehtoosassa.
Lisää sarakkeen B määrät vastaavalla merkkijonolla, jonka lopussa on tähti, syötä seuraava kaava:
=SUMIF(A2:A14"Samsung Galaxy V~*",B2:B14)
Jos haluat lisätä sarakkeeseen B määriä, joissa on kysymysmerkki (?) saman rivin sarakkeessa A, kokeile seuraavaa kaavaa:
=SUMMA(A2:A14,"~?",B2:B14)
SUMIF-toiminto päivämäärän kriteereillä
SUMIF-toiminto voi myös auttaa sinua ehdollisesti summaamaan arvot päivämääräkriteerien perusteella – esimerkiksi numerot, jotka vastaavat tiettyä päivämäärää, ennen päivämäärää tai päivämäärän jälkeen. Voit myös käyttää mitä tahansa vertailuoperaattoria, jolla on päivämääräarvo, luodaksesi päivämääräehtoja lukujen summaamista varten.
Päivämäärä on syötettävä Google-taulukoiden tukemassa päivämäärämuodossa tai soluviittauksena, joka sisältää päivämäärän, tai käyttämällä päivämääräfunktiota, kuten PÄIVÄYS() tai TÄNÄÄN().
Käytämme tätä esimerkkilaskentataulukkoa näyttääksemme, kuinka SUMIF-funktio päivämääräehtojen kanssa toimii:
Oletetaan, että haluat laskea yhteen yllä olevassa tietojoukossa (<=) 29.11.2019 tai sitä ennen tapahtuneet myyntimäärät, voit lisätä kyseiset myyntiluvut SUMIF-funktiolla jollakin seuraavista tavoista:
=SUMIF(C2:C13,"<=29.11.2019",B2:B13)Yllä oleva kaava tarkistaa jokaisen solun C2:sta C13:een ja vastaa vain niitä soluja, jotka sisältävät päivämäärät 29.11.2019 (29.11.2019) tai sitä ennen. Ja sitten summaa myyntimäärän, joka vastaa solualueen B2:B13 vastaavia soluja, ja näyttää tuloksen soluissa E3.
Päivämäärä voidaan syöttää kaavaan missä tahansa Google Sheetsin tunnistamassa muodossa, kuten "29.11.2019", "29.11.2019" tai "29.11.2019" jne. Muista päivämääräarvo ja operaattorin on kirjoitetaan aina lainausmerkkeihin.
Voit myös käyttää DATE()-funktiota ehdoissa suoran päivämääräarvon sijaan:
=SUMMA(C2:C13,"<="&PÄIVÄYS(2019;11;29),B2:B13)
Tai voit käyttää soluviittausta päivämäärän sijaan kaavan kriteeriosassa:
=SUMMA(C2:C13,"<="&E2,B2:B13)
Jos haluat liittää myyntimäärät yhteen tämän päivän päivämäärän perusteella, voit käyttää kriteeriargumentissa TODAY()-funktiota.
Voit esimerkiksi laskea yhteen kaikki tämän päivän myyntimäärät käyttämällä tätä kaavaa:
=SUMMA(C2:C13,TÄNÄÄN(),B2:B13)
SUMIF-toiminto tyhjillä tai ei-tyhjillä soluilla
Joskus saatat joutua laskemaan yhteen solualueen luvut, joissa on tyhjiä tai ei-tyhjiä soluja samalla rivillä. Tällaisissa tapauksissa voit käyttää SUMIF-funktiota arvojen summaamiseen kriteerien perusteella, ovatko solut tyhjiä vai eivät.
Summa jos tyhjä
Google Sheetsissä on kaksi ehtoa tyhjien solujen löytämiseen: "" tai "=".
Jos esimerkiksi haluat laskea yhteen kaikki myyntimäärät, jotka sisältävät nollapituisia merkkijonoja (näyttää visuaalisesti tyhjältä) sarakkeessa C, käytä lainausmerkkejä ilman välilyöntiä kaavassa:
=SUMMA(C2:C13,"",B2:B13)
Jos haluat laskea yhteen kaikki sarakkeen B myyntimäärät täydellisillä tyhjillä soluilla sarakkeessa C, sisällytä "=" kriteeriksi:
=SUMMA(C2:C13,"=",B2:B13)
Summa, jos ei ole tyhjä:
Jos haluat summata solut, jotka sisältävät minkä tahansa arvon (ei tyhjiä), voit käyttää "" kaavan kriteerinä:
Jos esimerkiksi haluat saada myynnin kokonaismäärän millä tahansa päivämäärillä, käytä tätä kaavaa:
=SUMMA(C2:C13,"",B2:B13)
SUMIF Perustuu useisiin kriteereihin OR-logiikalla
Kuten olemme toistaiseksi nähneet, SUMIF-funktio on suunniteltu summaamaan lukuja vain yhden kriteerin perusteella, mutta arvot on mahdollista summata useiden kriteerien perusteella Google Sheetsin SUMIF-funktiolla. Se voidaan tehdä yhdistämällä useampi kuin yksi SUMIF-funktio yhteen kaavaan TAI-logiikalla.
Jos esimerkiksi haluat laskea yhteen myyntimäärän "länsi"- tai "etelä"-alueella (OR-logiikka) määritetyllä alueella (B2:B13), käytä tätä kaavaa:
=SUMMA(C2:C13,"Länsi",B2:B13)+SUMMA(C2:C13,"Etelä",B2:B13)Tämä kaava summaa solut, kun vähintään yksi ehdoista on TOSI. Siksi se tunnetaan nimellä "OR-logiikka". Se myös laskee arvot yhteen, kun kaikki ehdot täyttyvät.
Kaavan ensimmäinen osa tarkistaa alueen C2:C13 tekstille 'Länsi' ja summaa alueen B2:B13 arvot, kun vastaavuus täyttyy. Sekuntiosa tarkistaa tekstiarvon ”Etelä” samalla alueella C2:C13 ja summaa sitten arvot vastaavan tekstin kanssa samalla summa_alueella B2:B13. Sitten molemmat summat lasketaan yhteen ja näytetään solussa E3.
Jos vain yksi kriteeri täyttyy, se palauttaa vain tämän summaarvon.
Voit myös käyttää useita kriteerejä yhden tai kahden sijaan. Ja jos käytät useita ehtoja, on parempi käyttää soluviittausta kriteerinä sen sijaan, että kirjoittaisit suoran arvon kaavaan.
=SUMMA(C2:C13,E2,B2:B13)+SUMMA(C2:C13,E3,B2:B13)+SUMMA(C2:C13,E4,B2:B13)
SUMIF ja TAI-logiikka lisää arvoja, kun vähintään yksi määritetyistä ehdoista täyttyy. Mutta jos haluat summata arvot vain, kun kaikki määritetyt ehdot täyttyvät, sinun on käytettävä sen uutta sisarusfunktiota SUMIFS().
SUMIFS-toiminto Google Sheetsissä (useita ehtoja)
Kun käytät SUMIF-funktiota arvojen summaamiseen useiden kriteerien perusteella, kaavasta voi tulla liian pitkä ja monimutkainen, ja olet altis virheille. Lisäksi SUMIF antaa sinun summata arvot vain yhdeltä alueelta ja kun jokin ehdoista on TOSI. Siellä SUMIFS-toiminto tulee käyttöön.
SUMIFS-toiminto auttaa sinua summaamaan arvot useiden hakukriteerien perusteella yhdellä tai useammalla alueella. Ja se toimii JA-logiikalla, mikä tarkoittaa, että se voi summata arvoja vain, kun kaikki annetut ehdot täyttyvät. Vaikka yksi ehto olisi epätosi, se palauttaa tuloksena arvon 0.
SUMIFS-funktion syntaksi ja argumentit
SUMIFS-funktion syntaksi on seuraava:
=SUMIFS(summa_alue, ehtojen_väli1, ehto1, [ehdot_väli2, ...], [ehto2, ...])Missä,
- summa_range – Solualue, joka sisältää arvot, jotka haluat summata, kun kaikki ehdot täyttyvät.
- kriteerit_väli1 – Se on solualue, josta tarkistat ehdot1.
- kriteerit 1 - Se on ehto, joka sinun on tarkistettava kriteerin_väli1:tä vastaan.
- kriteria_range2, kriteeri2, …– Arvioitavat lisäalueet ja kriteerit. Ja voit lisätä kaavaan lisää alueita ja ehtoja.
Käytämme seuraavan kuvakaappauksen tietojoukkoa osoittamaan, kuinka SUMIFS-toiminto toimii eri kriteereillä.
SUMIFS tekstin ehdoilla
Voit summata arvot kahden eri tekstiehdon perusteella eri alueilla. Oletetaan esimerkiksi, että haluat saada selville toimitetun Telttatuotteen kokonaismyyntimäärän. Käytä tätä varten tätä kaavaa:
=SUMIFS(D2:D13,A2:A13"Teltta",C2:C13"toimitettu")Tässä kaavassa meillä on kaksi kriteeriä: "Teltta" ja "Toimitettu". SUMIFS-toiminto tarkistaa kohteen 'Tent' (kriteerit1) alueella A2:A13 (kriteerin_alue1) ja tilan 'Delivered' (kriteeri2) alueella C2:C13 (kriteerin_alue2). Kun molemmat ehdot täyttyvät, se summaa vastaavan arvon solualueella D2:D13 (summa_alue).
SUMIFS numerokriteereillä ja loogisilla operaattoreilla
Voit käyttää ehdollisia operaattoreita luodaksesi ehtoja numeroilla SUMIFS-funktiolle.
Käytä tätä kaavaa saadaksesi selville yli viiden tuotteen kokonaismyynnin Kalifornian osavaltiossa:
=SUMIFS(E2:E13,D2:D13,">5",B2:B13"CA")Tässä kaavassa on kaksi ehtoa: ">5" ja "CA".
Tämä kaava tarkistaa suuret (Qty) suuremmat kuin 5 alueella D2:D13 ja tarkistaa tilan 'CA' alueella B2:B13. Ja kun molemmat ehdot täyttyvät (eli ne ovat samalla rivillä), se summaa summan E2:E13:ssa.
SUMIFS päivämäärän kriteereillä
SUMIFS-toiminnon avulla voit myös tarkistaa useita ehtoja samalla alueella sekä eri alueilla.
Oletetaan, että haluat tarkistaa toimitettujen tuotteiden kokonaismyyntimäärän 31.5.2021 jälkeen ja ennen 6.10.2021 päivämäärää, ja käytä sitten tätä kaavaa:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)Yllä olevassa kaavassa on kolme ehtoa: 31/5/2021, 10/5/2021 ja Toimitettu. Sen sijaan, että käyttäisimme suoria päivämäärä- ja tekstiarvoja, viittasimme soluihin, jotka sisältävät kyseiset kriteerit.
Kaava tarkistaa päivämäärät 31.5.2021 (G1) jälkeen ja päivämäärät ennen 10.6.2021 (G2) samalla alueella D2:D13 ja tarkistaa tilan Toimitettu näiden kahden päivämäärän välillä. Summaa sitten vastaava summa välillä E2:E13.
SUMIFS tyhjillä ja ei-tyhjillä soluilla
Joskus saatat haluta löytää arvojen summan, kun vastaava solu on tyhjä tai ei. Voit tehdä tämän käyttämällä yhtä kolmesta aiemmin keskustelemastamme kriteeristä: "=", "" ja "".
Jos esimerkiksi haluat laskea yhteen vain niiden 'Teltta'-tuotemäärän, joille toimituspäivää ei ole vielä vahvistettu (tyhjät solut), voit käyttää "="-ehtoja:
=SUMIFS(D2:D13,A2:A13,"Teltta",C2:C13,"=")Kaava etsii sarakkeesta A 'Teltta' -kohteen (kriteeri1) ja vastaavat tyhjät solut (kriteerit2) sarakkeesta C ja summaa sitten vastaavan määrän sarakkeessa D. "=" tarkoittaa täysin tyhjää solua.
Käytä "" kriteerinä saadaksesi selville 'Telt'-tuotteiden summan, joille toimituspäivä on vahvistettu (ei tyhjiä soluja):
=SUMIFS(D2:D13,A2:A13"Teltta",C2:C13"")Vaihdoimme vain "=":n "":ksi tässä kaavassa. Se etsii sarakkeen C ei-tyhjiä soluja sisältävien Telttakohteiden summan.
SUMIFS OR-logiikalla
Koska SUMIFS-funktio toimii JA-logiikalla, se laskee vain, kun kaikki ehdot täyttyvät. Mutta entä jos haluat summata arvon useiden kriteerien perusteella, kun jokin ehdoista täyttyy. Temppu on käyttää useita SUMIFS-toimintoja.
Jos esimerkiksi haluat laskea yhteen joko "Pyörätelineen" TAI "Reppun" myyntisumman, kun niiden tila on "Tilattu", kokeile tätä kaavaa:
=SUMIFS(D2:D13,A2:A13"Pyöräteline",C2:C13"Tilattu") +SUMIFS(D2:D13,A2:A13"Reppu",C2:C13"Tilattu")Ensimmäinen SUMIFS-toiminto tarkistaa kaksi kriteeriä "Pyöräteline" ja "Tilattu" ja laskee summan arvot sarakkeessa D. Sitten toinen SUMIFS tarkistaa kaksi kriteeriä "Reppu" ja "Tilattu" ja laskee summan arvot sarakkeessa D. Ja sitten , molemmat summat lasketaan yhteen ja näytetään F3:ssa. Yksinkertaisesti sanottuna tämä kaava laskee, kun joko "Pyöräteline" tai "Reppu" tilataan.
Siinä on kaikki mitä sinun tulee tietää Google Sheetsin SUMIF- ja SUMIFS-toiminnoista.