Rumus Countif Untuk Baris Yang Terlihat Saja (Visible Rows Only)

[.] com - Fungsi COUNTIF biasanya dipakai untuk menghitung jumlah sel dalam suatu range atau rentang yang memenuhi kriteria tunggal yang sudah kita tentukan sebelumnya, termasuk kalau pada range selnya ada baris/sel yang tersembunyi tetap akan dihitung.

Agar baris-baris yang tersembunyi tidak ikut terhitung perlu cara khusus untuk menghitungnya, kalau menyembunyikan barisnya dengan cara di filter kita dapat memakai adonan beberapa fungsi Excel contohnya ibarat ini:

=SUMPRODUCT((A2:B6="APEL")*(SUBTOTAL(3;OFFSET(A2;ROW(A2:B6)-MIN(ROW(A2:B6));0))))

Hasilnya kurang lebih ibarat ini:

Terlihat pada bab kiri jumlah buah Apel ada 5 dan saat di filter dengan menyembunyikan baris ke tiga hasilnya buah Apelnya berjumlah 4.

Namun cara ini ada belum sempurnanyanya yakni saat menyembunyikan baris melalui sajian Hide Rows atau dapat juga dengan keyboard Ctrl + 9 baris-baris yang tersembunyi akan tetap di hitung. Kaprikornus cara ini spesialuntuk cocok untuk data pada baris excel yang disembunyikan dengan cara difilter saja.

Agar baris-baris yang tersembunyi tidak ikut di hitung, kita dapat mengakalinya dengan membuat fungsi sendiri atau istilahnya UDF (User Defined Function) yang berjulukan COUNTIFVISIBLE hasil balasannya nanti akan terlihat ibarat gambar animasi diberikut ini:

Ketika row atau baris disembunyikan otomatis juga akan diabaikan alias tidak ikut di hitung, untuk membuat fungsi UDF harus mengguanakan VBA atau Macro dan caranya yaitu sebagai diberikut:

1 Buka aplikasi Microsoft Excel, kemudian tekan tombol Alt + F11 untuk membuka aplikasi Microsoft Visual Basic for Application.

2 Tambahkan Module gres dengan cara Insert - Module.

3 Pada jendela Module (Code) masukkan script VBA diberikut ini:

Function COUNTIFVISIBLE(rentang As Range, Kriteria As Variant) As Long     Dim rngSel As Range, hasil As Long     For Each rngSel In rentang         If Not (rngSel.EntireRow.Hidden Or rngSel.EntireColumn.Hidden) Then             If rngSel.Value = Kriteria Then hasil = hasil + 1         End If     Next rngSel     COUNTIFVISIBLE = hasil End Function 

Hasilnya kurang lebih ibarat ini:

Pembuatan fungsi UDF sudah selesai, untuk penerapannya adalah:

=COUNTIFVISIBLE (rentang; kriteria)

contohnya ibarat gambar di bawah ini:

Agar instruksi Macro tidak hilang, maka saat menyimpan workbook Anda harus memakai format yang dapat menyimpan macro yaitu .XLSM atau .XLSB atau dapat juga format .XLS, atau kalau ingin dijadikan sebagai add ins maka dapat menyimpannya dalam format file .XLAM atau XLA.

Kita juga menambahkan teks menolongan pada fungsi UDF yang kita buat, supaya megampangkan semoga tidak salah saat memasukkan fungsi.

Tekniknya yaitu dengan memasukan instruksi vab diberikut ini pada ThisWorkbook (code):

Private Sub Workbook_Open()     Application.MacroOptions _     Macro:="COUNTIFVISIBLE", _     Category:=4, _     ArgumentDescriptions:=Array( _         "Rentang atau Range yang hitung", _         "Kriteria yang diinginkan") End Sub 

Keterangan:
Macro yaitu nama fungsi UDF yang kita buat.
Category:= 4 artinya fungsi UDF kita akan di masukkan ke grup Statistical Functions.
ArgumentDescriptions untuk memasukkan teks menolongannya.

Lampiran

COUNTIF VISIBLE ROW.xlsb   [ 17 KB ]

Sekian tutorial pembuatan fungsi UDF COUNTIF untuk menghitung sel excel yang terlihat saja, Semoga bermanfaa...