COUNTIF a COUNTIFS v Exceli: počet s podmienkou
COUNTIF spočíta bunky, ktoré spĺňajú podmienku, napríklad zákazníkov z jedného mesta. COUNTIFS spočíta riadky, v ktorých platí viac podmienok naraz.
Vyskúšaj si to: Zákazníci z Košíc
Do bunky B8 napíš =COUNTIF(B2:B6;"Košice") a stlač Enter. Excel spočíta, koľko zákazníkov je z Košíc.
| A | B | |
|---|---|---|
| 1 | Zákazník | Mesto |
| 2 | Rybár | Košice |
| 3 | Sedláková | Prešov |
| 4 | Tóth | Košice |
| 5 | Hanzelová | Poprad |
| 6 | Mikuš | Košice |
| 7 | ||
| 8 | Z Košíc |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =COUNTIF(B2:B6;"Košice")
COUNTIF prejde bunky B2 až B6 a spočíta tie, v ktorých je „Košice“. Na veľkých a malých písmenách nezáleží.
Ako funguje funkcia COUNTIF
=COUNTIF(rozsah; kritériá)
- rozsah: bunky, ktoré sa majú spočítať, napríklad stĺpec s mestami.
- kritériá: podmienka, ktorú musí bunka splniť, napríklad
"Košice",">25"alebo odkaz na bunku.
Výsledkom je počet buniek, nie ich súčet. Keď chceš sčítať čísla z riadkov, ktoré podmienku spĺňajú, použi SUMIF. Pri texte COUNTIF nerozlišuje veľké a malé písmená.
Ako písať podmienky
"Košice": bunka obsahuje presne tento text.B10: rovná sa obsahu bunky B10, bez úvodzoviek.">25","<>áno": porovnanie, operátor aj hodnota spolu v úvodzovkách.">"&B12: porovnanie s číslom v bunke, spojené znakom &."*káva*": text obsahuje slovo káva. Hviezdička zastúpi ľubovoľný text, otáznik jeden znak."*": bunka obsahuje akýkoľvek text."<>": bunka nie je prázdna.
COUNTIFS: viac podmienok naraz
=COUNTIFS(rozsah_kritérií1; kritériá1; [rozsah_kritérií2; kritériá2]; …)
COUNTIFS berie dvojice rozsah a podmienka, najviac 127 dvojíc. Riadok sa zaráta, len keď v ňom platia všetky podmienky. Rozsahy musia mať rovnaký počet riadkov, inak COUNTIFS vráti #VALUE!. Na rozdiel od SUMIFS tu nie je žiadny rozsah súčtu, takže poradie sa pomýliť nedá.
Príklady
Počet podľa textu z bunky
Koľko úloh má stav, ktorý je napísaný v B7. Stačí zmeniť B7 a počet sa prepočíta.
=COUNTIF(B2:B5;B7) | A | B | |
|---|---|---|
| 1 | Úloha | Stav |
| 2 | Faktúry | hotová |
| 3 | Inventúra | rozpracovaná |
| 4 | Výplaty | hotová |
| 5 | Ponuka | hotová |
| 6 | ||
| 7 | Stav: | hotová |
| 8 | Počet: | 3 |
Počet medzi dvoma hodnotami
Koľko účastníkov má 18 až 26 rokov. COUNTIFS môže mať dve podmienky aj na ten istý stĺpec: aspoň 18 a zároveň najviac 26.
=COUNTIFS(B2:B6;">=18";B2:B6;"<=26") | A | B | |
|---|---|---|
| 1 | Účastník | Vek |
| 2 | Miroslav | 17 |
| 3 | Klára | 22 |
| 4 | Juraj | 26 |
| 5 | Tereza | 31 |
| 6 | Patrik | 19 |
| 7 | ||
| 8 | 18 až 26: | 3 |
Bunky s textom
Koľko ľudí v ankete napísalo poznámku. Podmienka "*" spočíta bunky, v ktorých je text.
=COUNTIF(B2:B5;"*") | A | B | |
|---|---|---|
| 1 | Respondent | Poznámka |
| 2 | R1 | chýba parkovisko |
| 3 | R2 | |
| 4 | R3 | dlhé čakanie |
| 5 | R4 | |
| 6 | ||
| 7 | S poznámkou: | 2 |
Časté chyby a čo znamenajú
Výsledok je 0, hoci zhody v tabuľke sú
- Odkaz na bunku je v úvodzovkách:
"B10"hľadá text B10, nie obsah bunky. - V bunkách sú medzery navyše, napríklad „Košice “ s medzerou na konci.
- Čísla v tabuľke sú uložené ako text, zvyčajne po importe z iného programu.
- Podmienka bez hviezdičiek hľadá celý obsah bunky. „káva“ nenájde „Ľadová káva“.
Excel vzorec neprijme
Porovnanie bez úvodzoviek, napríklad =COUNTIF(B2:B8;>25). Operátor aj číslo musia byť
v úvodzovkách: ">25".
Chyba #VALUE!
Rozsahy v COUNTIFS majú rôzny počet riadkov, napríklad B2:B9 a C2:C8. Všetky rozsahy musia byť rovnako veľké. Pozor aj na dlhé texty: pri texte dlhšom ako 255 znakov COUNTIF počíta nesprávne.
Hraničná hodnota sa zaráta zle
„Nad 25“ je ">25", „od 25“ je ">=25". Pri teplote presne 25 °C dajú tieto dve
podmienky rôzny 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ý počet neprejde.
Cvičenie 1: Letné dni
ľahkáMeteorologička si zapisuje najvyššie denné teploty za týždeň. Do B10 napíš vzorec s funkciou COUNTIF, ktorý spočíta dni s teplotou nad 25 °C.
| A | B | |
|---|---|---|
| 1 | Deň | Teplota (°C) |
| 2 | Pondelok | 24,5 |
| 3 | Utorok | 27 |
| 4 | Streda | 29,5 |
| 5 | Štvrtok | 23 |
| 6 | Piatok | 26 |
| 7 | Sobota | 21,5 |
| 8 | Nedeľa | 25,5 |
| 9 | ||
| 10 | Dni nad 25 °C |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =COUNTIF(B2:B8;">25")
Podmienka ">25" je v úvodzovkách celá, aj so znakom >. Deň s teplotou presne 25 °C sa nezaráta, lebo „nad 25“ znamená viac ako 25.
Cvičenie 2: Prihlášky na krúžok
ľahkáŠkola zbiera prihlášky na krúžky. Do B11 napíš vzorec, ktorý spočíta prihlásených na krúžok, ktorý je v bunke B10. Keď sa v B10 krúžok zmení, výsledok sa má prepočítať.
| A | B | |
|---|---|---|
| 1 | Žiak | Krúžok |
| 2 | Adam | Robotika |
| 3 | Ema | Futbal |
| 4 | Filip | Robotika |
| 5 | Natália | Spev |
| 6 | Kristián | Futbal |
| 7 | Laura | Robotika |
| 8 | Šimon | Spev |
| 9 | ||
| 10 | Krúžok: | Robotika |
| 11 | Prihlásených: |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =COUNTIF(B2:B8;B10)
S odkazom B10 namiesto napísaného textu stačí zmeniť krúžok v B10 a výsledok sa prepočíta sám.
Cvičenie 3: Všetko s kávou
strednáKaviareň má v ponuke rôzne nápoje. Do B11 napíš vzorec, ktorý spočíta položky, v ktorých názve je slovo „káva“, či už je na začiatku, v strede, alebo na konci.
| A | B | |
|---|---|---|
| 1 | Položka | Cena (€) |
| 2 | Káva zrnková | 2,40 |
| 3 | Čaj čierny | 2,10 |
| 4 | Ľadová káva | 3,50 |
| 5 | Káva s mliekom | 2,90 |
| 6 | Kakao | 2,80 |
| 7 | Čaj ovocný | 2,10 |
| 8 | Viedenská káva | 3,20 |
| 9 | Limonáda | 3,00 |
| 10 | ||
| 11 | Položky s kávou |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =COUNTIF(A2:A9;"*káva*")
Podmienka "*káva*" znamená „hocičo, potom káva, potom hocičo“. Nájde „Káva zrnková“ aj „Ľadová káva“.
Cvičenie 4: Veľké objednávky z Košíc
strednáDo B11 napíš vzorec, ktorý spočíta objednávky, ktoré sú z Košíc a zároveň majú sumu nad 100 €.
| A | B | C | |
|---|---|---|---|
| 1 | Objednávka | Mesto | Suma (€) |
| 2 | O-501 | Košice | 145 |
| 3 | O-502 | Trnava | 210 |
| 4 | O-503 | Košice | 64 |
| 5 | O-504 | Košice | 320 |
| 6 | O-505 | Martin | 88 |
| 7 | O-506 | Košice | 99 |
| 8 | O-507 | Trnava | 130 |
| 9 | O-508 | Košice | 180 |
| 10 | |||
| 11 | Košice nad 100 € |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =COUNTIFS(B2:B9;"Košice";C2:C9;">100")
COUNTIFS spočíta len riadky, v ktorých platia obe podmienky naraz. Objednávka O-507 z Trnavy má síce nad 100 €, ale nie je z Košíc.
Cvičenie 5: Koľko percent má školenie
náročnejšiaFirma sleduje, kto absolvoval školenie bezpečnosti práce. Do B11 napíš vzorec s funkciou COUNTIF, ktorý vypočíta, aký podiel zamestnancov školenie má. Bunka je naformátovaná na percentá.
| A | B | |
|---|---|---|
| 1 | Zamestnanec | Školenie |
| 2 | Baláž | áno |
| 3 | Čierna | nie |
| 4 | Dudáš | áno |
| 5 | Fialová | áno |
| 6 | Holub | nie |
| 7 | Jurčová | áno |
| 8 | Kmeť | áno |
| 9 | Lacková | nie |
| 10 | ||
| 11 | Podiel so školením |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =COUNTIF(B2:B9;"áno")/COUNTA(A2:A9)
Školenie má 5 z 8 zamestnancov, teda 0,625. Formát bunky to ukáže ako 63 %. COUNTA(A2:A9) sa prepočíta, keď niekto pribudne alebo odíde.
Otázky a odpovede
Aký je rozdiel medzi COUNTIF a COUNTIFS?
COUNTIF má jednu podmienku, COUNTIFS jednu alebo viac. Obe majú argumenty v rovnakom poradí: rozsah a za ním podmienka, takže COUNTIFS s jednou dvojicou funguje rovnako ako COUNTIF.
Ako spočítať bunky s viacerými podmienkami?
Keď majú platiť všetky naraz, použi COUNTIFS. Keď stačí jedna z nich, napríklad Košice alebo Prešov, sčítaj
dve funkcie COUNTIF: =COUNTIF(B2:B9;"Košice")+COUNTIF(B2:B9;"Prešov").
Ako v Exceli spočítať bunky s textom?
Bunky s akýmkoľvek textom spočíta =COUNTIF(B2:B20;"*"). Bunky s konkrétnym slovom kdekoľvek
v texte =COUNTIF(B2:B20;"*káva*"). Všetky neprázdne bunky, aj s číslami, spočíta
COUNTA.
Ako spočítať, koľkokrát sa hodnota opakuje?
Vedľa hodnoty napíš =COUNTIF($A$2:$A$50;A2) a vzorec skopíruj nadol. Rozsah je ukotvený znakmi $,
takže sa pri kopírovaní neposunie. Číslo väčšie ako 1 znamená, že hodnota je v zozname viackrát.