Jste zde: Úvodní stránka » excel » funkce » sumif-sumifs-funkce-excel

SUMIF SUMIFS funkce v Excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak na Excel - naučte se efektivně využívat Excel - nejoblíbenější (Hodnocení 97%, přes 13.000 studentů, + 750 pozitivních zpětných vazeb) - Získat kurz
MaxiKurz Excel - získejte lepší práci, více peněz, staňte se nepostradatelní (přes 6 hodin videí, 160 lekcí) - Získat kurz
G tabulky... - pokud nemáte rádi Excel využijte kurz na Google-tabulky

Jak sčítat hodnoty v tabulce splňující zadanou podmínku zadané podmínky. Podmínkou můžou být nejen hodnoty (číselné, textové), ale i intervaly (u hodnoty a číslo, datum), případně část(i) z textových hodnot.

Doplněno: 7.7.2015

Úvodem do funkcí SUMIF, SUMIFS

Jak na sčítání hodnot, které odpovídají požadovaným podmínkám. Například auta, která jsou na plyn, vyplacená mzda ženám z oddělení PR, atd. Včetně využití dynamických výběru.

Pro přehlednost je článek rozdělen na jednotlivé kapitoly:

Teorie, syntaxe
- SUMIF
- SUMIFS
SUMIF - vyplacená mzda - praktický příklad (mužům, ženám, IT oddělení, ...)
SUMIFS dvě podmínky - praktický příklad (zisk za prodané benzínová auta)
SUMIFS - rozsah podmínek - součet podle podmínky v požadovaném rozsahu
SUMIFS dynamický výběr zisk z dopravního prostředku (vše, auto, moto, loď, ...) a palivo (vše, benzín, nafta, plyn), využívá dynamický výběr
SUMIFS - jiný list zdrojová tabulka na jiném listu
SUMIFS -speciální znaky součet pro podmínku se speciálními znaky ~,*, ?, atd.
SUMIFS - rozdíl jak spočítat v rozsahu kriterií (větší a zároveň menší)
SUMIFS - datum jak spočítat dle kriterií datum
SUMIFS - datum měsíc Suma nad měsícem.
Sešit ke stažení - zdarma
Související články

SUMIF a SUMIFS trocha teorie

SUMIF

Sečte buňky ve vybrané oblasti podle zadaného kritéria.

Syntaxe

SUMIF(oblast;kritéria;součet)

Popis argumentů

oblast - vyhodnocovaná oblast buněk
kritéria- podmínka, která definuje sčítané buňky - může být "1", 1, "<1", ">1", "slovo"
součet- oblast ve které se sčítají buňky, pokud vyhovují kritériu v oblasti

Poznámka: SUMIF umožňuje vyhledávat i v nesouvislé oblasti, ale osobně si nedokáži představit praktické použití.

SUMIFS

Sečte buňky ve vybrané oblasti podle zadaných kritérií.

Syntaxe

SUMIFS(oblast_součtu, oblast_kritérií1, kriterium1 [oblast_kritérií2, kriterium2]...)

Popis argumentů

oblast_součtu - jedna nebo více buněk pro výpočet součtu, včetně čísel nebo názvů - Povinný argument
oblast_kritérií1 - První vyhodnocovaná oblast pro přidružené kritérium1 Povinný argument
kritérium1 Podmínka pro první oblast (vyjádřená číslem, výrazem, ...) Povinný argument
oblast_kritérií2 - Druhá vyhodnocovaná oblast pro přidružené kritérium2Nepovinný argument
kritérium2 - Podmínka pro druhou oblast_kritérií2, (vyjádřená číslem, výrazem, ...) Nepovinný argument

Poznámka: Max 127 kritérií.

Použití matematických operátoru

Ve funkcích SUMIF, SUMIFS lze pro kritérium použít i logické operátory:

= - rovná se
< - menší než
> - větší než
<= - menší nebo rovno
>= - větší nebo rovno
<> - různé od zadané hodnoty

Použití zástupných znaků

Při hledání textu lze použít i zástupné znaky (podobně jako když hledáte v databázích). V Excelu se používá hvězdička a otazník.

* - zastupuje libovolný řetězec
?- zastupuje právě jeden libovolný znak

Poznámka

Z vlastní zkušenosti doporučuji používat funkcí SUMIFS, protože suma součtu je u SUMIF na posledním místě a u SUMIFS na prvním místě, což se plete. A funkce SUMIFS dokáže spočítat sumu i pro podmínku z jedné oblasti.

Další alternativy jak spočítat hodnoty dle daných podmínek je možnost využít funkce SUBTOTAL a KDYŽ, případně vytvořit kontingenční tabulku.

SUMIF, SUMIFS - vyplacená mzda

Zdrojová tabulka z které budeme vybírat.

Máme tabulku zaměstnanců, kde jsou jejich jména, zda je muž/žena, na kterém oddělení pracuje a jeho mzda.

Použijeme funkce SUMIF a SUMIFS pro zjištění vyplacené mzdy (platu) pro muže:


=SUMIF(D16:D28;"muž";F16:F28)

=SUMIFS(F16:F28;D16:D28;"muž")

Podobně pro zjištění dalších informací:

Vyplacený plat pro ženy


=SUMIF(D16:D28;"žena";F16:F28)

=SUMIFS(F16:F28;D16:D28;"žena")

Vyplacený plat o oddělení IT


=SUMIF(E16:E28;"IT";F16:F28)

=SUMIFS(F16:F28;E16:E28;"IT")

Výsledek

Výsledek může vypadat například:

Soubor SUMIF - vyplacená mzda ke stažení zdarma (pro Excel 2007 a novější).

SUMIFS dvě podmínky

Předpokladem pro tento příklad je stejná zdrojová tabulka jako v předchozím příkladu.

Použijeme funkci SUMIFS pro zjištění vyplacené mzdy (platu) pro muže z oddělení IT a pro porovnání žen opět v oddělení IT:


=SUMIFS(F9:F21;E9:E21;"IT";D9:D21;"žena")

=SUMIFS(F9:F21;E9:E21;"IT";D9:D21;"muž")

Výsledek

Výsledek může vypadat například:

Soubor SUMIFS dvě podmínky ke stažení zdarma (pro Excel 2007 a novější).

SUMIFS - rozsah podmínek

Zdrojová tabulka (z prvního příkladu) je doplněna o sloupec věk (může být i datum narození atd.).

Využitím funkce SUMIFS zjistíme z tabulky:

Vyplacený plat pro osoby nad 48 let
Vyplacený plat pro osoby do 30 let

Funkce budou vypadat:


=SUMIFS(G9:G21;D9:D21;">48")

=SUMIFS(G9:G21;D9:D21;"<30")

Výsledek

Výsledek může vypadat například:

Rozsahy pokud je k dispozici hodnota

Pokud máte k dispozici hodnotu a rozsah (<,>) chcete mít natrvalo vložen, lze využít & nebo CONCATENATE. Předpoklad že v buňce A1 je požadované číslo (hodnota).


=SUMIFS(G9:G21;D9:D21;"<"&A1)

=SUMIFS(G9:G21;D9:D21;CONCATENATE("<";A1))

Ke stažení

Soubor SUMIFS - rozsah podmínek ke stažení zdarma (pro Excel 2007 a novější).

SUMIFS dynamický výběr

Základy máte za sebou, teď si trochu s tabulkami "pohrajeme" ať požadovaný součet se pouze vybere z rozbalovacího menu.

Zdrojem bude tabulka s dopravním prostředkem, palivem a ziskem.

Cílem bude vybírat dle paliva a dopravního prostředku a Excel (SUMIFS) nám vypočte zisk.

Řešení

Pro řešení je využito výběrového pole, funkce SVYHLEDAT a pro zajímavost jsem doplnil o podmíněné formátování.


=SUMIFS(E12:E33;C12:C33;O4;D12:D33;Q4)

Výsledek

Výsledek může vypadat například:

Soubor SUMIFS dynamický výběr ke stažení zdarma (pro Excel 2007 a novější).

SUMIFS - jiný list

Pokud bude zdrojová tabulka na jiném listě od Microsoft Excel 2007 již to Excel také zvládá. Jako zdrojovou tabulku použijeme úplně stejnou jako v předchozím příkladu (tj. Dopravní prostředky).

Stačí se jen odkazovat na daný list.


=SUMIFS(List1!E6:E27;List1!C6:C27;O3;List1!D6:D27;Q3)

Výsledek

Výsledek může vypadat například:

Soubor SUMIFS - zdrojová tabulka na jiném listu ke stažení zdarma (pro Excel 2007 a novější).

Další možnosti

Tabulku lze doplnit o výběr listu. Například na jednotlivých listech můžete mít měsíční mzdy. A jen si vyberete měsíc a již se spočte vyplacená suma na daném listě

SUMIFS - speciální znaky

Jak najít spočítat sumu pro buňky, které obsahují speciální znaky (*, ~, ?) jako hvězdička, vlnovka, otazník?

Pro zjištění daného znaku je potřeba použít znak vlnovku ~ a pak daný hledaný znak, například otazník, nebo hvězdička, případně i vlnovka. Další hvězdička, případně otazník opět představuje několik, nebo jeden znak (viz kapitola jak hledat texty).

Ukázka

Funkce


=SUMIFS(C10:C17;B10:B17;"*~**")	
=SUMIFS(C10:C17;B10:B17;"~*")	
=SUMIFS(C10:C17;B10:B17;"*~?*")	
=SUMIFS(C10:C17;B10:B17;"*~~*")

Výsledek ke stažení

Soubor SUMIFS - speciální znaky ke stažení zdarma (pro Excel 2007 a novější).

SUMIFS - rozdíl

Jak vyfiltrovat rozdílovou oblast, například lidé s věkem vězším než 26 let a menším než 55 let.

Ukázka

Soubor ke stažení

Zatím není veřejně k dispozici. Bude součásti sbírky zajímavých šablon ke stažení, která je v přípravě.

SUMIFS - datum

Jak najít spočítat sumu v oblasti součtu, pro buňky, které obsahují speciální datum (čas)?

Ukázka

Výsledek ke stažení

Soubor SUMIFS - datum ke stažení zdarma (pro Excel 2007 a novější).

SUMIFS - datum měsíc

Potřebuji sumu za jednotlivé měsíce. Využitím SUMIFS mohu porovnávat datum od do. NEb oli od prvního dne v daném měsící do posledného dne v daném měsící (aneb využít funkci EOMONTH).

Ukázka

SUMIFS - datum dle měsiců - prakticky v Excel


=SUMIFS($H$5:$H$15;$F$5:$F$15;">="&D19;$F$5:$F$15;"<="&EOMONTH(D19;0))

Ke stažení

Soubory ke stažení:

Další související články:

Další funkce, které souvisejí s funkci SUMIF, SUMFS

COUNTIF, COUNTIFS
SUMA
KDYŽ - logická funkce
SUBTOTAL
Kontingenční tabulka
DSUMA - databázová funkce

Závěrem

Využíváte funkci SUMIFS? Máte nějakou zajímavou ukázku použití? Použijte komentáře a pochlubte se.

Článek byl aktualizován: 19.09.2020 10:57

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

Komentáře

Jiří Beran

Přidáno: 21.12.14 23:27

Myslím, že jediný důvod, proč existuje SUMIF (která neumí nic, co by neuměla i SUMIFS), je historická. Zatímco SUMIF byla už dříve, SUMIFS přišla až v roce 2007. Ještě bych doplnil že alternativní funkcí k SUMIFS je kromě zmíněných také DSUMA (byť s trochu jinou logikou zápisu). DSUMA má proti SUMIFS jednu obrovskou výhodu, že umožňuje, aby více kritérií mělo mezi sebou vztah "nebo" (nejenom "a"), což je někdy ou SUMIFSu dost omezující.

Pavel Lasák

Přidáno: 22.12.14 12:49

To Jiří Beran: Děkuji za doplnění. DSUMA doplním, tu jsem nechtěně vynechal, mám ještě v plánu doplnit článek o pár dalších ukázek použití. Holt včera se mi už nechtělo :(

Petr Pecháček

Přidáno: 22.12.14 22:55

Tak ono by se slušelo uvádět i ekvivalentní možnosti, nematicové i maticové - SUMA(KDYŽ...)), SUMA((...)*(...)*...), SOUČIN.SKALÁRNÍ(...). Jinak je pravda že tzv. "databázové" funkce Dxxxx nikdo moc nepoužívá. Sám na ně zapomínám.

Pavel Lasák

Přidáno: 23.12.14 18:11

To Petr Pecháček: Díky za další nápady, doplním do příslušných článku o SUMA, SOUČIN.SKALÁRNÍ, KDYŽ.

Jiří Beran

Přidáno: 29.12.14 18:35

Ještě jedna drobnost. Funkce SUMIF přeci jen umí jednu věc, kteoru neumí SUMIFS. A sice používat nestejně dlouhé oblasti v oblasti kritérií a součtů. Ale nemyslím že by to byla vlastnost až tak klíčová :)

Pavel Lasák

Přidáno: 03.01.15 09:22

To Jiří Beran: U použití nestejně dlouhých oblastí zatím nevidím praktické využití. Ale je pravda, že tuto "klíčovou" vlastnost jsem přehlédl.

Antonín Sedláček

Přidáno: 16.01.15 10:14

Existuje nějaká alternativa k funkci SUMIF, která by dokázala sumovat čísla z více sloupců? (Např. mám v jednotlivých sloupcích údaje po měsících a potřebuji výběry se součty za čtvrtletí a rok.)

Jojo

Přidáno: 19.01.15 07:26

To Antonín Sedláček: -- buď použiť pomocné stĺpce so súčtami pre štvrťroky, alebo SUMIF+SUMIF+SUMIF pre tri stĺpce

Pavel Lasák

Přidáno: 22.01.15 19:49

To Antonín Sedláček: Nelze využít kontingenčních tabulek?

Antonín Sedláček

Přidáno: 24.01.15 19:06

Děkuji za odpovědi. Vyzkouším.

Petr Stolař

Přidáno: 16.06.15 09:48

Prosím o radu, pravděpodobně s nastavením. Mám problém s fcemi SUMIF a SUMIFS (ale i s COUNTIF a COUNTIFS) v případě, že chci součet hodnot (počet hodnot) jiných než hodnota v konkrétní buňce. Např. vzorec =SUMIFS(A1:A50;B1:B50;">C1") nesečte buňky, které mají větší hodnotu než C1, ale snaží se sečíst buňky, které mají(!) hodnotu >C1. Jedná se konkrétní instalaci MS Excel 2010. Zkoušel jsem i obnovu nastavení národního prostředí.

Petr Stolař

Přidáno: 16.06.15 12:30

Beru zpět. Pomohl jsem si takto ...;">"&C1;... Díky

Petr Kromíchal

Přidáno: 02.07.15 15:03

Dobrý den pane Lašák, dovoluji se na Vás obrátit s dotazem ohledně funkce sumif. potřeboval bych pomocí této funkce sečíst předpis splátek úvěru v období od xy k dnešnímu datu, kdy pro určení dnešního data používám funkci DNES. Nemohl by jste mi prosím pomoci? S pozdravem Petr K.

Pavel Lasák

Přidáno: 07.07.15 19:47

To Petr Kromíchal: Jak na datum ve funkcích SUMIFS, SUMIF jsem doplnil do článku.

Petr Kromíchal

Přidáno: 08.07.15 22:22

Děkuji mockrát, už jsem mi to podařilo také vypátrat. Hezký den.

Petr Burda

Přidáno: 02.08.15 11:10

Dobrý den, jsem naprostý začátečník a laik a proto požádám o polopatickou odpověď. Ve cvičném zadání mám sečíst hodnoty ze tří různých listů (A,B,C), kde se v každém nachází buňka s hodnotou např. denní tržba - řekněme A1, B1, C1. Na list D mám provést do samostatné buňky součet do buňky D1. Můj (neúspěšný) postup je tento : do buňky D1 si nastavím tento vzorec =SUMIF('1.týden'!I5,+'2.týden'!I4,+'3.týden'!I4) ale vůbec nevím, co mám zadat do kritéria. Ať napíšu co napíšu (např. 1), vždy je výsledek, že napsaný vzorec obsahuje chybu. Tedy, co má kriterium vyjadřovat? Má mít nějakou přesně definovanou podobu a tvar? Děkuji

B Jarous

Přidáno: 12.08.15 11:36

Dobrý den, chci se zeptat, jestli existuje podobná funkce i pro násobení. Díky BJ

Pavel Lasák

Přidáno: 17.10.15 16:45

To Petr Burda: Provést klasický součet =A!A1+=B!A1+=C!A1 PRo začátečníka doporučuji začít článkem Excel 2010 krok za krokem a pak pokračovat On-line kurz Excelu nebo si u mě objednat školení ;)

Pavel Lasák

Přidáno: 17.10.15 16:57

To B Jarous: Existuje SOUČIN.SKALÁRNÍ více v samostatném článku: Funkce SOUČIN.SKALÁRNÍ (SUMPRODUCT) v Excel

Daniel Dědek

Přidáno: 19.10.15 14:51

Dobrý den, chtěl bych se zeptat zda je možné sečíst jen čísla ve sloupci, pokud buňky obsahují i znak např.: S, 100.

Pavel Lasák

Přidáno: 26.01.16 08:58

To Daniel Dědek: Záleží jak je hodnota vložena, pokud jako text, tak využít textové funkce ZPRAVA, ZLEVA, ČÁST a získat dané číslo. Pak již je možno sčítat. Pokud je hodnota vložena jako číslo s využití vlastního formátu, pak lze sčítat přímo.

Vladimír

Přidáno: 23.02.16 13:37

Dobrý den, zde SUMIF využít pro součet hodnot barevně označených? Například jen text označený červeně (nebo celé pole buňky)?

Jirka

Přidáno: 25.02.16 10:44

mfg. Tabulka sloupce roky a řádky hodnoty podle kriterií min,nad,am....V jiné tabulce jsou jednotlivé bunky s =SVYHLEDAT(J$13;W$3:DC$1000;5;0), což mi vyhledává hodnotu za příslušný rok J$13. Všechny hodnoty v bunkách mám sečteny pomocí =SUMIF(A3:A78;"=min";C3:C78). Bylo by možné sjednotit vzorečky v jeden, abych měl součet podle kriteria roku a min.

Vítek

Přidáno: 10.03.16 09:26

Zdravím, marně zatím přemýšlím, jak získat součet buněk na základě více podmínek a současně ve více sloupcích. Konkrétně - mám v v jednom řádku (výrobní výkaz) 10 lidí (10 sloupců, souvisle, OS. ČÍSLO), kteří se na výrobě podíleli a jejich PRÉMIE (10 sloupců, souvisle). Potřebuji udělat tabulku, kde v řádcích budou jedinečná os.čísla zaměstnanců, ve sloupcích kombinace DEN+SMĚNA (ranní, noční) a v hodnotě buňky pak bude nějaká SUMA PRÉMIÍ při současném splnění podmínek (OS.ČÍSLO;DEN;SMĚNA), přičemž OS.ČÍSLO a jeho PRÉMIE není vždy ve stejném z 10 sloupců... může být ve kterémkoliv z nich (např. u jednoho výrobku je pracovník 1111 uveden jako 6/10 a jeho prémie v(6/10), jindy může být v 2/10 a prémie v P2/10) a potřebuji do sumy hledat ve všech 10. Zatím mně napadlo pouze otrocky zopakovat 10x sumifs pro sloupce 1/10, 2/10, 3/10.... ale jde to i jinak? =SUMIFS($BE:$BE;$AK:$AK;A8;$D:$D;F$1;$E:$E;F$2) ...KDE $BE$ je první z 10 sloupců pro prémie, $AK je první sloupec pro osobní čísla, $A8 je konkrétní osobní číslo které mě pro výstup zajímá, $D je sloupec pro den, $F$1 je hodnota konkrétního dne, $E je sloupec pro směnnost (ráno/noc) a $F$2 pak konkrétní zkoumaná směna

Hana

Přidáno: 07.04.16 14:25

Dobrý den, lze do kritéria SUMIFS zadat rozsah čísel v daném sloupci A. Ve sloupci A jsou čísla od 1 do 100 a potřebuji vysčítat pouze ty řádky, kde jsou ve sloupci A čísla větší jak 10 a menší než 20. Děkuji

Jarda

Přidáno: 20.04.16 14:46

to Hana Lze to zadat jak požadujete. =SUMIFS(A:A;A:A;">10";A:A;"<20")

David

Přidáno: 12.05.16 21:26

Dobrý den, řeším problém, jak v daném řádku nebo sloupci sečíst hodnoty buněk pouze určité barvy. Nevím, jak zadat do kriteria tu barvu pozadí..? Dá se to udělat pomocí SUMIFS? Děkuji za radu.

Pavel Lasak

Přidáno: 30.06.16 16:51

To David: Nutno využít VBA makra a napsat si vlastní funkci využitím například ColorIndex.

Využít SUBTOTAL a filtry podle barev - pracnější

Silva

Přidáno: 11.08.16 14:05

Dobrý den, s kolegyní jsme před lety daly dohromady takový mzdový prográmek v excelu, podklad pro další zpracování pro účetní. Funguje výborně. Malá stavební firma, několik zakázek, několik zedníků. Každá zakázka má svůj list, kam se napíšou hodiny, kolik tam odpracoval zedník, na základě hodinové sazby se do dalšího sloupce znásobí mzda na té zakázce. Pak je sešit, kde jsou všichni zedníci pod sebou a sloupce se zakázkami, takže dole je součet mezd všech na zakázce. A na straně zase mzda zedníka ze všech zakázek. A nějaké další funkce. Aby se to do toho listu přelilo, máme to zadané přes sumif. Ve sloupci se zakázkou je vzorec sumif - vezme se oblast z listu se zakázkou, podmínka je jméno a pod podmínkou vyhledá excel tu mzdu, kterou dá do toho souhrnného listu. Jenže - někdy, naštěstí výjimečně - se stane, že sumif vyhodí u někoho najednou nulu. A když zadám vzorec znovu, úplně stejně, tak nic. Prostě jako kdyby v té buňce bydlel nějaký zlý skřítek. Ani jedné nám to není jasné, zkusila jsem zrušit celý řádek, zadat vše znovu ...... a pořád stejně. Netušíte, čím to může být? Snad to píšu aspoň trochu srozumitelně.