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

Jste zde: Úvodní stránka » excel » funkce-matematicke » SUMIF-SUMIF-soucet-dle-podminky-Excel
Microsoft Excel logo

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

Videokurzy Excel

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

Jak sčítat hodnoty v tabulce splňující zadanou podmínku Podmínka můžou mít různého typu (číselné, textové), ale i intervaly (u hodnoty a číslo, datum), případně část textu.

Osobně doporučuji raději funkci SUMIFS.

Ú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:


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
Excel funkce SUMIF - syntaxe argumenty

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.

SUMIF Excel - suma na základě podmínky ukázka

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

SUMIFS - rozsah podmínek

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) SUMIF - texty
SUMIF - texty speciální znaky

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 - speciální znaky

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

SUMIF - spojení jiné funkce

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)} SUMIF - chyby

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) Excel funkce SUMIF - ukázka VBA

Ke stažení

SUMIF - 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 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: 08.08.2018 17:35

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

Pavel Lasák

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

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


Komentáře zatím nejsou

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






Excel


Sdílejte

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

Nové články


Reklama


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 - 2019 | 2327

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