Excel-ohjeet
SUMIFS: Laske yhteen vain ne luvut, joilla on merkitystä
Tarvitsetko yhteissumman yhdelle henkilölle, kuukaudelle tai tuotteelle? SUMIFS laskee yhteen vain ne rivit, jotka vastaavat pyyntöäsi, ilman suodatusta ja laskinta.
Oletko koskaan laskenut lukuja yhteen käsin?
Kuvittele myyntilista. Osa myynneistä on Annan, osa Benin, osa tehtiin pohjoisessa ja osa etelässä. Pomosi kysyy: "Paljonko Anna myi pohjoisessa?"
Voisit suodattaa listan, valita luvut ja lukea summan ruudun alareunasta. Sitten pomo kysyy Benistä, ja aloitat alusta. Ja taas uudestaan.
SUMIFS hoitaa tämän yhdellä kaavalla. Vaihda nimi, ja summa päivittyy itsestään.
Nimi kuulostaa tekniseltä, mutta sen muistaa helposti: SUM tarkoittaa yhteenlaskua ja IFS tarkoittaa "jos nämä ehdot täyttyvät".
Onko SUMIFSistä hyötyä minulle?
Todennäköisesti, jos joudut joskus vastaamaan tällaisiin kysymyksiin:
- Paljonko käytimme ruokaan maaliskuussa?
- Mikä on yhden myyjän kokonaismyynti?
- Montako tuntia yksi henkilö työskenteli yhdessä projektissa?
- Mikä on yhden asiakkaan kaikkien maksamattomien laskujen summa?
Jos kysymyksessä on sana "yhteensä" ja sen jälkeen "jollekin", "jossakin" tai "joltakin", SUMIFS on todennäköisesti vastaus.
Katso, miten se toimii
Tässä on pieni myyntilista. Sarakkeessa A on myyjä, sarakkeessa B alue ja sarakkeessa C myyty summa.
| A | B | C | E | F | G | ||
|---|---|---|---|---|---|---|---|
| 1 | Myyjä | Alue | Summa | Myyjä | Alue | Yhteensä | |
| 2 | Anna | Pohjoinen | 200 | Anna | Pohjoinen | 300 | |
| 3 | Ben | Eteläinen | 150 | ||||
| 4 | Anna | Eteläinen | 300 | ||||
| 5 | Cara | Pohjoinen | 250 | ||||
| 6 | Anna | Pohjoinen | 100 | ||||
| 7 | Ben | Pohjoinen | 400 |
Soluun G2 kirjoitamme yhden kaavan. Excel etsii rivejä, joilla myyjä on Anna ja alue on Pohjoinen (oranssit solut E2 ja F2). Vain rivit 2 ja 6 täsmäävät (siniset solut), joten Excel laskee yhteen niiden summat (vihreät solut): 200 + 100 = 300.
Huomaa, että Annan 300 etelässä ei tule mukaan, eikä Benin 400 pohjoisessa. Rivin on täytettävä kaikki ehdot, jotta se lasketaan mukaan.
Mitä pyydät Rivit, jotka täsmäävät Luvut, jotka lasketaan yhteen
Kaava askel askeleelta
Tässä on kaava, joka teki työn:
=SUMMA.JOS.JOUKKO( C2:C7; A2:A7; E2; B2:B7; F2 )
Lue se vasemmalta oikealle kolmena kysymyksenä. Toinen ja kolmas kysymys koostuvat kustakin tarkistettavasta sarakkeesta ja ehdosta, jota vasten sitä verrataan:
Mitä lasken yhteen?
C2:C7 on sarake, jossa ovat summat. Se on aina ensimmäisenä.
Mikä on ensimmäinen ehtoni?
Tarkista sarake A2:A7 (myyjä) ja pidä vain rivit, jotka täsmäävät arvoon E2 (Anna).
Mikä on toinen ehtoni?
Tarkista sarake B2:B7 (alue) ja pidä vain rivit, jotka täsmäävät arvoon F2 (Pohjoinen).
Vaihda E2:ksi "Ben" ja F2:ksi "Eteläinen", niin yhteensumma muuttuu arvoon 150. Voit myös jättää toisen ehdon pois, jos tarvitset vain yhden, tai lisätä kolmannen, neljännen tai useampia. Lisää vain pareja: "tarkistettava sarake" ja "ehto".
Puolipiste vai pilkku? Se riippuu asuinpaikastasi
Jos internetistä kopioitu kaava antaa virheen, vaikka se näyttää täydelliseltä, syynä on usein erotinmerkki.
| Tyyli | Erotinmerkki | Tyypilliset alueet | Esimerkki |
|---|---|---|---|
| Eurooppalainen | Puolipiste ; | Suomi, Ruotsi, Saksa, Ranska, Espanja | =SUMMA.JOS.JOUKKO(C2:C7; A2:A7; E2; B2:B7; F2) |
| Amerikkalainen | Pilkku , | Yhdysvallat, Britannia, Kanada, Australia | =SUMIFS(C2:C7, A2:A7, E2, B2:B7, F2) |
Kaava tekee saman asian kummallakin tyylillä. Monissa Euroopan maissa pilkku on desimaalimerkki (2,5 eikä 2.5), joten Excel käyttää osien välillä puolipistettä. Jos saat virheen, vaihda jokainen pilkku puolipisteeksi tai päinvastoin. Huomaa myös, että internetin esimerkeissä on yleensä englanninkielinen funktion nimi SUMIFS, kun suomenkielinen Excel vaatii nimen SUMMA.JOS.JOUKKO.
Näin käytät sitä itse
- Napsauta tyhjää solua, johon haluat yhteensumman.
- Kirjoita =SUMMA.JOS.JOUKKO(
- Valitse sarake, jonka luvut haluat laskea yhteen, ja kirjoita sitten puolipiste.
- Valitse ensimmäisen ehdon sarake ja sen jälkeen solu tai sana, jota sen pitää vastata.
- Toista jokaiselle lisäehdolle, kirjoita lopetussulku ) ja paina Enter.
Ehdot voivat olla älykkäämpiä kuin "yhtä suuri kuin"
Tähän asti ehto oli tarkka nimi. SUMIFS osaa myös verrata lukuja ja etsiä kuvioita. Kirjoita ehto lainausmerkkien sisään:
| Haluat | Kirjoita tämä ehto |
|---|---|
| Summat, jotka ovat yli 100 | ">100" |
| Summat, jotka ovat vähintään 100 | ">=100" |
| Kaikki paitsi Anna | "<>Anna" |
| Nimet, jotka alkavat kirjaimilla "An" | "An*" (tähti tarkoittaa "mitä tahansa") |
| Päivämäärät 1.3.2025 jälkeen | ">"&E2, kun päivämäärä on solussa E2 |
Esimerkiksi tämä laskee yhteen kaikki Annan myynnit, jotka ovat yli 100:
=SUMMA.JOS.JOUKKO(C2:C7; A2:A7; "Anna"; C2:C7; ">100")
Tässä samaa saraketta (C) käytetään sekä yhteenlaskuun että ehtoon. Se on täysin sallittua.
SUMMA, SUMMA.JOS ja SUMMA.JOS.JOUKKO: Mitä eroa on?
| Funktio | Mitä se tekee | Käytä sitä, kun |
|---|---|---|
| SUMMA (SUM) | Laskee kaiken yhteen | Haluat kokonaissumman |
| SUMMA.JOS (SUMIF) | Laskee yhteen rivit, jotka täyttävät yhden ehdon | Sinulla on vain yksi ehto |
| SUMMA.JOS.JOUKKO (SUMIFS) | Laskee yhteen rivit, jotka täyttävät yhden tai useamman ehdon | Sinulla on yksi, kaksi tai useita ehtoja |
Jos olet epävarma, käytä SUMIFSiä (SUMMA.JOS.JOUKKO). Se toimii myös yhdellä ehdolla, eikä sinun tarvitse koskaan vaihtaa funktiota, kun kysymyksesi laajenee.
Yleisiä ongelmia ja niiden ratkaisut
Tulos on 0, vaikka odotit enemmän
Mikään rivi ei täyttänyt kaikkia ehtoja. Tarkista kirjoitusasu ja etsi ylimääräisiä välilyöntejä tiedoista tai ehtosolusta. "Anna " (välilyönnillä) ei ole sama asia kuin "Anna". Varmista myös, että ehto vastaa tarkasti sitä, miten tieto on kirjoitettu, esimerkiksi "Pohjoinen" ja "Pohjoiset".
Tulos on liian suuri tai liian pieni
Tarkista, että lukusarake on kaavassa ensimmäisenä. Jos järjestys menee sekaisin, Excel laskee yhteen väärän sarakkeen. Varmista myös, että jokainen ehto osoittaa oikeaan sarakkeeseen.
Excel sanoo, että kaavassa on ongelma
Tarkista erotinmerkit. Jos Excelisi käyttää eurooppalaisia asetuksia, käytä puolipisteitä (;) pilkkujen (,) sijaan, kuten yllä selitettiin.
Näet #ARVO!
Kaavasi alueet eivät ole yhtä isoja. Esimerkiksi C2:C7 yhdessä A2:A9:n kanssa. Varmista, että jokaisessa alueessa on yhtä monta riviä.
Jotkin luvut jäävät huomiotta
Joskus luku on tallennettu tekstinä, jolloin se näyttää luvulta, mutta Excel ei laske sitä mukaan. Jos solussa on pieni vihreä kolmio tai luku on tasattu vasemmalle, muunna se oikeaksi luvuksi.
Ehto, jossa on > tai <, ei toimi
Ehdot, jotka sisältävät merkkejä kuten >, < tai =,
pitää kirjoittaa lainausmerkkien sisään, esimerkiksi
">100". Jos haluat käyttää solun arvoa,
yhdistä ne &-merkillä: ">"&E2.
Lyhyt versio
- SUMIFS laskee yhteen lukuja, mutta vain riveiltä, jotka täyttävät ehtosi.
- Kaava on =SUMMA.JOS.JOUKKO(laske tämä yhteen; tarkista tämä sarake; tätä vasten; ...).
- Yhteenlaskettavat luvut tulevat aina ensin, sitten parit "tarkistettava sarake" ja "ehto".
- Rivin on täytettävä kaikki ehdot, jotta se lasketaan mukaan.
- Osoita ehdot soluihin, jotta voit muuttaa kysymystä koskematta kaavaan.
- Käytä puolipisteitä eurooppalaisessa Excelissä ja pilkkuja amerikkalaisessa Excelissä.