Všeobecný vzorec
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
Zhrnutie
Ak chcete podmienene sčítať rovnaké rozsahy, ktoré existujú v samostatných pracovných hárkoch, všetko v jednom vzorci, môžete použiť funkciu SUMIF s parametrom INDIRECT zabaleným do SUMPRODUCT. V zobrazenom príklade je vzorec v C9:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))
Vysvetlenie
Údaje na každom z troch spracovávaných listov vyzerajú takto:
Najskôr si uvedomte, že SUMIF nemôžete používať s „normálnym“ 3D odkazom, ako je tento:
Sheet1:Sheet3!D4:D5
Toto je štandardná „3D syntax“, ale ak sa ju pokúsite použiť s programom SUMIF, zobrazí sa chyba #HODNOTA. Na riešenie tohto problému teda môžete použiť pomenovaný rozsah „hárky“, ktoré obsahujú zoznam všetkých hárkov (karta hárka), ktoré chcete zahrnúť. Ak však chceme vytvoriť odkazy, ktoré bude Excel správne interpretovať, musíme zreťaziť názvy hárkov do rozsahov, s ktorými musíme pracovať, a potom pomocou príkazu NEPRIAME, aby ich Excel správne rozpoznal.
Pretože pomenovaný rozsah „sheets“ obsahuje viac hodnôt (tj. Jeho pole), výsledkom je v tomto prípade SUMIF aj pole (niekedy nazývané „výsledné pole“). Na jeho spracovanie teda používame SUMPRODUCT, pretože SUMPRODUCT má schopnosť natívne manipulovať s poľami bez potreby Ctrl-Shift-Enter, ako mnoho iných vzorcov poľa.
Inač
Vyššie uvedený príklad je trochu komplikovaný. Ďalším spôsobom, ako tento problém vyriešiť, je urobiť „miestny“ podmienený súčet na každom hárku a potom použiť bežný 3D súčet na sčítanie každej hodnoty na karte súhrnu.
Za týmto účelom pridajte vzorec SUMIF do každého hárka hárka, ktorý používa na súhrnnom hárku bunku kritérií. Keď potom zmeníte kritériá, aktualizujú sa všetky prepojené vzorce SUMIF.
