Formula umum
=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!"&rng),criteria))
Ringkasan
Untuk menghitung kecocokan di seluruh buku kerja, Anda bisa menggunakan rumus yang didasarkan pada fungsi COUNTIF dan SUMPRODUCT. Dalam contoh yang ditampilkan, rumus di D5 adalah:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!A1:Z10000"),B5))
di mana "lembar" adalah rentang bernama B8: B10.
Konteks
Buku kerja ini memiliki empat lembar kerja. Tiga dari lembar kerja, "Sheet1", "Sheet2", dan "Sheet3" berisi 1000 nama depan acak dalam tabel yang terlihat seperti ini:
Catatan: kisaran yang kami gunakan dalam rumus, A1: Z10000, adalah sewenang-wenang dan harus disesuaikan agar sesuai dengan data Anda.
Penjelasan
Bekerja dari dalam ke luar, pertama kita memiliki ekspresi ini di dalam fungsi INDIRECT:
"'"&sheets&"'!A1:Z10000"
Karena "sheets" adalah rentang bernama yang berisi "Sheet1", "Sheet2", dan "Sheet3", kita mendapatkan larik seperti ini setelah ekspresi dievaluasi:
("'Sheet1'!A1:Z10000";"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000")
Di dalam array, kita memiliki tiga nilai, dan masing-masing adalah nama sheet yang digabungkan melalui penggabungan ke rentang A1: Z10000. Perhatikan bahwa ini semua adalah nilai teks.
Selanjutnya, fungsi INDIRECT digunakan untuk mengonversi setiap nilai teks menjadi referensi yang tepat, yang diberikan ke fungsi COUNTIF sebagai argumen rentang, bersama dengan nilai dalam D5 untuk kriteria.
Karena kami telah memberikan COUNTIF tiga rentang terpisah, kami mendapatkan kembali tiga hasil dalam larik seperti ini:
(5;6;5)
Setiap item dihitung untuk satu lembar.
Akhirnya, SUMPRODUCT digunakan untuk menjumlahkan larik, dan mengembalikan hasil 16.
