Funkce SUMIF a COUNTIF jsou dokonalým spojením podmínky a funkcí na sčítání a počítání. Tyto dvě funkce vám tak dovolí, sčítat nebo počítat na základě podmínky, kterou ji zadáte. Podmínku přitom může být téměř cokoliv. V dnešním videu s ukážeme jak funkce SUMIF a COUNTIF používat, jaká mají kritéria a jak v nich můžeme uplatnit logické podmínky se znaménky větší, menší nebo rovná se. Na závěr si rovněž ukážeme nejčastější chyby, které se při práci se SUMIF a COUNTIF vyskytují, a které vám mohou vrátit chybovou hlášku.
Cvičný excelový soubor ke stažení:
Funkce SUMIF
Funkce SUMIF je dokonalým spojením dvou excelových funkcí, kterými jsou funkce SUMA a podmínka KDYŽ neboli funkce IF v angličtině. Jak už toto spojení napovídá, jedná se o funkci, která sečte hodnoty na základě nějaké podmínky, kterou ji zadáte.
= SUMIF (oblast; kritérium; [součet])
Funkce SUMIF má tři parametry. Prvním parametrem je oblast, druhým parametrem je kritérium a třetí parametr součet je nepovinný.
Použití funkce SUMIF si ukážeme na několika následujících příkladech.
Řekněme, že nás zajímá, kolik byly naše příjmy v roce 2019. V naší zdrojové tabulce na listu Data máme údaje o příjmech a výdajích v několika letech. Stejně tak máme náklady i příjmy rozdělené do několika kategorií. Te zdrojové tabulky vidíme, že nám plynou příjmy ze zaměstnání v podobě mzdy, stejně tak pronajímáme nemovitost, ze které nám plyne měsíčně nájemné. Dále dvakrát do roka dostaneme bonus ke mzdě.
Začneme tedy tím, že nás zajímá, kolik jsme vydělali v práci ve formě mzdy. Do buňky B4 napíšeme funkci SUMIF. Prvním parametrem funkce je oblast. Oblast je oblast buněk, proti které chceme ověřit splnění kritéria. Naše kritérium je slovo mzda, takže ve zdrojové tabulce musíme označit sloupec, ve kterém se nachází slovo mzda. To je sloupec Popis platby na listu Data (B3:B1429). Nezapomeneme sloupec zafixovat klávesou F4 jak pro řádky, tak pro sloupce.
Druhým parametrem je kritérium, tím je slovo mzda. Označíme proto v naší cílové tabulce slovo mzda v buňce A4. Posledním parametrem je oblast součtu. Tedy které buňky chceme sčítat. Potřebujeme sečíst sloupec příjem na listu Data, tak ho celý označíme (C3:C1429).
Technicky funguje funkce SUMIF tak, že otestuje každý řádek v oblasti buněk, zda splňuje zadané kritérium. U buněk, které vyhovují námi zadanému kritériu následně sečte hodnoty.
Funkce COUNTIF
Rovnou si na příkladech budeme ukazovat i druhou funkci, funkci COUNTIF. Funkce COUNTIF funguje na velmi podobném principu, jako SUMIF, akorát s tím rozdílem, že počítá hodnoty. Zatímco funkce SUMIF je sčítá, tak funkce COUNTIF je počítá. Je to v podstatě funkce POČET s podmínkou. Rozdíl od funkce SUMIF si ukážeme na tomto příkladě.
= COUNTIF (oblast; kritérium)
Funkce COUNTIF má dva parametry. Prvním parametrem je oblast, tedy oblast buněk, vůči které porovnáváme kritérium. Druhým parametrem je samotné kritérium. Na rozdíl od funkce SUMIF zde nemáme třetí parametr, kterým je součet.
Do buňky C4 napíšeme funkci COUNTIF a jako oblast označíme sloupec, kde se vyskytuje slovo mzda, tedy sloupec Popis platby na listu Data ((B3:B1429). Nezapomeneme zafixovat klávesou F4 jak pro řádky, tak sloupce. A jako kritérium označíme buňku, ve které máme mzdu, tedy buňku A4. Vyjde nám číslo 35, to znamená, že ve zdrojové tabulce se položka mzda objevuje celkem 35krát.
Funkce COUNTIF tedy projde řádek po řádku, a pokud se popis platby shoduje se zadaným kritériem, tak uvede jedničku, u ostatních řádků, které nevyhovují si Excel pamatuje nulu. Na konci pak sečte jedničky a to nám vrátí celkový počet.
SUMIF a COUNTIF
Funkce SUMIF a COUNTIF by fungovaly i kdybyste kritérium vyplnili přímo do vzorce jako textovou hodnotu. V tomto případě ovšem nesmíte zapomenout uvést textovou hodnotu do uvozovek.
Zkusíme to na dalším příkladě, kdy chceme sečíst platby za hypotéku. Do buňky B10 napíšeme funkci SUMIF a označíme sloupec, kde chceme hledat slovo hypotéka, tedy sloupec Popis platby na listu Data (B3:B1429) a jako kritérium neoznačíme tentokrát odkaz na buňku, ale napíšeme slovo hypotéka přímo do vzorce. Jelikož se jedná o textovou hodnotu, tak nesmíme slovo hypotéka zapomenout uvést do uvozovek.
Posledním parametrem je součet. Opět označíme celý sloupec na listu Data, tentokrát s výdaji (D3:D1429). Funkce SUMIF vrátila součet všech plateb za hypotéku. To samé provedeme i pro funkci COUNTIF. Do buňky C10 napíšeme funkci COUNTIF, označíme sloupec s popisem plateb (B3:B1429) a jako kritérium napíšeme opět slovo hypotéka, a to v uvozovkách. Na hypotéce jsme tedy zaplatili celkem 544 tisíc, a to v 35 platbách.
SUMIF a COUNTIF s logickými podmínkami
Ve funkcích SUMIF a COUNTIF můžete používat i logické podmínky, a to znaménka menší, větší, rovná se a jejich varianty. Zkusíme to na dalším příkladě, kde chceme sečíst všechny výdaje, které byly za rok vyšší než částka 5 000 Kč.
Do buňky B15 napíšeme funkci SUMIF a otevřeme závorku. Nejdříve si musíme uvědomit, co je teď naše oblast buněk. V tomto případě nehledáme specifický výdaj, ale ptáme se, které výdaje byly vyšší než určitá hodnota. Naší oblastí buněk je tak celý sloupec s výdaji (D3:D1429). Jako kritérium napíšeme >5 000, nezapomeneme uvést do uvozovek, jelikož se jedná o textovou hodnotu.
Teď nás ještě zajímá, kolik takových plateb vyšších než 5 000 Kč bylo. Do buňky C15 tedy napíšeme funkci COUNTIF a jako oblast označíme zase oblast s výdaji (D3:D1429) a jako kritérium napíšeme >5000 Kč v uvozovkách. Plateb vyšších než 5 000 Kč bylo celkem 37.
Zásadou správné práce v Excelu je, že se všechny variabilní buňky píší zvlášť do samostatných buněk, aby bylo jednodušší je měnit. Existuje několik zápisů s logickými operátory, které můžete využít a je jen na vás, který vám bude nejsympatičtější.
Nejzákladnějším způsobem je napsat celou logickou podmínku neboli kritérium do vzorce, tak jak jsme si to ukázali v předchozím příkladu. Buňky kritéria jsou vyznačené světle oranžově.
Funkce SUMIF a COUNTIF – kritérium jako odkaz na buňku
První variantou je, že si do pomocné buňky D18 napíšete částku kritéria i se znaménkem. V takovém případě nemusíte ve vzorci kritérium uvádět do uvozovek a pouze se ve vzorci odkážete na tuto buňku. Do buňky B19 tedy napíšeme funkci SUMIF a jako oblast označíme sloupec s výdaji (D3:D1429) a jako kritérium označíme buňku, kde máme uvedeno >2 000 Kč, tedy buňku D18. Ještě zjistíme, kolik takových plateb bylo. V buňce C19 napíšeme funkci COUNTIF a jako oblast buněk opět sloupec D3:d1429 na listu Data a jako kritérium odkaz na buňku D18.
Funkce SUMIF a COUNTIF – kritérium jako kombinace odkazu na buňku a textové hodnoty
Druhou variantou zápisu je, že znaménko větší nebo menší napíšete do vzorce tzv. „natvrdo“ a na částku kritéria se odkážete do buňky. Zápis by v takovém případě vypadal takto. V buňce B20 funkce SUMIF a jako oblast buněk sloupec s výdaji na listu Data a jako kritérium nejdříve znaménko větší v uvozovkách, a odkaz na buňku (D20), kde máme uvedenou hodnotu 2 000 Kč. To ale není vše, jelikož zde spojujeme textovou hodnotu a číselnou hodnotu, tak mezi nimi musíme uvést znaménko pro ampersand (&).
To samé zopakujeme i pro funkci COUNTIF.
Funkce SUMIF a COUNTIF – kritérium jako kombinace odkazů na buňku
Poslední variantou je mít znaménko logického operátoru i číslo ve vlastních buňkách. Zápis funkce by pak vypadal takto. Do buňky B21 bychom napsali funkci SUMIF a jako oblast buněk označili sloupec s výdaji na listu Data a jako kritérium bychom nejdříve označili buňku pro znaménko (D19), ampersand (&) a buňku s hodnotou (D20). Jelikož se odkazujeme na buňky, tak není potřeba nic uvádět do uvozovek.
To samé bychom udělali i pro funkci COUNTIF.
Všechny čtyři zápisy jsou správně, takže je jen na vás, který se vám nejlépe pamatuje.
Nejčastější chyby ve funkci SUMIF a COUNTIF:
- Zapomenete textovou hodnotu uvést do uvozovek
- V parametrech oblast a součet jste označili nestejně velké oblasti (rozsah oblastí musí být stejný pro obě oblasti)
- Přehodili jste oblast součtu a oblast buněk, takže jste pro oblast součtu použili textové hodnoty
- V oblasti součtu se odkazujeme na textové hodnoty
Tyto výše uvedené chyby jsou nejčastější příčinou proč vám funkce SUMIF a COUNTIF vrátí chybové hlášky.
