Rumus COUNTIF untuk Baris yang terlihat saja (Visible Rows only)

Fungsi COUNTIF biasanya digunakan untuk menghitung jumlah sel dalam suatu range atau rentang yang memenuhi kriteria tunggal yang telah kita tentukan sebelumnya, termasuk jika pada range selnya ada baris/sel yang tersembunyi tetap akan dihitung.

Agar baris-baris yang tersembunyi tidak ikut terhitung perlu cara khusus untuk menghitungnya, jika menyembunyikan barisnya dengan cara di filter kita bisa menggunakan gabungan beberapa fungsi Excel contohnya seperti ini:

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

Hasilnya kurang lebih seperti ini:

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

Namun cara ini ada kekurangannya yakni ketika menyembunyikan baris melalui menu Hide Rows atau bisa juga dengan keyboard Ctrl + 9 baris-baris yang tersembunyi akan tetap di hitung. Jadi cara ini hanya cocok untuk data pada baris excel yang disembunyikan dengan cara difilter saja.

Agar baris-baris yang tersembunyi tidak ikut di hitung, kita bisa mengakalinya dengan membuat fungsi sendiri atau istilahnya UDF (User Defined Function) yang bernama COUNTIFVISIBLE hasil akhirnya nanti akan terlihat seperti gambar animasi berikut 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 adalah sebagai berikut:

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

2 Tambahkan Module baru dengan cara Insert - Module.

3 Pada jendela Module (Code) masukkan script VBA berikut 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 seperti ini:

Pembuatan fungsi UDF sudah selesai, untuk penggunaannya adalah:

=COUNTIFVISIBLE (rentang; kriteria)

Contohnya seperti gambar di bawah ini:

Agar kode Macro tidak hilang, maka ketika menyimpan workbook Anda harus menggunakan format yang bisa menyimpan macro yaitu .XLSM atau .XLSB atau bisa juga format .XLS, atau jika ingin dijadikan sebagai add ins maka bisa menyimpannya dalam format file .XLAM atau XLA.

Kita juga menambahkan teks bantuan pada fungsi UDF yang kita buat, supaya memudahkan agar tidak salah ketika memasukkan fungsi.

Caranya adalah dengan memasukan kode vab berikut 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 adalah nama fungsi UDF yang kita buat.
Category:= 4 artinya fungsi UDF kita akan di masukkan ke grup Statistical Functions.
ArgumentDescriptions untuk memasukkan teks bantuannya.