Üldine valem
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
Kokkuvõte
Eraldi töölehtedes eksisteerivate identsete vahemike tingimuslikuks summeerimiseks kõik ühes valemis saate kasutada funktsiooni SUMIF koos INDIRECT-iga, mis on pakitud SUMPRODUCT-i. Näidatud näites on valem C9-s järgmine:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))
Selgitus
Kõigi kolme töödeldava lehe andmed näevad välja järgmised:
Kõigepealt pange tähele, et SUMIF-e ei saa kasutada sellise tavalise 3D-viitega:
Sheet1:Sheet3!D4:D5
See on tavaline 3D-süntaks, kuid kui proovite seda kasutada SUMIF-iga, saate vea #VALUE. Niisiis saate selle probleemi lahendamiseks kasutada nimega vahemikku "lehed", kus on loetletud kõik lehed (töölehe vahekaart), mille soovite lisada. Kuid selleks, et luua viiteid, mida Excel õigesti tõlgendab, peame liitma lehtede nimed vahemikega, millega peame töötama, ja seejärel kasutama INDIRECT-i, et Excelis neid õigesti ära tunda.
Kuna nimega vahemik "lehed" sisaldab mitut väärtust (st selle massiivi), on SUMIFi tulemus sel juhul ka massiiv (mida nimetatakse mõnikord ka "tulemuseks olevaks massiiviks"). Seega kasutame selle käsitsemiseks SUMPRODUCTi, kuna SUMPRODUCT suudab massiive naturaalselt käsitseda, ilma et oleks vaja Ctrl-Shift-Enter, nagu paljudel teistel massiivivalemitel.
Teine tee
Ülaltoodud näide on mõnevõrra keeruline. Teine võimalus selle probleemi lahendamiseks on teha igale lehele "kohalik" tingimuslik summa ja seejärel kasutada iga väärtuse kokkuvõtte vahekaardil tavalise 3D-summa kasutamist.
Selleks lisage igale lehelehele valem SUMIF, mis kasutab kokkuvõtte lehel kriteeriumirakku. Kui muudate kriteeriume, värskendatakse kõiki lingitud SUMIF-valemeid.
