SUMIF a SUMIFS v Exceli: súčet s podmienkou
SUMIF sčíta len tie riadky, ktoré spĺňajú podmienku, napríklad tržby jednej predajne. SUMIFS zvládne podmienok viac naraz a AVERAGEIF počíta s podmienkou priemer.
Vyskúšaj si to: Tržby jednej predajne
Do bunky D2 napíš =SUMIF(A2:A4;"Košice";B2:B4) a stlač Enter. Excel sčíta tržby len v riadkoch, kde je predajňa Košice.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Predajňa | Tržba | ||
| 2 | Košice | 1250 | ||
| 3 | Žilina | 980 | ||
| 4 | Košice | 1430 |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =SUMIF(A2:A4;"Košice";B2:B4)
SUMIF prejde predajne v A2:A4. Kde nájde Košice, pripočíta tržbu z rovnakého riadku stĺpca B.
Ako funguje funkcia SUMIF
=SUMIF(rozsah; kritériá; [rozsah_súhrnu])
- rozsah: bunky, ktoré sa porovnávajú s podmienkou, napríklad stĺpec s predajňami.
- kritériá: podmienka, napríklad
"Košice",">100"alebo odkaz na bunku. - rozsah_súhrnu: bunky, ktoré sa sčítajú. Keď ho vynecháš, SUMIF sčíta priamo bunky z prvého argumentu.
SUMIF prechádza rozsah riadok po riadku. Kde podmienka platí, pripočíta číslo z rovnakého riadku rozsahu súhrnu. Oba rozsahy majú mať rovnakú veľkosť a začínať v rovnakom riadku.
Ako písať podmienky
"Košice": bunka obsahuje presne text Košice. Text píš do úvodzoviek.B9: rovná sa obsahu bunky B9. Odkaz na bunku je bez úvodzoviek.">100","<=50","<>Nitra": porovnanie. Operátor aj hodnota idú spolu do úvodzoviek.">="&B10: porovnanie s číslom v bunke. Operátor je v úvodzovkách a spojí sa s bunkou znakom &."Káva*": začína sa slovom Káva. Hviezdička zastúpi ľubovoľný text, otáznik jeden znak.100: rovná sa číslu 100. Samotné číslo úvodzovky nepotrebuje.
SUMIFS: viac podmienok naraz
=SUMIFS(rozsah_súčtu; rozsah_kritérií1; kritériá1; [rozsah_kritérií2; kritériá2]; …)
SUMIFS sčíta riadky, v ktorých platia všetky podmienky naraz. Pozor na poradie: rozsah súčtu je pri SUMIFS prvý, pri SUMIF posledný. Za ním idú dvojice rozsah a podmienka, najviac 127 dvojíc.
AVERAGEIF: priemer s podmienkou
=AVERAGEIF(rozsah; kritériá; [priemerný_rozsah])
Argumenty sú rovnaké ako pri SUMIF, len výsledkom je priemer. Keď podmienku nesplní žiadna bunka, AVERAGEIF vráti chybu #DIV/0!. Pre viac podmienok je AVERAGEIFS s poradím ako pri SUMIFS.
Príklady
Súčet podľa textu
Firma sčíta cestovné náhrady jedného zamestnanca.
=SUMIF(A2:A5;"Hrušková";B2:B5) | A | B | |
|---|---|---|
| 1 | Zamestnanec | Náhrada (€) |
| 2 | Hrušková | 46,2 |
| 3 | Daniš | 18 |
| 4 | Hrušková | 31,5 |
| 5 | Daniš | 52,8 |
| 6 | ||
| 7 | Hrušková: | 77,7 |
Súčet podľa čísla
Súčet faktúr do 500 €. Podmienka aj sčítanie sa týkajú toho istého stĺpca, tretí argument netreba.
=SUMIF(B2:B5;"<=500") | A | B | |
|---|---|---|
| 1 | Faktúra | Suma (€) |
| 2 | F-101 | 320 |
| 3 | F-102 | 1150 |
| 4 | F-103 | 480 |
| 5 | F-104 | 760 |
| 6 | ||
| 7 | Do 500 €: | 800 |
Dve podmienky so SUMIFS
Hodiny na projekte Web, ktoré odpracoval Matúš.
=SUMIFS(C2:C5;A2:A5;"Matúš";B2:B5;"Web") | A | B | C | |
|---|---|---|---|
| 1 | Meno | Projekt | Hodiny |
| 2 | Matúš | Web | 6 |
| 3 | Simona | Web | 4 |
| 4 | Matúš | Sklad | 3 |
| 5 | Matúš | Web | 5 |
| 6 | |||
| 7 | Matúš, Web: | 11 |
Časté chyby a čo znamenajú
Podmienka bez úvodzoviek
Text a porovnanie v podmienke musia byť v úvodzovkách. =SUMIF(A2:A9;Košice;B2:B9) vráti
chybu #NAME? a pri >100 bez úvodzoviek Excel vzorec vôbec neprijme.
Odkaz na bunku v úvodzovkách
">=B10" nie je porovnanie s bunkou B10, ale s textom B10. Správne je
">="&B10. Rovnako "B9" hľadá text B9, nie obsah bunky.
Zlé poradie v SUMIFS
Kto je zvyknutý na SUMIF, dá rozsah súčtu na koniec. SUMIFS ho chce na začiatku. Pri zlom poradí vráti nesprávny súčet alebo chybu #VALUE!.
Rozsahy rôznej veľkosti
Rozsahy v SUMIFS musia mať rovnaký počet riadkov, inak SUMIFS vráti #VALUE!. Pri SUMIF Excel rozsah súhrnu potichu prispôsobí, takže chybu neuvidíš, len nesprávny výsledok.
Cvičenia
Päť úloh od ľahkých po náročnejšie. Vzorec píš do vyznačenej bunky, kontrolu spustíš klávesom Enter alebo tlačidlom Skontrolovať. Kontrola vzorec vyskúša aj na iných údajoch, takže napísaný výsledok neprejde.
Cvičenie 1: Výdavky podľa kategórie
ľahkáDomácnosť si zapisuje výdavky. Do B10 napíš vzorec s funkciou SUMIF, ktorý sčíta výdavky za kategóriu napísanú v B9.
| A | B | |
|---|---|---|
| 1 | Kategória | Suma (€) |
| 2 | Potraviny | 54,2 |
| 3 | Doprava | 30 |
| 4 | Potraviny | 38,9 |
| 5 | Zábava | 25 |
| 6 | Potraviny | 61,4 |
| 7 | Doprava | 12 |
| 8 | ||
| 9 | Kategória: | Potraviny |
| 10 | Spolu (€): |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =SUMIF(A2:A7;B9;B2:B7)
=SUMIF(A2:A7;B9;B2:B7) porovná každú kategóriu s textom v B9 a sčíta sumy zo zhodných riadkov. Keď v B9 prepíšeš kategóriu, súčet sa zmení sám.
Cvičenie 2: Objednávky nad 100 €
ľahkáDo B9 napíš vzorec s funkciou SUMIF, ktorý sčíta len objednávky vyššie ako 100 €.
| A | B | |
|---|---|---|
| 1 | Zákazník | Suma (€) |
| 2 | Kubík | 85 |
| 3 | Lacková | 240 |
| 4 | Mráz | 100 |
| 5 | Oravcová | 132,5 |
| 6 | Pekár | 64 |
| 7 | Rybárová | 310 |
| 8 | ||
| 9 | Nad 100 €: |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =SUMIF(B2:B7;">100")
Podmienka ">100" vyberie sumy vyššie ako 100. Tretí argument chýba, preto SUMIF sčíta priamo bunky z B2:B7, ktoré podmienku spĺňajú. Objednávka presne za 100 € sa nezaráta.
Cvičenie 3: Názov začína slovom Káva
strednáKaviareň predáva viac druhov kávy. Do B10 napíš vzorec s funkciou SUMIF, ktorý sčíta tržby za všetky položky, ktorých názov sa začína slovom Káva.
| A | B | |
|---|---|---|
| 1 | Položka | Tržba (€) |
| 2 | Káva zrnková | 412 |
| 3 | Čaj zelený | 96 |
| 4 | Káva mletá | 287 |
| 5 | Kakao | 74 |
| 6 | Káva v kapsulách | 355 |
| 7 | Čaj ovocný | 118 |
| 8 | Ľadová káva | 140 |
| 9 | ||
| 10 | Káva spolu: |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =SUMIF(A2:A8;"Káva*";B2:B8)
Hviezdička zastúpi akýkoľvek text, takže "Káva*" vyhovie položkám Káva zrnková, Káva mletá aj Káva v kapsulách. Ľadová káva sa slovom Káva nezačína, preto sa nezaráta.
Cvičenie 4: Predajňa a mesiac
strednáDo B13 napíš vzorec, ktorý sčíta tržby predajne z B11 za mesiac z B12. Podmienky sú dve, preto potrebuješ SUMIFS.
| A | B | C | |
|---|---|---|---|
| 1 | Predajňa | Mesiac | Tržba (€) |
| 2 | Košice | Január | 8200 |
| 3 | Žilina | Január | 6400 |
| 4 | Košice | Február | 7900 |
| 5 | Žilina | Február | 7100 |
| 6 | Košice | Marec | 9050 |
| 7 | Žilina | Marec | 6800 |
| 8 | Košice | Január | 450 |
| 9 | |||
| 10 | |||
| 11 | Predajňa: | Košice | |
| 12 | Mesiac: | Január | |
| 13 | Tržba: |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =SUMIFS(C2:C8;A2:A8;B11;B2:B8;B12)
=SUMIFS(C2:C8;A2:A8;B11;B2:B8;B12) sčíta tržby z C2:C8 len v riadkoch, kde je v stĺpci A predajňa z B11 a zároveň v stĺpci B mesiac z B12. Košice majú v januári dva záznamy, oba sa zarátajú.
Cvičenie 5: Priemer silných dní
náročnejšiaPredajňa sleduje počet zákazníkov a tržbu za deň. Do B11 napíš vzorec s funkciou AVERAGEIF, ktorý vypočíta priemernú tržbu dní, keď prišlo aspoň toľko zákazníkov, koľko je v B10.
| A | B | C | |
|---|---|---|---|
| 1 | Deň | Zákazníci | Tržba (€) |
| 2 | Pondelok | 96 | 1840 |
| 3 | Utorok | 120 | 2310 |
| 4 | Streda | 134 | 2655 |
| 5 | Štvrtok | 101 | 1975 |
| 6 | Piatok | 158 | 3120 |
| 7 | Sobota | 87 | 1590 |
| 8 | |||
| 9 | |||
| 10 | Aspoň zákazníkov: | 120 | |
| 11 | Priemerná tržba: |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =AVERAGEIF(B2:B7;">="&B10;C2:C7)
">="&B10 vytvorí podmienku ">=120" a keď sa číslo v B10 zmení, podmienka sa zmení s ním. AVERAGEIF potom spriemeruje tržby z C2:C7 len pre dni, ktoré podmienku spĺňajú.
Otázky a odpovede
Aký je rozdiel medzi SUMIF a SUMIFS?
SUMIF má jednu podmienku, SUMIFS jednu alebo viac. SUMIFS má navyše rozsah súčtu na prvom mieste. Keďže SUMIFS zvládne aj jednu podmienku, môžeš používať len ju.
Ako sčítať s viacerými podmienkami?
Ak majú platiť všetky naraz (predajňa Košice a mesiac január), použi SUMIFS. Ak stačí jedna z nich
(Košice alebo Žilina), sčítaj dve funkcie SUMIF: =SUMIF(A2:A9;"Košice";C2:C9)+SUMIF(A2:A9;"Žilina";C2:C9).
Ako napísať podmienku „väčšie ako“?
Operátor a číslo daj spolu do úvodzoviek: ">100". Keď je číslo v bunke, spoj ich znakom &:
">"&B10. Ďalšie operátory sú <, >=, <= a <> (nerovná sa).
Ako vypočítať priemer s podmienkou?
Funkciou AVERAGEIF. Napríklad =AVERAGEIF(A2:A9;"Košice";C2:C9) vráti priemernú tržbu
predajne v Košiciach.
S viacerými podmienkami použi AVERAGEIFS.