Přeskočit navigaci | Přeskočit na novinky

Nový videokurz na Seduo: Excel pro začátečníky

     

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

SUMIF SUMIFS funkce v Excel


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.

SUMIF, SUMIFS

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

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

MS Excel 2010 - Úvodní obrazovka

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:

SUMIF, SUMIFS - vyplacená mzda

Soubor SUMIF - vyplacená mzda soubor ve formátu *.xlsx 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:

SUMIFS dvě podmínky

Soubor SUMIFS dvě podmínky soubor ve formátu *.xlsx 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:

SUMIFS - rozsah podmínek

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 soubor ve formátu *.xlsx ke stažení zdarma (pro Excel 2007 a novější).

SUMIFS dynamický výběr

SUMIFS - zdroj

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:

SUMIFS - dynamický výběr

Soubor SUMIFS dynamický výběr soubor ve formátu *.xlsx 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:

SUMIFS - rozsah podmínek

Soubor SUMIFS - zdrojová tabulka na jiném listu soubor ve formátu *.xlsx 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

SUMIFS - speciální znaky

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 soubor ve formátu *.xlsx 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

SUMIFS - rozdíl

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

SUMIFS - datum prakticky v Excel

Výsledek ke stažení

Soubor SUMIFS - datum soubor ve formátu *.xlsx ke stažení zdarma (pro Excel 2007 a novější).

Ke stažení

SUMIFS - funkce EXCEL - stahuj logo

Soubory ke stažení:


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

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

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: 26.01.2016 09:08

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

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, PowerPoint) se věnuji od roku 2006. Své vědomosti a zkušenosti dávám k dispozici na různých školeních a konzultacích, ale také na tomto webu. K dispozici na tomto webu je mnoho návodu, tipů a triků včetně desítek různých šablon.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil

Doporučte tento článek přátelům

Pokud vám článek pomohl, případně si myslíte, že může pomoci i někomu dalšímu, budu rád když jej sdílením doporučíte přátelům - děkuji:



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ě.


Přidání nového komentáře

Poznámka: Z časových důvodu není v mých sílách zodpovědět všechny dotazy. Děkuji za pochopení.




(vlož číslo padesátpět)

Z bezpečnostních důvodu dočasně nemůžete použít HTML značky, kromě <code> a </code> (pro vkládání VBA kódu). Děkuji za pochopení.
Děkuji, za Vaše komentáře.

Za obsah komentářů neodpovídám, jelikož jsou komentáře publikovány ihned po jejich napsání čtenářem. Toto nemohu nijak ovlivnit. Přesto si vyhrazuji možnost jakýkoli neslušný komentář smazat bez udání důvodu. V komentářích si tykáme. ;-)





Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Newsletter

Přihlaste se odběru novinek a získejte:
Ebook zdarma -
10 kroků ke zvládnutí (Word, Excel, PowerPoint)
Šipka Kniha 3D Více o ebooku ...

TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2016 | 25770

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.