Hitung jumlah sel yang berisi teks tertentu di Excel
Misalkan, Anda memiliki daftar string teks, dan mungkin ingin menemukan jumlah sel yang berisi teks tertentu sebagai bagian dari isinya. Dalam hal ini, Anda dapat menggunakan karakter wildcard (*) yang mewakili teks atau karakter apa pun dalam kriteria Anda saat menerapkan fungsi COUNTIF. Artikel ini, saya akan memperkenalkan cara menggunakan rumus untuk menangani pekerjaan ini di Excel.
- Hitung jumlah sel yang berisi teks tertentu tanpa peka huruf besar-kecil
- Hitung jumlah sel yang berisi teks tertentu dengan peka huruf besar-kecil
Hitung jumlah sel yang berisi teks tertentu tanpa peka huruf besar-kecil
Untuk menghitung jumlah sel yang cocok dengan bagian dari string teks, fungsi COUNIF di Excel dapat membantu Anda, lakukan hal berikut:
Rumus umum dengan teks hardcode:
- range: Rentang sel berisi string teks yang ingin Anda hitung;
- *: Wildcard yang menemukan sejumlah karakter;
- text: Teks atau karakter tertentu yang harus dicari.
Seperti gambar di bawah ini, saya ingin menghitung jumlah sel yang berisi teks "Tokyo" tertentu dari rentang A2:A11.
Silakan masukkan atau salin rumus di bawah ini ke dalam sel kosong, lalu tekan Enter kunci untuk mengembalikan hasilnya, lihat tangkapan layar:
Rumus umum dengan referensi sel:
- range: Rentang sel berisi string teks yang ingin Anda hitung;
- *: Wildcard yang menemukan sejumlah karakter;
- cell: Sel berisi teks atau karakter tertentu yang harus dicari.
Untuk membuat rumus secara dinamis, ganti string hardcode dengan referensi sel. Misalnya, teks kriteria ditempatkan ke dalam sel C2:C4, untuk menghitung sel yang berisi bagian dari teks ini, lakukan seperti ini.
1. Masukkan atau salin rumus di bawah ini ke dalam sel kosong tempat untuk menampilkan hasilnya:
2. Kemudian seret gagang isian ke sel yang ingin Anda terapkan rumus ini, dan Anda akan mendapatkan hasil seperti gambar di bawah ini:
Tips: Jika Anda ingin menghitung jumlah sel yang berisi lebih dari satu kriteria teks, misalnya, untuk menghitung sel yang berisi item "Tokyo" dan "Kelas1" dalam rentang A2: A11 seperti gambar di bawah ini:
Untuk menghitung sel dengan beberapa substring, silakan terapkan salah satu rumus berikut sesuai keinginan Anda:
=COUNTIFS(A2:A11,"*"&C2&"*", A2:A11,"*"&C3&"*")
Hitung jumlah sel yang berisi teks tertentu dengan peka huruf besar-kecil
Untuk menghitung jumlah sel yang berisi teks tertentu dengan peka huruf besar/kecil yang sama persis dengan teks huruf besar/kecil dalam sel tertentu, Anda harus menggabungkan fungsi SUMPRODUCT, ISNUMBER dan FIND untuk menyelesaikannya, sintaks generiknya adalah:
- text: Teks atau sel berisi teks yang Anda cari;
- range: Rentang sel berisi string teks yang ingin Anda hitung.
Silakan masukkan atau salin rumus di bawah ini ke dalam sel kosong, lalu tekan Enter kunci untuk mendapatkan hasil:
Penjelasan rumusnya:
- TEMUKAN(C2, A2:A11): Fungsi FIND peka huruf besar/kecil, ia mencari teks tertentu di setiap sel rentang. Jika teks ada, itu akan mengembalikan posisi karakter pertama, jika tidak #VALUE! Kesalahan dikembalikan. Jadi, Anda akan mendapatkan larik seperti ini: {7;#VALUE!;#VALUE!;#VALUE!;1;6;#VALUE!;#VALUE!;#VALUE!;#VALUE!}.
- NOMOR(TEMUKAN(C2, A2:A11)): Fungsi ISNUMBER ini akan mengubah angka menjadi TRUE, dan nilai kesalahan menjadi FALSE seperti ini: {TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}.
- --(INUMBER(TEMUKAN(C2, A2:A11)))): Tanda negatif ganda (--) digunakan untuk mengubah nilai TRUE dan FALSE menjadi angka 1 dan 0 seperti ini: {1;0;0;0;1;1;0;0;0;0}.
- SUMPRODUCT(--(ISNUMBER(FIND(C2, A2:A11))))=SUMPRODUCT({1;0;0;0;1;1;0;0;0;0}): Akhirnya, fungsi SUMPRODUCT ini menjumlahkan semua angka dalam array untuk mendapatkan hasil akhir: 3.
Fungsi relatif yang digunakan:
- COUNTIF:
- Fungsi COUNTIF adalah fungsi statistik di Excel yang digunakan untuk menghitung jumlah sel yang memenuhi kriteria.
- SUMPRODUK:
- Fungsi SUMPRODUCT bisa digunakan untuk mengalikan dua atau lebih kolom atau larik bersama-sama, lalu mendapatkan jumlah produk.
- NOMOR:
- Fungsi ISNUMBER mengembalikan TRUE saat sel berisi angka, dan FALSE jika tidak.
- MENCARI:
- Fungsi FIND digunakan untuk menemukan string di dalam string lain, dan mengembalikan posisi awal string di dalam string lain.
Artikel lainnya:
- Hitung jumlah sel yang tidak sama dengan banyak nilai di Excel
- Di Excel, Anda dapat dengan mudah mendapatkan jumlah sel yang tidak sama dengan nilai tertentu dengan menggunakan fungsi COUNTIF, tetapi apakah Anda pernah mencoba menghitung sel yang tidak sama dengan banyak nilai? Misalnya, saya ingin mendapatkan jumlah total produk di kolom A tetapi mengecualikan item tertentu di C4:C6 seperti gambar di bawah ini. Artikel ini, saya akan memperkenalkan beberapa rumus untuk menyelesaikan pekerjaan ini di Excel.
- Hitung Jumlah Sel Yang Mengandung Angka Ganjil Atau Genap
- Seperti yang kita ketahui, bilangan ganjil memiliki sisa 1 jika dibagi 2, dan bilangan genap memiliki sisa 0 jika dibagi 2. Pada tutorial kali ini saya akan membahas tentang cara mendapatkan jumlah sel yang mengandung ganjil atau genap. angka di Excel.
- Hitung Jumlah Sel Yang Mengandung Jumlah Karakter Tertentu
- Jika Anda ingin menghitung jumlah sel yang berisi sejumlah karakter tertentu (seperti 5 karakter) dalam rentang sel seperti gambar di bawah ini. Di Excel, fungsi COUNTIF dapat membantu Anda. Pada artikel ini, saya akan berbicara tentang cara mendapatkan jumlah sel yang berisi persis x karakter atau sel yang berisi lebih dari jumlah karakter tertentu.
Alat Produktivitas Kantor Terbaik
Kutools for Excel - Membantu Anda Menonjol Dari Kerumunan
Kutools for Excel Membanggakan Lebih dari 300 Fitur, Memastikan Apa yang Anda Butuhkan Hanya Dengan Sekali Klik...
Tab Office - Aktifkan Pembacaan dan Pengeditan dengan Tab di Microsoft Office (termasuk Excel)
- Satu detik untuk beralih di antara lusinan dokumen terbuka!
- Kurangi ratusan klik mouse untuk Anda setiap hari, ucapkan selamat tinggal pada tangan mouse.
- Meningkatkan produktivitas Anda sebesar 50% saat melihat dan mengedit banyak dokumen.
- Menghadirkan Tab Efisien ke Office (termasuk Excel), Sama Seperti Chrome, Edge, dan Firefox.
