Funkce SUMIF() a SUMIFS()

Základní užití funkce SUMIF() již bylo představeno v článku Ověření dat: vlastní filtr. V dnešním příspěvku rozšířím funkci s jedním kritériem o další dodatečná kritéria, k čemuž se používá vícekriteriální funkce SUMIFS().

Funkce najdete v kategorii matematických funkcí. Obě funkce jsou case sensitive, což znamená, že nerozlišují velká a malá písmena.

Funkce SUMIF()

Funkce vrací součet hodnot buněk, které splňují jedno zvolené kritérium. Syntaxe funkce je

=SUMIF(oblast; kritérium, [součet])

První argument funkce Oblast slouží k zadání rozsahu buněk, které mají být testovány na základě zvoleného kritéria zadaného v druhém argumentu. Poslední argument [Součet] slouží k zadání rozsahu buněk určených ke sčítání. Hranatá závorka znamená, že argument funkce není povinný.

Příklady užití funkce

Při základním použití funkce pouze se dvěma argumenty se do argumentu Oblast vkládá rozsah buněk určených k sumarizaci. Například součet buněk, které mají hodnotu vyšší než 1.000 zapíšeme

=SUMIF(rozsah_buněk; ">1000")

V tomto případě je užití funkce identické s podmínkovou funkcí COUNTIF(), která vrací počet buněk splňující kritérium.

Pokud bychom chtěli například sečíst prodeje po obchodnících, musíme funkci použít v její tříargumentové podobě. Kritériem k vyhodnocení je jméno obchodníka. Shoduje-li se se jménem obchodníka ve vybrané oblasti dat, je buňka zahrnuta do součtu.

=SUMIF("sloupec_obchodníci"; "jméno_obchodníka"; "sloupec_prodeje")

Pokročilejší možností užití funke je kombinace s logickými a zástupnými znaky. Například znak hvězdičky * zastupuje libovolný počet znaků. Využijeme například v situaci, kdy potřebujeme získat součet hodnot s určitým počátečním písmenem. Dalším zástupným znakem je otazník ?, který zastupuje jeden libovolný znak.

Žlutý sloupec sčítá buňky, které překočily mez 80.000. Červený sloupec sčítá buňky s počátečním písmenem a. Třetí sloupec sčítá buňky, které nemají počáteční písmeno a. A poslední zelený sloupec sčítá buňky, které mají přesně pět znaků.

Funkce SUMIFS()

Funkce SUMIFS() je nadstavbou jednokriteriální funkce. Funkce vrací součet hodnot buněk, které splňují více kritérií. Lze ji však použít i pro jediné kritérium. Argumenty funkce v hranatých závorkách nejsou povinné.

=SUMIFS(oblast_součtu; oblast_kritérií1; kritérium1; [oblast_kritérií2; kritérium2]; ...)

Testování buněk v praxi

Následující tabulka obsahuje palivové a nepalivové transakce zákazníků v mezinárodní síti čerpacích stanic. Úkolem je získat součty nákupů v Kč na základě zvolených kritérií.

V prvním případě nás zajímá nákup produktu Diesel v Kč. Protože se jedná pouze o jednu podmínku je jedno, zda použijete funkci SUMIF() nebo funkci SUMIFS(). Modrá barva představuje zvolené kritérium a oblast buněk, která vstupuje do vyhodnocení kritéria. Červený sloupec říká, která čísla mají být sčítány při splnění kritéria.

Funkce vrátí částku 9.467 Kč, která říká, za kolik zákazníci nakoupili Diesel (viz sloupec Výsledek).

Ve druhém případě k nákupu Diesel přidáme kritérium země, ve které byl nákup realizován. Funkce SUMIFS() sečte hodnoty, je-li splněna podmínka produkt Diesel a současně je nákup realizován v SK.

Funkce vrátí částku 3.341 Kč.

V posledním případě je součet založen na vyhodnocení 3 kritérií. Třetí kritérium je, že nákup produktu Diesel na Slovensku byl, po přepočtu do Kč, vyšší než 1.200 Kč.

Výstupem je částka 2.534 Kč.