Přejít k hlavnímu obsahu

Sumif s více kritérii založenými na logice OR a AND

Při práci na listech aplikace Excel může být nutné sečíst hodnoty na základě více kritérií. Někdy je více kritérií ze stejného sloupce (logika NEBO), ale někdy z různých sloupců (logika AND). Jak byste se v tomto případě mohli vypořádat s tímto úkolem v Excelu?


Sumif s více kritérii založenými na logice NEBO

Předpokládejme, že máte seznam produktů, který obsahuje dodavatele a množství, jak je uvedeno níže, nyní chcete získat všechna celková množství, která dodává dodavatel John a David. Zde pro vás představím dva jednoduché vzorce.

Pomocí vzorce SUMIF + SUMIF +…

Pokud chcete sečíst čísla, která splňují některé z kritérií (OR) z více kritérií, můžete sečíst několik funkcí SUMIF do jednoho vzorce, obecná syntaxe je:

=SUMIF(criteria_range, criteria1, sum_range)+SUMIF(criteria_range, criteria2, sum_range)+…
  • criteria_range: Rozsah buněk, které mají odpovídat kritériím;
  • criteria1: První kritérium, které se používá k určení, které buňky se mají sečíst;
  • criteria2: Druhé kritérium, které se používá k určení, které buňky se mají sečíst;
  • sum_range: Rozsah buněk, ze kterých chcete sčítat.

Nyní prosím zkopírujte nebo zadejte některý z níže uvedených vzorců do prázdné buňky a stiskněte vstoupit klíč k získání výsledku:

=SUMIF(B4:B14,"John",C4:C14) + SUMIF(B4:B14,"David",C4:C14)           (Type the criteria manually)
=SUMIF(B4:B14,E4,C4:C14) + SUMIF(B4:B14,E5,C4:C14)                           
(Use a cell reference)


Vysvětlení vzorce:

=SUMIF(B4:B14,"John",C4:C14) + SUMIF(B4:B14,"David",C4:C14)

  • První SUMIF(B4:B14,"Jan";C4:C14) najde řádky Jana a sečte celková množství;
  • Druhý SUMIF(B4:B14,"David";C4:C14) najde řádky Davida a sečte celková množství;
  • Poté sečtěte tyto dva vzorce SUMIF, abyste získali všechna celková množství dodaná jak Johnem, tak Davidem.

Pomocí funkcí SUM a SUMIF

Výše uvedený vzorec se velmi snadno používá, pokud existuje pouze několik kritérií, ale pokud chcete sečíst hodnoty s více podmínkami NEBO, může být výše uvedený vzorec nadbytečný. V tomto případě vám může pomoci lepší vzorec, který je vytvořen na základě funkcí SUM a SUMIF. Obecné syntaxe jsou:

Obecný vzorec s pevně zakódovaným textem:

=SUM(SUMIF(criteria_range, {criteria1,criteria2,...}, sum_range))
  • criteria_range: Rozsah buněk, které mají odpovídat kritériím;
  • criteria1: První kritérium, které se používá k určení, které buňky se mají sečíst;
  • criteria2: Druhé kritérium, které se používá k určení, které buňky se mají sečíst;
  • sum_range: Rozsah buněk, ze kterých chcete sčítat.

Obecný vzorec s odkazy na buňky:

{=SUM(SUMIF(criteria_range, criteria_cells, sum_range))}
Array formula, should press Ctrl + Shift + Enter keys together.
  • criteria_range: Rozsah buněk, které mají odpovídat kritériím;
  • criteria_cells: Buňky obsahující kritéria, která chcete použít;
  • sum_range: Rozsah buněk, ze kterých chcete sčítat.

Zadejte nebo zkopírujte některý z níže uvedených vzorců, které potřebujete, do prázdné buňky a poté získejte výsledek:

=SUM(SUMIF(B4:B14, {"John","David"}, C4:C14))           (Type the criteria manually)
=SUM(SUMIF(B4:B14, E4:E5, C4:C14))                             
(Use cell references, array formula, should press Ctrl + Shift + Enter keys)


Vysvětlení vzorce:

=SUM(SUMIF(B4:B14; {"Jan","David"}; C4:C14))

> SUMIF(B4:B14; {"John","David"}; C4:C14):

  • {"Jan","David"}: Konstanta pole, která je sadou více kritérií poskytovaných ve složených závorkách.
  • SUMIF(B4:B14,"David";C4:C14) Konstanta pole používající logiku OR nutí funkci SUMIF sčítat čísla v C4:C14 na základě jednoho z více kritérií ("John" a "David") a vrátí dva samostatné výsledky: {410,1520}.

> SUM(SUMIF(B4:B14, {"Jan","David"}, C4:C14))=SUM({410,1520}): Nakonec tato funkce SUM sečte tyto výsledky pole a vrátí výsledek: 1930.


Sumif s více kritérii založenými na logice AND pomocí funkce SUMIFS

Pokud chcete sečíst hodnoty s více kritérii v různých sloupcích, můžete tento úkol rychle vyřešit pomocí funkce SUMIF. Obecná syntaxe je:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • sum_range: Rozsah buněk, ze kterých chcete sčítat;
  • criteria_range1: Rozsah, kde je použito kritérium1;
  • criteria1: První kritérium, které se kontroluje podle rozsahu_kritérií1 a určuje, které buňky se mají přidat; (typ kritérií může být: číslo, logický výraz, odkaz na buňku, text, datum nebo jiná funkce Excelu.)
  • criteria_range2, criteria2…: Další dodatečné rozsahy a související kritéria. (ve vzorci SUMIFS můžete nastavit 127 párů kritérií_rozsah a kritéria.)

Řekněme, že mám tabulku se sloupci Produkt, Dodavatel, Množství a Cena, jak je uvedeno níže. Nyní chci zjistit součet celkové ceny produktu Apple a množství, které je větší nebo rovno 200.

Aplikujte prosím některý z níže uvedených vzorců do prázdné buňky a stiskněte vstoupit klíč k vrácení výsledku:

=SUMIFS(D4:D14,A4:A14,"Apple",C4:C14,">=200")           (Type the criteria manually)
=SUMIFS(C4:C14,A4:A14,G3,C4:C14,G4)                           
(Use cell references)


Vysvětlení vzorce:

=SUMIFS(D4:D14,A4:A14,"Apple",C4:C14,">=200")

  • Rozsah A4:A14 kontroluje produkt „Apple“ a rozsah C4:C14 extrahuje všechna množství větší nebo rovna 200, poté funkce SUMIFS sečte pouze odpovídající buňky z rozsahu D4:D14.

Použitá relativní funkce:

  • SUMIF:
  • Funkce SUMIF může pomoci sečíst buňky na základě jednoho kritéria.
  • SUMIFS:
  • Funkce SUMIFS v Excelu může pomoci sečíst hodnoty buněk na základě více kritérií.

Další články:

  • Sečíst, pokud buňky obsahují konkrétní text v jiném sloupci
  • Tento tutoriál vám ukáže, jak sečíst hodnoty, pokud buňky obsahují konkrétní nebo částečný text v jiném sloupci. Vezměte si níže uvedený rozsah dat jako příklad, abyste získali celkové množství produktů obsahujících text „T-Shirt“, s tímto úkolem v Excelu si poradí jak funkce SUMIF, tak funkce SUMPRODUCT.
  • Součet nejmenších nebo dolních hodnot N v aplikaci Excel
  • V Excelu je pro nás snadné sečíst rozsah buněk pomocí funkce SUM. Někdy může být nutné sečíst nejmenší nebo dolní 3, 5 nebo n čísel v datovém rozsahu, jak je znázorněno na obrázku níže. V tomto případě vám SUMPRODUCT spolu s funkcí SMALL může pomoci vyřešit tento problém v Excelu.

Nejlepší kancelářské nástroje produktivity

Kutools pro Excel - pomůže vám vyniknout před davem

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty  |  Odstranit prázdné řádky  |  Kombinujte sloupce nebo buňky bez ztráty dat  |  Kolo bez vzorce ...
Super VLookup: Více kritérií  |  Vícenásobná hodnota  |  Přes Multi-Sheets  |  Fuzzy vyhledávání...
Adv. Rozbalovací seznam: Snadno rozevírací seznam  |  Závislý rozbalovací seznam  |  Vícenásobný výběr rozevíracího seznamu...
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  Porovnejte sloupce s Vyberte stejné a různé buňky ...
Doporučené funkce: Zaměření mřížky  |  Návrhové zobrazení  |  Velký Formula Bar  |  Správce sešitů a listů | Knihovna zdrojů (Automatický text)  |  Výběr data  |  Zkombinujte pracovní listy  |  Šifrovat/dešifrovat buňky  |  Odesílat e-maily podle seznamu  |  Super filtr  |  Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky ...)  |  50+ Graf Typ nemovitosti (Ganttův diagram ...)  |  40+ Praktické Vzorce (Vypočítejte věk na základě narozenin ...)  |  19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty ...)  |  12 Konverze Tools (Čísla na slova, Přepočet měny ...)  |  7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky Excelu ...)  |  ... a více

Kutools pro Excel se může pochlubit více než 300 funkcemi, Zajištění toho, že to, co potřebujete, je jen jedno kliknutí...


Záložka Office - Povolte čtení a úpravy na záložkách v Microsoft Office (včetně Excelu)

  • Jednu sekundu přepnete mezi desítkami otevřených dokumentů!
  • Snižte stovky kliknutí myší každý den, sbohem s myší rukou.
  • Zvyšuje vaši produktivitu o 50% při prohlížení a úpravách více dokumentů.
  • Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Edge a Firefox.
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
do you guys know if this logic works on google sheets?
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations