Formula umum
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Ringkasan
Untuk mencari nilai di beberapa lembar kerja di buku kerja dan mengembalikan hitungan, Anda bisa menggunakan rumus yang didasarkan pada fungsi COUNTIF dan INDIRECT. Dengan beberapa penyetelan awal, Anda dapat menggunakan pendekatan ini untuk mencari nilai tertentu di seluruh buku kerja. Pada contoh yang ditunjukkan, rumus di C5 adalah:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Konteks - contoh data
Buku kerja berisi total 4 lembar kerja. Sheet1 , Sheet2 , dan Sheet3 masing-masing berisi 1000 nama depan acak yang terlihat seperti ini:
Penjelasan
Rentang B7: B9 berisi nama sheet yang ingin kita sertakan dalam pencarian. Ini hanyalah string teks, dan kita perlu melakukan beberapa pekerjaan untuk membuatnya dikenali sebagai referensi sheet yang valid.
Bekerja dari dalam ke luar, ekspresi ini digunakan untuk membuat referensi lembar penuh:
"'"&B7&"'!"&"1:1048576"
Tanda kutip tunggal ditambahkan untuk mengizinkan nama lembar dengan spasi, dan tanda seru adalah sintaks standar untuk rentang yang menyertakan nama lembar. Teks "1: 1048576" adalah rentang yang mencakup setiap baris di lembar kerja.
Setelah B7 dievaluasi, dan nilai digabungkan, ekspresi di atas mengembalikan:
"'Sheet1'!1:1048576"
yang masuk ke fungsi INDIRECT sebagai argumen 'ref_text'. INDIRECT mengevaluasi teks ini dan mengembalikan referensi standar ke setiap sel di Sheet1 . Ini masuk ke fungsi COUNTIF sebagai rentang. Kriteria tersebut diberikan sebagai acuan mutlak untuk C4 (dikunci sehingga rumus dapat disalin ke kolom C).
COUNTIF kemudian mengembalikan hitungan semua sel dengan nilai yang sama dengan "mary", 25 dalam kasus ini.
Catatan: COUNTIF tidak peka huruf besar-kecil.
Berisi vs. Setara
Jika Anda ingin menghitung semua sel yang berisi nilai di C4, alih-alih semua sel sama dengan C4, Anda dapat menambahkan wildcard ke kriteria seperti ini:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Sekarang COUNTIF akan menghitung sel dengan substring "John" di mana saja di dalam sel.
Performa
Secara umum, bukanlah praktik yang baik untuk menentukan rentang yang mencakup semua sel lembar kerja. Melakukannya dapat menyebabkan masalah kinerja, karena jangkauannya mencakup jutaan dan jutaan sel. Dalam contoh ini, masalahnya bertambah, karena rumus menggunakan fungsi INDIRECT, yang merupakan fungsi volatil. Fungsi volatil dihitung ulang pada setiap perubahan lembar kerja, sehingga dampaknya pada kinerja bisa sangat besar.
Jika memungkinkan, batasi rentang ke ukuran yang sesuai. Misalnya, jika Anda mengetahui bahwa data tidak akan muncul setelah baris 1000, Anda dapat mencari 1000 baris pertama saja seperti ini:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
