Tweet

SUMIF (SUMIF) - součet dle podmínky v Excel

Úvodem do funkce SUMIF

Jak získat součet hodnot, které splňují požadovanou podmínku v Excel, aneb praktické využití funkce SUMIF SUM - jako suma a IF jako podmínka (v angličtině je název funkce SUMIF), jednak teorie, základní použití v listu, praktické příklady, možné komplikace, tipy, triky až po využití funkce ve VBA .

Pro přehlednost je rozdělen na kapitoly:

• Teorie použití SUMIF - syntaxe
• Základní použití SUMIF - pevná podmínka
• Pokročilé použití SUMIF - rozsah podmínky
• SUMIF - podmínka text
• SUMIF - speciální znaky - hvězdičky, otazníky, vlnovky
• SUMIF - ve spojení s ostatními funkcemi
  • CONCATENATE
  • DATUM, DNES
• Chyby v SUMIF
• Použití s funkce SUMIF ve VBA
• Související články
• Ke stažení

SUMIF syntaxe a trocha teorie

Funkce sečte hodnoty v oblasti na základě podmínky. Například chci plat pro ženy/muže, nebo plat pro osoby starší 20 let, atd.

Syntaxe česky:

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

Syntaxe anglicky:

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

Popis argumentů:

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

Použití operátoru

Ve funkcích SUMIF, potažmo SUMIFS, COUNTIF 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 naopak používat i zástupné znaky (podobně jako když hledáte v databázích, nebo ve filtrech). V Excelu se používá hvězdička a otazník.

• * - zastupuje libovolný řetězec
• ? - zastupuje právě jeden libovolný znak
• ~ - znak za vlnovkou ztrÁcí speciální funkci, platí pro *, ? a ~ neboli pokud hledáte i tyto znaky

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. Navíc funkce SUMIFS dokáže spočítat sumu nejen pro jednu podmínku, ale pro více nezávislých podmínek.

Další alternativy jak spočítat hodnoty dle daných podmínek je možnost využít funkce SUBTOTAL, KDYŽ, databázové funkce, využít maticový zápis funkcí, vytvořit kontingenční tabulku, filtry, VBA. Odkazy na články jsou v poslední kapitole.

SUMIF základ - vyplacená mzda

Máte tabulku zaměstnanců, kde jsou jejich jména, informace o pohlaví, zda je muž/žena, na kterém oddělení pracuje, jeho věk a jeho mzda. Potřebuji zjistit vyplacený plat - sumu vyplacených finančních prostředku pro muže a pro ženy.

Vyřešit lze jednoduše, pomocí funkce SUMIF.

• Vyplacený plat pro ženy

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

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

• Vyplacený plat pro ženy

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

Nemusíte hledat jen podle pohlaví, lze i podle oddělení, atd.

• Vyplacený plat o oddělení IT

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

Nebo jen součet platu větších než 10 - podmínka je ve stejném sloupci, ve kterém se provádí součet

=SUMIF(F12:F24;">10") =SUMIF(F12:F24;">10";F12:F24)

Poznámka, pokud sčítám číselné hodnoty, nad kterými mám podmínku, nemusím znova opakovat argument součet, ale když zopakuji nic se neděje.

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 SUMIF zjistíme z tabulky:

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

Funkce budou vypadat:

=SUMIF(D16:D28;">48";G16:G28) =SUMIF(D16:D28;"<30";G16:G28)

Mohu se odkázat na číslo a operátor mít ve funkci, nebo spojit operátor a funkci.

=SUMIF(D16:D28;"<"&D9;G16:G28) =SUMIF(D16:D28;F9&D9;G16:G28) =SUMIF(D16:D28;G9;G16:G28) =SUMIF(D16:D28;CONCATENATE(F9;D9);G16:G28)

Výsledek

Poznámka

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

SUMIF - hledat text

Jak hledat slova, texty, ale nejen přesnou shodu, ale i slova obsahující část slova, například pro sto > sto, stokoruna, korun sto, atd.

Budeme hledat jen slova, která neobsahují speciální znaky, aneb respektující že do popisu názvu, výrobku a jmen se nezadávají speciální znaky jako hvězdičky, otazníky, vlnovky.

Jak na tyto speciální znaky jsem popsal v následující kapitole, abych vás neochudil ;) o trik s vlnovkou ~ .

Začneme klasickým slovem - to už umíte.

=SUMIF(C16:C26;"auto";E16:E26)

Samozřejmě mohu přes buňku a využívat konkrétní slovo, nebo slova obsahující hledáne slovo, nebo slovo a znak (několik znaků pevně danných) které mohou být různé. Pak je potřeba využít hvězdičku, otazník. hvězdička jakykoliv počet, otazník přesný počet, jeden otazník jeden znak.

=SUMIF(C16:C26;"auto*";E16:E26) =SUMIF(C16:C26;"*auto*";E16:E26) =SUMIF(C16:C26;"loď ?";E16:E26) =SUMIF(C16:C26;"loď ??";E16:E26)

Hlkedané slovo může být i v buňce.

=SUMIF(C14:C24;C5;E14:E24) =SUMIF(C14:C24;C8;E14:E24) =SUMIF(C14:C24;"*"&C9&"*";E14:E24)

Poznámka, Excel v základu nezvládá regulární výrazy, ale ve VBA ano. Ale to již nechávám čtenáři k samodostudování, nebo se zmíním v některém z dalších článků ;)

SUMIF - speciální znaky

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

klasické znaky se hledají dobře, jen u dvou znaku je problém otazník a hvězdička, protože otazník představuje právě jeden hledaný znak a hvězdička několik znaků.

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.

Funkce

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

Ukázka

SUMIF - ve spojení s ostatními funkcemi

Podmínku poskládat využitím funkce CONCATENATE, znáte z předchozí kapitoly.

=SUMIF(D16:D28;CONCATENATE(F9;D9);G16:G28)

Spojení dvou funkcí, kdy chci sečíst lodě a auta a nemohu použít SUMIFS - starší Excel.

=SUMIF(C16:C26;"auto";E16:E26)+=SUMIF(C16:C26;"lod";E16:E26)

Datum, mohu využít funkce pro dnešní den, nebo datum poskládat

=SUMIF(D12:D17;DNES();G12:G17) =SUMIF(D12:D17;DATUM(F7;E7;D7);G12:G17) .. případně ve spojení s operátory =SUMIF(D12:D17;">="&DATUM(F7;E7;D7);G12:G17)

Další ukázky v přípravě

Ukázka

Chyby v SUMIF

Oblasti nestejně velké

=SUMIF(D5:D9;"muž";F5:F10) oblasti posunuty =SUMIF(D5:D9;"muž";F6:F10)

v argumentu součet nejsou čísla

=SUMIF(D5:D10;"muž";E5:E10)

sčítám logické hodnoty

=SUMIF(D5:D10;"muž";G5:G10)

Problém je i v maticovém zápisu. Pokud chCete používat maticově použijte SUMA a KDYŽ.

{=SUMIF(D9:D14;F4:F5;F9:F14)}

Použití s funkce SUMIF ve VBA

Při zadávání do buňky lze využít Formula nebo FormulaLocal.

Range("C12").Formula = "=SUMIF(D5:D1,""muž"",F5:F10)" Range("C13").FormulaLocal = "=SUMIF(D5:D10;""muž"";F5:F10)"

Nebo využít Application s WorksheetFunction.

hodnota = Application.WorksheetFunction.SumIf( _ Range("D5:D10"), _ "muž", _ Range("F5:F10")) MsgBox (hodnota)

využít i pojmenování argumentu a lze i přehazovat ;)

hodnota = Application.WorksheetFunction.SumIf( _ Arg1:=Range("D5:D10"), _ Arg2:="muž", _ Arg3:=Range("F5:F10")) MsgBox (hodnota)

Ke stažení

Soubory ke stažení:

• SUMIF - vyplacená mzda
• další ukázky v přípravě

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 SUMIF nebo máte stejně jako já raději 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:59

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 zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.