Formula umum
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
Ringkasan
Untuk menjumlahkan rentang identik yang ada di lembar kerja terpisah secara bersyarat, semua dalam satu rumus, Anda dapat menggunakan fungsi SUMIF dengan INDIRECT, dibungkus dalam SUMPRODUCT. Pada contoh yang ditampilkan, rumus di C9 adalah:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))
Penjelasan
Data pada masing-masing dari tiga lembar yang sedang diproses terlihat seperti ini:
Pertama-tama, perhatikan bahwa Anda tidak dapat menggunakan SUMIF dengan referensi 3D "normal" seperti ini:
Sheet1:Sheet3!D4:D5
Ini adalah "sintaks 3D" standar tetapi jika Anda mencoba menggunakannya dengan SUMIF, Anda akan mendapatkan kesalahan #VALUE. Jadi, untuk mengatasi masalah ini Anda dapat menggunakan rentang bernama "lembar" yang mencantumkan setiap lembar (tab lembar kerja) yang ingin Anda sertakan. Namun, untuk membangun referensi yang akan diinterpretasikan oleh Excel dengan benar, kita perlu menggabungkan nama sheet ke rentang yang perlu kita kerjakan dan kemudian menggunakan INDIRECT untuk membuat Excel mengenalinya dengan benar.
Selain itu, karena rentang bernama "sheets" berisi beberapa nilai (yaitu sebuah array), hasil dari SUMIF dalam hal ini juga berupa sebuah array (terkadang disebut "array resultan). Jadi, kami menggunakan SUMPRODUCT untuk menanganinya, karena SUMPRODUCT memiliki kemampuan untuk menangani array secara native tanpa memerlukan Ctrl-Shift-Enter, seperti banyak rumus array lainnya.
Cara lain
Contoh di atas agak rumit. Cara lain untuk menangani masalah ini adalah dengan melakukan penjumlahan bersyarat "lokal" pada setiap lembar, kemudian menggunakan penjumlahan 3D biasa untuk menjumlahkan setiap nilai pada tab ringkasan.
Untuk melakukan ini, tambahkan rumus SUMIF ke setiap lembar lembar yang menggunakan sel kriteria pada lembar ringkasan. Kemudian saat Anda mengubah kriteria, semua rumus SUMIF yang ditautkan akan diperbarui.
