Bagaimana cara menghitung sel dengan teks di excel?
Minggu lalu, salah satu pembaca saya menanyakan rumus sederhana yang bisa dia gunakan untuk menghitung sel dengan teks. Pada dasarnya, dia mencoba mencari tahu metode apa yang terbaik untuk digunakan.
Namun jika dipikir-pikir sejenak. Di Excel Anda memiliki rumus berbeda untuk menghitung semua sel tempat Anda memiliki nilai teks. Tapi intinya yang mana yang sebaiknya Anda gunakan.
Saya pikir itu tergantung situasinya. Satu hal yang perlu Anda pahami di sini adalah bahwa sel dengan teks berarti sel mana pun yang tidak memiliki angka, sel kosong, atau kesalahan.
Hari ini di artikel ini saya ingin berbagi 6 cara berbeda untuk menghitung sel dengan teks dalam situasi berbeda.
1. COUNTIF dengan wildcard untuk menghitung sel dengan nilai teks
Rumus terbaik untuk menghitung sel dengan teks adalah COUNTIF dengan wildcard (* – Asterisk). Pada tabel data di bawah ini, Anda memiliki daftar nama dan nomor ponsel dalam satu kolom. Dan dari daftar ini Anda perlu menghitung jumlah sel dengan nama (teks). Rumusnya adalah:
=COUNTIF(A2:A20,"*")
Saat Anda memasukkannya, itu akan mengembalikan jumlah sel dengan teks saja.
Beginilah cara kerja rumus ini
Di sini kita menggunakan COUNTIF untuk menghitung sel dengan kriteria tertentu dan untuk kriteria kita menggunakan tanda bintang. Saat Anda menggunakan tanda bintang, ini menghitung sel yang berisi sejumlah karakter selain nilai logika, angka (jika tidak dimasukkan sebagai teks), dan kesalahan.
Catatan: Jika ada sel yang hanya memiliki spasi kosong , ini akan dihitung sebagai teks. Jadi untuk ini Anda dapat memodifikasi rumus Anda seperti ini.
=COUNTIFS(A2:A20,"*",A2:A20,"<> ")2. SUMPRODUK dan ISTEXT untuk menghitung jumlah sel yang berisi teks
Untuk menghitung jumlah sel yang berisi teks, Anda juga bisa membuat rumus dengan menggabungkan SUMPRODUK dan ISTEXT. Dan rumusnya menjadi:
=SUMPRODUCT(--ISTEXT(A2:A20))
Saat Anda memasukkannya, itu akan mengembalikan jumlah sel yang berisi teks.
Beginilah cara kerja rumus ini
Untuk memahami rumus ini, Anda perlu membaginya menjadi tiga bagian berbeda.
Pertama, Anda menggunakan ISTEXT untuk memeriksa apakah suatu sel berisi teks atau tidak. Di sini ISTEXT mengembalikan array yang untuk semua sel di mana Anda memiliki TRUE untuk sel dengan teks dan FALSE untuk semua sel lainnya.
Kedua, Anda menambahkan tanda minus ganda untuk mengubah array TRUE/FALSE menjadi 1/0. Sekarang untuk semua sel dengan teks Anda memiliki 1 dan 0 untuk sel lainnya.
Ketiga, Anda menggunakan SUMPRODUK untuk menjumlahkan array ini. Dan jumlah ini adalah jumlah sel yang berisi teks.
Keuntungan menggunakan SUMPRODUK di sini adalah Anda tidak perlu memasukkan rumus dalam bentuk tabel.
3. Rumus menghitung sel dengan teks tertentu
Sekarang mari kita gali lebih dalam. Misalkan Anda perlu menghitung sel tempat Anda memiliki teks tertentu. Ini sebuah contoh. Pada tabel data di bawah, Anda perlu menghitung sel tempat Anda memiliki “John”. Dan untuk ini rumusnya adalah:
=COUNTIF(A2:A20,"John")
Rumus ini akan mengembalikan jumlah sel tempat teks yang Anda tentukan berada.
4. Sel dengan sebagian teks
Ini terkadang terjadi ketika Anda perlu menghitung sel dengan mencocokkan sebagian teks. Dan, COUNTIF dan wildcard dapat membantu Anda dalam hal tersebut.
Lihat tabel di bawah di mana kami menggabungkan nama produk dan nomor faktur. Dan dari sana Anda perlu menghitung jumlah sel dengan “Produk-A”. Gunakan rumus di bawah ini:
=COUNTIF(A2:A20,"Product-A*")
Rumus ini akan mengembalikan jumlah sel tempat Anda memiliki “Produk-A”.
5. Teks peka huruf besar-kecil
Sekarang ada hal yang berbeda. Anda perlu menghitung sel dengan teks tertentu dan ini harus berupa angka peka huruf besar-kecil.
Pada tabel di bawah, Anda perlu menghitung sel dengan nama “JOHN”, tetapi di sini Anda memiliki nama yang sama dalam huruf kecil, tetapi nomornya harus dalam huruf besar.
Untuk ini, Anda dapat menggabungkan SUMPRODUK dengan EXACT.
=SUMPRODUCT(--EXACT("JOHN",A1:A20))
Ini akan mengembalikan jumlah sel tempat Anda memiliki “JOHN”.
Bagaimana cara kerja rumus ini?
Untuk memahami rumus ini, Anda perlu membaginya menjadi tiga bagian berbeda.
Pertama, Anda menggunakan EXACT untuk membandingkan seluruh rentang sel dengan teks “JOHN”. Ia mengembalikan array di mana Anda memiliki TRUE untuk sel di mana “JOHN”, mengabaikan “john”, “John” dan semua sel lainnya.
Kedua, Anda menambahkan tanda minus ganda untuk mengubah array TRUE/FALSE ini menjadi 1/0. Sekarang untuk semua sel dengan “JOHN” Anda memiliki 1 dan 0 untuk sel lainnya.
Ketiga, Anda menggunakan SUMPRODUK untuk menjumlahkan array ini. Dan jumlah ini adalah jumlah sel yang mengandung “JOHN”.
5. VBA untuk menghitung sel dengan teks
Menggunakan kode makro sangat menghemat waktu. Kode di bawah ini akan membantu Anda menghitung jumlah sel dengan teks dalam pilihan dan di seluruh lembar kerja.
Untuk seleksi:
Sub countTextSelection() Dim rng As Range Dim i As Integer For Each rng In Selection If Application.WorksheetFunction.IsText(rng) Then i = i + 1 End If Next rng MsgBox i End SubKeseluruhan spreadsheet:
Sub countTextWorksheet() Dim rng As Range Dim i As Integer For Each rng In ActiveSheet.UsedRange If Application.WorksheetFunction.IsText(rng) Then i = i + 1 End If Next rng MsgBox i End SubTerkait: Apa itu VBA di Excel
Kesimpulan
Saya yakin semua metode ini cukup bagi Anda untuk menghitung sel dengan teks dalam situasi tertentu. Anda dapat menggunakan salah satu metode yang menurut Anda cocok untuk Anda.
Untuk sebagian besar situasi, metode pertama dapat diterapkan tanpa masalah apa pun. Tapi, jika ingin lebih jauh, Anda bisa menggunakan cara lain. Bagi saya, VBA sangat menghemat waktu karena saya tidak perlu menerapkan rumus. Sekarang giliranmu untuk memberitahuku sesuatu.
Apakah Anda memiliki metode lain yang bisa kita gunakan untuk menghitung jumlah sel dengan teks?
Pastikan untuk membagikan pemikiran Anda kepada saya di bagian komentar, saya ingin mendengar pendapat Anda. Dan jangan lupa untuk membagikan postingan ini ke teman-teman Anda, saya yakin mereka akan menikmatinya.