excelkurz.sk

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.

ABCD
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.

Ako funguje funkcia SUMIF

=SUMIF(rozsah; kritériá; [rozsah_súhrnu])

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

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.

B7 =SUMIF(A2:A5;"Hrušková";B2:B5)
AB
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.

B7 =SUMIF(B2:B5;"<=500")
AB
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úš.

B7 =SUMIFS(C2:C5;A2:A5;"Matúš";B2:B5;"Web")
ABC
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.

Vyriešené: 0 z 5 Body: 0 Séria: 0 dní

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.

AB
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.

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 €.

AB
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.

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.

AB
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.

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.

ABC
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.

Cvičenie 5: Priemer silných dní

náročnejšia

Predajň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.

ABC
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.

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.

Súvisiace