Sumifs

enSUMIFS

Definice

Funkce sečte hodnoty ve vybrané oblasti pro splněné kritérium nebo více kritérií a oblastí, typicky určité kategorie.

Vzorec

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

Argumenty funkce

Oblast_součtu (povinný) – Oblast buněk, které mají být sečteny, pokud jsou splněna kritéria.

Oblast_kritérií1 (povinný) – Oblast kritérií, ve kterých se vyhodnocují podmínky.

Kritérium1 (povinný) – Daná podmínka, která musí být splněna v oblasti kritérií.

Oblast_kritérií2… Oblast_kritériíN (volitelný) – Pokud má být vyhodnoceno více kritérií, je nutné zadat další oblasti. Počet oblastí kritérií musí být roven počtu kritérií.

Kritérium2… KritériumN (volitelný) – Další kritérium, které vždy navazuje na danou oblast kritérií.

Praktické vysvětlení

Oblast součtu může být jenom jedna, jelikož výstupem je pouze jedna hodnota, proto není možné mít více oblastí součtu. Lze však mít více oblastí kritérií v případě, kdy je nutné zjistit součet na základě kritérií ve více sloupcích nebo více hodnot v jednom sloupci. Existuje i funkce Sumif, která má možnost sčítat pouze na základě jednoho kritérií v oblasti kritérií. Pomocí funkce Sumifs lze ale sčítat data na základě více kritérií, proto je vhodnější ji používat. Počet oblastí kritérií musí být shodná s počtem kritérií.

V sloupci „A“ se nachází jediná oblast kritérií, přičemž oblast součtu je v sloupci „B“. Kritérium se nachází v buňce „C2“, tedy nalezne se každý text „Auto“ v sloupci „A“ a k nim se sečtou příslušné hodnoty výnosů.

Nyní máme 2 kritéria, jejichž oblasti jsou v odlišných sloupcích. Kritérium „Auto“ je hledáno v sloupci „A“ a kritérium „Červená“ je hledáno v sloupci „B“ barva, přičemž oblast součtu je v sloupci „C“ výnosy. Je nutné vždy pár kritérium a oblast kritérií navázat na sebe. Obě kritéria musí být splněné, tedy v sloupci „A“ se musí najít „auto“ a zároveň v sloupci „B“ červená, jinak hodnota výnosů nebude sečtena. Proto výsledkem je 250 000, protože pouze 2 řádky splňují obě podmínky zároveň, a to řádek první (100 000) a poslední (150 000). Existuje i řádek, kde je napsáno auto, ale má modrou barvu. Stejně tak existuje řádek s červenou barvou, ale kategorií je motocykl, proto tyto řádky nejsou zahrnuty ve výpočtu.

Do funkce lze zadávat i matematické podmínky. Chtějme např. sečíst sloupec výnosy pouze v případě, kdy náklady budou vyšší než 4 000. V takovém případě můžeme matematický operátor „>4000“ zadat přímo do buňky, nebo přímo do argumentu funkce následovně, kdy se sečtou pouze hodnoty, kde je náklady 5 000 a 8 000, jelikož pouze tyto 2 řádky jsou vyšší než 5 000, přičemž jejich výnosy jsou 100 000 a 30 000, proto je výsledkem 130 000.

Použití s ostatními funkcemi

Funkci lze používat zejména ve výrokové logice, kdy chceme zjistit, zdali celkový součet na základě daného kritéria přesáhne určitou částku, pokud ano, budeme chtít vypsat bonus ve výši 20 000, jinak bude bonus 0, a to s kombinací funkce Když.

=KDYŽ(SUMIFS(A2:A6;B2:B6;C2)>100000;20000;0)

Funkce lze kombinovat s více funkcemi, ne pouze s matematickými. Pokud bychom měli např. v sloupci „A“ datumy, kdy chceme zjistit sumu ze sloupce „B“ pouze v případě, kdy je datum v sloupci „A“ až po dnešním dni, je možné využít kombinaci s funkcí Dnes, kdy je nutné dát do řetězce pouze znaménko větší nebo rovno a následně znakem & přidat danou funkci.

=SUMIFS(B2:B6;A2:A6;“>=“&DNES())

Pokud by funkce začala být nepřehledná z důvodu více oblastí kritérií, je možné definovat proměnné, které se do funkce Sumifs dosadí pomocí funkce Let.

=LET(data;B2:B6;podmínka;A2:A6;SUMIFS(data;podmínka;“auto“))