Bagaimana menghitung sel yang difilter dengan teks di Excel?
Di Excel, menghitung sel dengan teks itu sederhana. Tapi ketika datang ke menghitung sel yang difilter dengan teks, hal-hal menjadi rumit. Tutorial ini akan memperkenalkan tiga jalan untuk menghitung sel dengan teks dari daftar yang difilter.
Hitung sel yang difilter dengan teks dengan menggunakan kolom pembantu
Menggunakan fungsi COUNTIF, selain dengan bantuan kolom pembantu, kita dapat dengan mudah menghitung sel yang difilter dengan teks. Silakan lakukan sebagai berikut.
1. Silahkan salin rumus di bawah ini ke sel D2, lalu tekan tombol Enter untuk mendapatkan hasil pertama.
=SUBTOTAL(103,A2)
Catatan: Kolom pembantu dengan rumus SUBTOTAL adalah untuk menguji apakah baris difilter atau tidak. Dan 103 singkatan dari COUNTA fungsi di fungsi_num argumen.
2. Kemudian seret gagang isian ke bawah ke sel yang ingin Anda terapkan rumus ini juga.
3. Silakan salin rumus di bawah ini ke dalam sel F2, lalu tekan tombol Enter kunci untuk mendapatkan hasil akhir.
=COUNTIFS(A2:A18,"*", D2:D18, 1)
Kita dapat melihat bahwa ada 4 sel dengan teks dalam data yang difilter.
Metode lain untuk menghitung sel yang difilter dengan teks adalah menggunakan kombinasi dari SUMPRODUK, SUBTOTAL, TIDAK LANGSUNG, BARIS & ISTEKS Fungsi. Silakan lakukan sebagai berikut.
Silakan salin rumus di bawah ini ke dalam sel E2, lalu tekan tombol Enter kunci untuk mendapatkan hasil.
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))), --(ISTEXT(A2:A18)))
Penjelasan Rumus:
- BARIS(A2:A18) mengembalikan nomor baris masing-masing dari rentang A2:A18.
- TIDAK LANGSUNG("A"&BARIS(A2:A18)) mengembalikan referensi sel yang valid dari rentang yang diberikan.
- SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))) menguji apakah baris difilter atau tidak, dan mengembalikan 1 untuk sel yang terlihat, 0 untuk sel yang tersembunyi dan kosong.
- ISTEKS(A2:A18) memeriksa apakah setiap sel dalam rentang A2:A18 berisi teks, dan mengembalikan True untuk sel dengan teks, False untuk sel lainnya. Operator unary ganda (--) memaksa nilai TRUE dan FALSE menjadi 1 dan 0.
- SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))), --(ISTEXT(A2:A18))) dapat dilihat sebagai SUMPRODUCT({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1}). Kemudian SUMPRODUCT mengalikan dua array bersama-sama dan mengembalikan jumlah nilai, yaitu 4.
Metode ketiga menghitung sel dengan teks dari data yang difilter adalah menggabungkan itu SUMPRODUK, SUBTOTAL, OFFSET, MIN, BARIS & ISTEKS Fungsi. Silakan lakukan sebagai berikut.
Silakan salin rumus di bawah ini ke dalam sel E2, lalu tekan tombol Enter kunci untuk mendapatkan hasil.
=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)), -- (ITEXT(A2:A18)) )
Penjelasan Rumus:
- OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1) mengembalikan referensi sel individual dari rentang A2:A18.
- SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)) memeriksa apakah baris difilter atau tidak, dan mengembalikan 1 untuk sel yang terlihat, 0 untuk sel yang tersembunyi dan kosong.
- ISTEKS(A2:A18) memeriksa apakah setiap sel dalam rentang A2:A18 berisi teks, dan mengembalikan True untuk sel dengan teks, False untuk sel lainnya. Operator unary ganda (--) memaksa nilai TRUE dan FALSE menjadi 1 dan 0.
- SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)), -- (ITEXT(A2:A18))) dapat dilihat sebagai SUMPRODUCT({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1}). Kemudian SUMPRODUCT mengalikan dua array bersama-sama dan mengembalikan jumlah nilai, yaitu 4.
Operasi Lainnya (Artikel)
Gabungkan Fungsi COUNTIF Dan LEFT Untuk Menghitung Sel Dimulai Dengan Karakter Tertentu Di Excel
Untuk menghitung jumlah sel yang dimulai dengan karakter tertentu di Excel, Anda dapat menggabungkan fungsi COUNTIF dan LEFT untuk menyelesaikannya. Sebenarnya ada berbagai macam rumus yang bisa mencapainya. Tutorial ini akan memandu Anda langkah demi langkah.
Bagaimana Menghitung Sel Yang Mengandung Angka Atau Tidak Di Excel?
Jika ada rentang sel, beberapa berisi angka, dan yang lainnya berisi teks, bagaimana Anda bisa menghitung sel yang berisi angka atau tidak dengan cepat di Excel?
Bagaimana Menghitung Sel Jika Salah Satu Dari Beberapa Kriteria Terpenuhi Di Excel?
Bagaimana jika menghitung sel jika mengandung salah satu dari beberapa kriteria? Di sini saya akan membagikan cara menghitung sel jika mengandung X atau Y atau Z … dll di Excel.
Bagaimana Menghitung Sel Dengan Teks Dan Warna Isi / Font Tertentu Di Excel?
Apakah Anda tahu cara menghitung sel dengan beberapa kondisi? Misalnya, hitung jumlah sel yang berisi teks dan warna font/isian tertentu. Artikel ini akan menunjukkan solusinya.
Alat Produktivitas Kantor Terbaik
Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%
- Bilah Formula Super (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
- Gabungkan Sel / Baris / Kolom dan Menyimpan Data; Pisahkan Konten Sel; Gabungkan Baris Duplikat dan Jumlah / Rata-rata... Mencegah Sel Duplikat; Bandingkan Rentang...
- Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
- Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
- Favorit dan Sisipkan Rumus dengan Cepat, Rentang, Grafik dan Gambar; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
- Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
- Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
- Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
- Pengelompokan Tabel Pivot menurut nomor minggu, hari dalam seminggu dan banyak lagi ... Tampilkan Sel yang Tidak Terkunci dan Terkunci dengan warna berbeda; Sorot Sel Yang Memiliki Rumus / Nama...
- Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
- Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
- Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
