Generička formula
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
Sažetak
Da biste uvjetno zbrojili identične raspone koji postoje u zasebnim radnim listovima, sve u jednoj formuli, možete upotrijebiti funkciju SUMIF s INDIRECT, omotanu u SUMPRODUCT. U prikazanom primjeru formula u C9 je:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))
Obrazloženje
Podaci na svakom od tri lista koja se obrađuju izgledaju ovako:
Prije svega, imajte na umu da ne možete koristiti SUMIF-ove s "normalnom" 3D referencom poput ove:
Sheet1:Sheet3!D4:D5
Ovo je standardna "3D sintaksa", ali ako je pokušate koristiti sa SUMIF-om, dobit ćete pogrešku #VALUE. Dakle, za zaobilaženje ovog problema možete koristiti imenovani raspon "listova" koji navodi svaki list (karticu radnog lista) koji želite uključiti. Međutim, da bismo izgradili reference koje će Excel pravilno interpretirati, moramo povezati nazive listova u raspone s kojima trebamo raditi, a zatim upotrijebiti INDIRECT da bi ih Excel ispravno prepoznao.
Također, budući da imenovani raspon "listovi" sadrži više vrijednosti (tj. Njegov niz), rezultat SUMIF-a u ovom je slučaju također niz (koji se ponekad naziva i "rezultantni niz"). Dakle, mi koristimo SUMPRODUCT za obradu, budući da SUMPRODUCT ima mogućnost izvorne obrade nizova bez potrebe za Ctrl-Shift-Enter, kao i mnoge druge formule niza.
Drugi način
Gornji je primjer donekle složen. Drugi način rješavanja ovog problema je načiniti "lokalni" uvjetni zbroj na svakom listu, a zatim upotrijebiti redoviti 3D zbroj da biste zbrojili svaku vrijednost na kartici sažetka.
Da biste to učinili, dodajte formulu SUMIF na svaki list lista koji koristi ćeliju kriterija na listu sažetka. Tada kada promijenite kriterije, ažurirat će se sve povezane SUMIF formule.
