Identifikasi Formula Dengan Conditional Formating

Bagaimanakah cara mengenali dan membedakan jenis konten sel, apakah berupa nilai statis atau sebuah formula? Cara pertama yang umumnya digunakan adalah dengan klik sel tertentu dan melihat formula bar, apakah disitu ada rumus/formula atau tidak. Cara kedua adalah dengan menampilkan mode show formula (tekan shortcut Ctrl + `).  

Selain kedua cara tersebut, mulai dari versi 2013, excel sudah menyediakan fungsi ISFORMULA yang dapat digunakan untuk mengetest apakah kontent sel berupa formula atau bukan. Dengan menggunakan fungsi ISFORMULA dalam rule conditional formating, maka kita bisa mengenali sel berisi formula dengan mudah dan cepat. 

Fungsi ISFORMULA

Fungsi ISFORMULA berguna untuk mengetest apakah sebuah sel berisi formula atau tidak. Fungsi ini dapat dituliskan sebagai berikut:    =ISFORMULA(reference)

Adapun referensi di sini adalah berupa sebuah sel. Misalnya untuk mengecek apakah sel A1 berisi formula atau tidak, maka kita bisa menuliskan rumus:  =ISFORMULA(A1)

Jika sel A1 berisi formula maka rumus akan menghasilkan nilai TRUE, sebaliknya jika sel A1 berisi nilai statis atau tidak berisi formula maka rumus akan menghasilkan nilai FALSE. Untuk lebih jelasnya perhatikan gambar di bawah ini.

Gambar di atas menunjukan cara menggunakan rumus ISFORMULA  dan nilai TRUE dan FALSE sebagai hasil dari rumus tersebut.

Fungsi ISFORMULA dengan Conditional Formating.

Untuk memggunakan fungsi  ISFORMULA dalam rule conditional formating maka ikuti langkah berikut:

Contoh: Sebuah tabel pada range A1:C5 berisi serangkaian data. Conditional formating diperlukan untuk mengidentifikasi sel-sel dalam range tersebut apakah berisi formula atau hanya berisi nilai statis saja. 

• Seleksi range A1:C5 dimulai dari ujung Atas Kiri (sel A1) sampai ke ujung bawah kanan (sel C5)
• Dari tab home, klik Conditional Formating ➜ klik New Rule… sehingga masuk ke jendela New Formating Rule
• Select rule type: pilih user a Formula to determine which cells to format.
• Ketika formula berikut:  =ISFORMULA(A1)  
• Perhatikan kembali rumus ISFORMULA(A1),  pastikan penulisan referensi A1 tidak boleh ada tanda dolar.
• Klik tombol Format untuk mengatur format sel apabila sel berisi formula
• Klik Tab Fill ➜ Pilih warna yang anda inginkan, misalnya kuning  (untuk pengaturan format ini ada juga bisa memilih pengaturan format sesuai preferensi anda, mana yang anda suka)
• Setelah setting format dirasa sesuai, lalu klik OK, dan klik OK lagi.
• Berikut gambaran langkah-langkah penerapan fungsi ISFORMULA dalam rule conditional formating.

Setelah langkah-langkah diatas diikuti, maka sampai tahap tersebut kita sudah bisa membuat conditional formating untuk mengecek apakah konten sel berisi formula atau bukan. Berikut contoh kenampakannya di excel setelah dilakukan setting rule conditional formating dengan rumus ISFORMULA

Bagaimana jika fungsi ISFORMULA tidak tersedia pada versi excel yang anda gunakan?

Jika masih menggunakan excel versi 2010 atau yang lebih lama, anda tidak menemukan fungsi ISFORMULA pada versi tersebut. Namun demikian, tenang saja,  anda masih bisa membuat fungsi ISFORMULA sendiri menggunakan bantuan VBA. Fungsi ISFORMULA buatan user ini hanya memerlukan code VBA yang sangat sederhana, yaitu:

Function ISFORMULA(sel As Range)

ISFORMULA = sel.HasFormula

End Function

Supaya bisa digunakan, code tersebut harus diketik atau dicopy pada VBA editor, modul standar (tekan short cut Alt + F11 untuk masuk ke VBA editor, kemudian klik insert  modul  untuk membuat modul baru. Selanjutnya copy atau ketik kode VBA seperti contoh diatas.

Langkah selanjutnya tinggal menerapkan fungsi ISFORMULA dalam conditional formating. 

Demikian pembahasan singkat mengenai bagaimana membuat rule conditional formating untuk mengenali dan membedakan kontent sel yang berisi formula dengan yang bukan formula / rumus. Semoga bermanfaat.

Terima kasih.

Silahkan dicek juga catatan pelajaran excel lainnya:

• Control Conditional Formating dengan Check Box
• Menemukan Data Duplikat dengan Conditional Formating
• Menandai Data yang Muncul 2 kali, 3 kali atau lebih
• Merubah Warna Sel berdasarkan Value dan Formula

Cara Membatasi Scroll Area Pada Worksheet

Pada Excel versi 2007 atau yang lebih baru, area worksheet dibatasi oleh jumlah kolom maksimal 16.364 dan jumlah baris maksimal 1.048.576.  Namun pada kenyataanya, user lebih sering menggunakan sebagian kecil saja dari jumlah sel yang tersedia dalam worksheet. Penggunaan sebagian area dalam worksheet tanpa pembatasan scroll area terkadang cukup menyulitkan dalam melakukan observasi lembar kerja excel. Karena itulah maka disarankan untuk membatasi scroll area.

Keuntungan pembatasan scroll area:

• Pembatasan scroll area memudahkan dalam melakukan observasi dan navigasi workhsheet.
• Jika anda pembuat tempate laporan, maka anda bisa menyembunyikan data yang tidak ingin diketahui oleh user di luar lokasi scroll area.

Pembatasan scroll area dapat dilakukan dengan beberapa cara, antara lain:

• Menyembunyikan kolom dan baris tidak terpakai
• Menentukan valid range
• Mengaktifkan range terpakai (used range)

Menyembunyikan kolom dan baris tidak terpakai

Tehnik paling sederhana untuk membuat batasan lembar kerja excel adalah dengan cara menyembunyikan baris dan kolom tidak terpakai.

Langkahnya cukup mudah:

• Seleksi semua kolom yang tidak digunakan, kemudian klik kanan ➨ hide
• Dengan cara serupa, seleksi semua baris yang tidak digunakan, kemudian klik kanan ➨ hide

Hasilnya dapat dilihat seperti screenshot di bawah ini.

Dari gambar di atas, kita dapat melihat, excel hanya menampilkan kolom dan baris untuk area yang digunakan saja, yaitu baris 1 s/d 15 dan kolom A s/d H. Hal tersebut karena area yang tidak digunakan sudah disembunyikan (di-hide).

Menentukan Valid Range Spesifik

Metode kedua untuk menetapkan batasan scroll area adalah dengan cara menentukan valid range spesifik. Valid range dapat di-setting melalui property worksheet.

Caranya:

• Klik kanan pada tab sheet yang akan ditentukan sroll area nya.
• Klik view Code, sehingga akan masuk ke VBA editor. 

• Di dalam vba editor, cari jendela properties. Jika jendela properties belum terlihat, tekan F4 untuk memunculkannya.
• Pada jendela properties, pada field ScrollArea, ketikan valid range yang anda inginkan. Contoh pengisian seperti dalam gambar di bawah ini.

Keterangan: tanda absolute ($) akan muncul otomatis meskipun kita mengetik valid range tanpa tanda dolar $.

Setelah selesai, kembali ke worksheet dan kita bisa buktikan bahwa scroll range akan dibatasi sesuai valid range yang kita ketikan di jendela properties. Jika scroll area yang kita gunakan sedikit saja atau kurang dari ukuran range yang terlihat (visible range), maka kolom dan baris diluar scroll area masih akan nampak. Namun kita  tidak bisa menyeleksi sel / range dan mengetik pada sel diluar scroll area. Silahkan dibuktikan sendiri.

Sayangnya, kita tidak bisa menyimpan setting scrollarea yang dibuat dengan cara edit properties karena scroll area akan di-reset kembali setelah close file.

Untuk mengatasi hal tersebut, kita bisa menggunakan code vba untuk menetapkan scroll area. Code ini ditempatkan pada even worksheet_activate sehingga akan dijalankan setiap kali worksheet yang disisipkan code ini diaktifkan.

Caranya:

• Klik kanan pada tab sheet yang akan dibatasi sroll area nya.
• Klik view Code, sehingga akan masuk ke VBA editor.
• Pada jendela project explorer, double clik nama sheet yang akan dibatasi scroll area nya sehingga muncul muncul object sheet tersebut.
• Ketik atau copy code berikut pada modul ojbect sheet:


Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:J50"
End Sub


Contoh penempatan code dapat dilihat pada screenshot di bawah ini.

Gambar di atas menunjukan contoh penempatan code untuk membatasi scroll area sheet 3  pada range A1:J50. Code berjalan setiap kali sheet 3 diaktifkan (berpindah dari sheet lain ke sheet 3).

Untuk membuat batasan scroll area pada sheet lainnya, silahkan masuk kembali ke VBA editor, kemudian pada jendela project explorer, double klik nama sheet yang akan disisipkan code. Selanjutnya ketik code yang sama dengan contoh sebelumnya pada modul object sheet yang diinginkan.

Kita juga bisa berexperimen dengan merubah range scroll area. Silahkan range A1:J50 diedit kembali dengan range yang anda inginkan.

Bagaimana Jika kita ingin mengedit sel di luar scroll area?

Sebagaimana penjelasan di atas, setelah kita setting scroll area maka kita tidak bisa mengedit sel di luar scroll area. Untuk itu kita harus menghapus setting scroll area terlebih dahulu jika ingin memodifikasi area sheet di luar scrollarea.

Kita bisa menghapus secara manual dengan cara menghapus scroll area pada property worsksheet. Cara yang lebih cepat dan disarankan adalah dengan menggunakan macro / VBA. Adapun codenya sangat sederhana.

Misalnya:

Sub deleteScrollArea()
ActiveSheet.ScrollArea = ""
End Sub


Untuk menempatkan code pada module VBA, ikuti langkah berikut:

• Masuk ke jendela VBA dengan cara tekan Alt + F11
• Jika module standar belum tersedia, Klik menu Insert ➨ Module
• Maka akan muncul modul baru (misalnya : Module1)
• Tempatkan code pada module 1. Bisa dilakukan dengan copy paste contoh code di atas, maupun dengan mengetiknya sendiri.

Screenshot di bawah ini menggambarkan langkah-langkah penempatan code pada modul standar.

Setelah langkah di atas diikuti satu-persatu, maka kita bisa menghapus scroll range menggunakan makro.

Caranya:

• Tekan Alt + F8 untuk memunculkan list Macro 
• Pilih macro yang sudah kita buat sebelumnya (macro deleteScrollArea)
• Lalu klik Run

Mengaktifkan Range Terpakai (Used Range)

Cara Ketiga dan yang paling fleksibel adalah dengan menetapkan used range sebagai scroll area. Untuk menempatkan code, caranya serupa dengan cara kedua yaitu:

• Klik kanan tab sheet yang ingin di-setting scroll area nya
• Pada module object sheet terpilih, ketik atau copy code berikut:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.ScrollArea = Me.UsedRange.Address
End Sub



• Setelah itu, keluar kembali dari VBA editor.

Karena code diatas akan dijalankan ketika sheet diaktifkan maka kita harus pindah dulu ke sheet lain, kemudian masuk kembali (mengaktifkan) ke sheet dimana code sudah disisipkan. Maka kita akan mendapati sheet dengan batasan scroll area sudah terpasang.

Jika kita ingin mengedit areal di luar used range, maka kita bisa mereset sementara scroll area dengan cara yang sudah dijelaskan sebelumnya

Sampai disini, mudahan-mudahan catatan excel mengenai cara membatasi scroll area dapat difahami dan semoga bermanfaat.
Thks

Artikel terkait:

• Cara mencegah save as, print, dan insert sheet.
• Membuat Daftar Isi Otomatis
• Macro untuk find & replance text dalam comment.

Referensi:
David Raina & Hawley 2007, Excel Hack, Tips & Tools for Streamlining Your Spreadsheet, 2nd edition. O’Reilly Media, Inc

Mencegah Save As, Print dan Insert Sheet

Sebagai pembuat sebuah template laporan, mungkin kita tidak menginginkan user melakukan tindakan tertentu. Misalnya melakukan save as, print, insert sheet, dan sebagainya. Dengan menambahkan sedikit code vba, kita bisa me-manage interaksi antara user dengan spreadsheet yang kita buat. Nah, jex-cel telah mencatat beberapa contoh code yang dapat digunakan untuk mencegah user menjalankan command tertentu di excel.

1. Mencegah Save As
2. Mencegah print
3. Mencegah print sheet tertentu
4. Mencegah insert sheet

Mencegah User Melakukan Save As

Atas dasar alasan tertentu, mungkin kita tidak ingin user melakukan save as dan mengganti nama file template yang kita buat. Untuk itu, kita bisa menyisipkan code pada module workbook.

• Jika kita membuat file baru, maka save dulu file tersebut sebelum disisipkan code. Jika workbook baru belum di save, dan kemudian kita menyisipkan code cegah save as maka file tersebut tidak akan bisa di-save sama sekali.
• Tekan Alt F11 untuk masuk ke VBA editor.
• Masuk ke module object Thisworkbook (dengan cara double klik object thisworkbook di jendela project explorer)
• Copy code berikut pada module object thisworkbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim responUser As Long
If SaveAsUI = True Then
responUser = MsgBox("Maaf, file tidak bisa di save as dengan nama lain" _
& Chr(10) & "Apakah anda akan menyimpan saja?", vbQuestion + vbOKCancel)
Cancel = (responUser = vbCancel)
If Cancel = False Then Me.Save
Cancel = True
End If
End Sub



Letak code dalam modul VBA dapat dilihat dalam screenshot berikut:

Setelah code di copy paste atau diketik di modul object thisworkbook, silahkan coba lakukan proses Save As

Maka akan muncul pesan seperti pada gambar di bawah ini:

Jika user menekan tombol ok, maka file akan disimpan biasa (bukan save as) dan tidak ada perubahan nama file. Jika user menekan cancel, maka perubahan tidak akan tersimpan.

Mencegah User Melakukan Print.

Karena melihat banyak kertas print berakhir di tempat sampah  atau tercecer / menumpuk di bawah meja kerja, kita bisa berinisiatif untuk membantu menghemat kertas dengan cara mencegah user untuk melakukan print. Untuk itu kita bisa menyisipkan code pada event before print di module thisworkbook.

• Tekan Alt F11 untuk masuk ke VBA editor
• Masuk ke module object Thisworkbook (dengan cara double klik object thisworkbook di jendela project explorer)
• Copy code berikut pada module object thisworkbook.


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox "Maaf anda tidak bisa print buku kerja ini", vbInformation
End Sub


Silahkan test dengan cara menjalankan command print (File --> Print --> Tombol Print)

Maka user akan melihat pesan pemberitahuan tidak bisa  print,  seperti gambar di bawah ini.

Kita bisa saja menyisipkan code tanpa code pesan (msgbox) seperti berikut:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub


Code tersebut sebenarnya sudah cukup untuk mencegah user melakukan print. Namun jika tidak ada pesan pemberitahuan, maka kemungkinan besar user akan menyangka ada error dan mengejar orang IT untuk memperbaikinya. Dengan kata lain, msgbox meski opsional tetapi sangat penting untuk memberitahu user mengenai apa yang tidak boleh dilakukan maupun yang harus dilakukan sehingga bisa mencegah adanya kesalahpahaman.

Mencegah print sheet tertentu

Jika kita ingin mencegah user melakukan print sheet tertentu saja maka gunakan code berikut:


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Select Case ActiveSheet.Name
Case "Sheet1", "Sheet2"
Cancel = True
MsgBox "Maaf anda tidak bisa print " & ActiveSheet.Name, vbInformation
End Select
End Sub


Catatan:  dalam contoh di atas, nama sheet yang dicegah untuk diprint adalah "Sheet1", "Sheet2". Kita dapat merubah dan menambah sheet sesuai keperluan.
Misalnya:

Case "data 1", "data 2", "data 3"

Mencegah Insert Sheet

Excel sebenarnya sudah menyediakan fiture Protect Workbook’s Structure yang dapat mencegah user untuk bisa men-delete worksheet, merubah susunan sheet, merubah nama sheet dan sebagainya. Namun adakalanya kita hanya ingin mencegah user untuk menambahkan sheet baru dan tetap mengizinkan perubahan struktur  lainnya.

Untuk mencegah user menambahkan sheet baru, lakukan langkah seperti contoh sebelumnya. Bedanya hanya pada code yang digunakan.

• Masuk ke VBA editor dengan cara tekan Alt + 11
• Selanjutnya Masuk ke module object Thisworkbook dengan cara double klik object thisworkbook di jendela project explorer
• Selanjutnya pada module object thisworkbook, copy code di bawah ini:


Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.DisplayAlerts = False
MsgBox "Maaf, anda tidak bisa menambahkan sheet baru", vbInformation
Sh.Delete
Application.DisplayAlerts = True
End Sub


Silahkan lakukan test dengan cara mencoba tambahkan sheet baru. Maka akan tampil pesan seperti screenshot di bawah ini.

Sekian dulu catatan mengenai contoh-contoh code untuk mencegah user melakukan tindakan tertentu pada excel, yaitu: mencegah save as, mencegah print dan mencegah insert sheet. Semoga bermanfaat.

Artikel terkait:
Teknik Find & Replace Text dalam Comment Box
Cara Entri Data Pada Beberapa Sheet Sekaligus
Cara Membuat Daftar Isi Otomatis

Referensi:
David Raina & Hawley 2007, Excel Hack, Tips & Tools for Streamlining Your Spreadsheet, 2nd edition. O’Reilly Media, Inc

Menghitung Jumlah Sel Dengan Kriteria Warna

Bagaimana cara menghitung jumlah sel dengan kriteria warna tertentu? Pertanyaan ini kerap muncul di forum-forum diskusi mengenai excel.

Nampaknya sampai dengan saat ini, solusi terbaik untuk menghitung banyaknya sel yang berwarna tertentu adalah dengan menggunakan VBA.


Nah, dalam kesempatan ini je-xcel akan memberikan tips singkat bagaimana membuat UDF (User Defined Function) untuk menghitung banyaknya sel dalam sebuah range yang memiliki warna tertentu.

Code VBA untuk menghitung banyak sel dengan kriteria warna tergolong sangat singkat. Perhatikan contoh berikut:

Function hitungWarna(selAcuan As Range, rangeWarna As Range)
Dim sel As Range
For Each sel In rangeWarna
    If sel.Interior.Color = selAcuan.Interior.Color Then
        hitungWarna = hitungWarna + 1
    End If
Next
End Function

UDF hitungWarna dapat digunakan dalam lembar kerja excel, setelah code diatas diketik atau dicopy pada module standar dalam jendela VBA, caranya sebagai berikut

• Masuk dulu ke dalam jendela VBA dengan cara tekan shorcut ALT+F11

• Dari jendela VBA, klik menu Insert , dan klik module

• Maka kemudian akan muncul module standar yang baru. Copy atau ketik code di atas ke dalam module:

• Save as file excel dengan type  file Excel macro enable workbook atau Excel binary workbook
• Sampai pada tahap ini, fungsi hitungWarna sudah dapat digunakan dalam lembar kerja excel.

Penjelasan fungsi:

Fungsi hitungWarna dapat digunakan untuk menghitung banyaknya sel didalam rangeWarna yang memiliki warna sama dengan selAcuan.

Contohnya:

selAcuan   = D1

rangeWarna = A1:B5

Maka untuk menghitung banyaknya sel di range A1:B5 yang memiliki warna yang sama dengan sel D1 dapat dituliskan rumusnya sebagai berikut:

=hitungwarna(D1,A1:B5)

Sesuai contoh gambar berikut:

Dari gambar tersebut, bisa disimpulkan bahwa fungsi hitungWarna berhasil menghitung banyaknya warna yang sama dengan sel D1 (kuning) pada range A1:B5.  (dalam contoh ada 3 warna)


Demikian tips singkat bagaimana menggunakan code VBA untuk menghitung banyaknya sel dengan kriteria warna.

Semoga bermanfaat.


Baca juga artikel belajar excel lainnya:

• Cara Cepat Membuat Hyperlink dengan VBA
• Macro untuk Setting Comment
• Macro untuk Melihat dan Menyembunyikan Comment dengan Cepat 

3 Cara Cepat Menghapus Baris Kosong di Excel

Ada kalanya kita berhadapan dengan baris-baris data excel yang tidak kontinyu, dimana baris data tersebut diselingi oleh baris-baris kosong yang secara teknis tidak diperlukan. Oleh karenanya baris-baris kosong tersebut harus dihapus atau dihilangkan. Bagaimana cara menghapus baris kosong tersebut?

Nah, dalam kesempatan belajar excel kali ini kita akan membahas bagaimana cara menghapus baris kosong di excel dengan cepat, mudah dan praktis.

Ada beberapa metode yang dapat dilakukan untuk menghapus baris kosong di excel. 3 diantaranya adalah :

• Memilih dan menghapus baris kosong menggunakan autofilter, 
• Memilih dan menghapus baris kosong menggunakan alat Go To
• Menghapus baris kosong menggunakan macro / VBA. 

Dalam tutorial ini akan dibahas satu persatu dari ketiga cara tersebut, disertai contoh dan gambah langkah-langkahnya.

Anggaplah kita memiliki data seperti contoh sederhana dalam gambar di bawah ini.

Gambar diatas hanya sebagai contoh saja, Manfaat terbesar akan terasa ketika kita berhadapan dengan jumlah baris data yang besar.

Cara 1: Memilih dan Menghapus Baris Kosong Menggunakan Autofilter

Autofilter dapat digunakan untuk menyaring data sesuai kriteria data dalam kolom terpilih. Nah dengan fungsinya tersebut, kita juga dapat memanfaatkan autofilter untuk menyaring sel kosong  atau blank.

• Kembali ke contoh. Seleksi kolom A dan B (entire column) menggunakan mouse.
• Dari tab Data, klik Autofilter (symbol corong), atau kalau mau lebih cepat, maka gunakan shortcut keyboar saja, tinggal tekan CTR+SHIFT+L

• Sehingga muncul dropdown list pada header kolom. Nah dari sini kita bisa memfilter baris kosong (blank)

• Klik salah satu header kolom dan hilangkan semua centang, kecuali centang pada (blank)

• Kemudian klik OK sehingga baris kosong sudah terfilter .
• Seleksi semua baris kosong yang terfilter tersebut.
• Klik kanan  dan Delete Row

• Maka jadilah semua baris kosong sudah hilang hilang dihapus.

• Kembalikan data terfilter dengan mencentang semua kategori melalui dropdown list

• Klik Ok 
• Selesai, kita sudah mengikuti langkah-langkah cara menghapus baris kosong menggunakan bantuan autofilter untuk memilih baris kosong, kemudian dilanjutkan dengan men-delete baris kosong tersebut

Cara 2: Memilih dan Menghapus Baris Kosong Menggunakan Alat Go

Sebenarnya ada kemiripan cara kerja untuk menghapus baris kosong, metode apapun yang digunakan. Yaitu:

PILIH  dan  HAPUS

Begitu juga dengan alat Go To

• Seleksi kolom A:B menggunakan mouse
• Tekan shortcut F5,  atau tekan shortcut CTR+G
• Maka akan muncul kotak dialog Go To
• Klik Special..

• Kemudian pilih Blank,   dan tekan OK

• Maka sel kosong dalam range akan terseleksi
• Selanjutnya klik kanan pada sel kosong yang sudah diseleksi tersebut.

• Pilih Entire row, kemudian klik OK

• Maka semua baris kosong akan terhapus.
• Selesai.

Cara 3 : Menghapus Baris Kosong Menggunakan Macro / VBA

Nah untuk cara yang ketiga ini kita perlu menuliskan kode VBA untuk memerintahkan excel supaya bisa menghapus baris kosong pada range terpilih.

• Copy atau ketik code berikut pada module standar.

Sub hapusBarisKosong()
Dim target As Range, i As Long
On Error GoTo skip
Set target = Selection
If WorksheetFunction.CountA(target) = 0 Then GoTo skip
For i = target.Rows.Count To 1 Step -1
   If WorksheetFunction.CountA(target.Rows(i)) = 0 Then
      target.Rows(i).Delete Shift:=xlUp
   End If
Next
Exit Sub
skip:
MsgBox "Range yang diseleksi tidak bisa di proses", vbCritical, "Error"
End Sub

• Jika anda baru mengenal VBA, anda bisa masuk ke jendela VBA dengan cara menekan shortcut ALT + F11

• Berikut adalah penampakan jendela VBA Excel:

• Kemudian dari menu insert, klik module, untuk memunculkan module standar.

• Copy atau ketik code tadi dalam module standar baru hasil insert module.

• Setelah code tersebut diketik atau dicopy ke module standar, maka kita sudah bisa menggunakannya untuk menghapus baris kosong pada range terpilih dalam lembar kerja excel.

• Kembali ke jendela excel

• Seleksi range yang akan dihapus baris kosongnya (jangan entire row ya) karena jika demikian code akan memproses sampai baris akhir data dan akan memakan waktu lama. Cukup seleksi range yang diperlukan saja. Misalnya range A1:B15

• Kemudian jalankan makronya.
• Masuk tab developer, klik pita Macro

• Nama makro yang sudah kita buat akan muncul dalam list makro. Pilih makro tersebut (hapusBarisKosong), kemudian klik RUN. Perhatikan gambar dibawah ini.

• Sampai pada tahap ini, kita sudah berhasil menghapus baris kosong menggunakan macro/ VBA

Ringkasan

Ada banyak jalan menuju roma, demikian juga dengan beberapa penyelesaian tugas di excel. Dalam belajar excel kali ini sudah dibahas bagaimana menggunakan 3 metode berbeda untuk menghapus baris kosong di excel, yaitu menggunakan bantuan autofilter, alat GoTo, dan macro/VBA. Silahkan dipilih, yang mana yang lebih nyaman anda menggunakannya. Atau jika pembaca memiliki metode lainnya, silahkan dishare di sini.

Terimakasih

Salam Excel.

Artikel Terkait:

• Cara Menghilangkan Spasi Berlebih
• Cara Grouping Baris dan Kolom di Excel
• Macro Untuk Setting Comment
• Macro Untuk Melihat dan Menyembunyikan Comment

Rumus Excel COUNTIF: Menghitung Sel Dengan Syarat

Fungsi COUNTIF Dalam Rumus Excel dapat digunakan untuk menghitung banyaknya sel yang memenuhi syarat, kriteria atau kondisi tertentu. Misalnya menghitung banyaknya sel kosong, menghitung sel berisi bilangan tertentu, sel berisi tanggal tertentu, maupun sel berisi text atau karakter tertentu. Fungsi COUNTIF tersedia pada berbagai versi excel, baik versi 2003, 2007, 2010, 2013 dan 2016. 

Belajar excel kali ini akan membahas fungsi COUNTIF secara rinci, dimulai dari pembahasan syntax dan dasar cara penggunaannya, kemudian dilanjutkan dengan pembahasan beberapa contoh penerapan rumus COUNTIF. Pada bagian akhir akan diulas problem solving atas beberapa masalah dan pertanyaan seputar penggunaan fungsi COUNTIF dalam rumus Excel.

• Syntax dan Cara Penggunaan Fungsi COUNTIF
• Contoh-Contoh Cara Menggunakan Fungsi COUNTIF Pada Excel
• Rumus COUNTIF Untuk Text dan Bilangan (Exact Match)
• Rumus COUNTIF Menggunakan Karakter Wildcard (Partial Match)
• Menghitung Banyaknya Sel Kosong atau Sebaliknya
• Menghitung Sel Jika Berisi Bilangan Lebih Besar, Lebih Kecil, Atau atau Sama Dengan
• Rumus COUNTIF Untuk Tanggal
• Rumus COUNTIF Dengan Dua Kriteria
• Menghitung Banyaknya Data Duplikat
• Menjawab Beberapa Permasalahan Seputar Rumus COUNTIF

Syntax dan Cara Penggunaan Fungsi COUNTIF

COUNTIF digunakan untuk menghitung banyaknya sel dengan kondisi atau kriteria tertentu. Syntax-nya pun sangat sederhana dan hanya memiliki 2 argumen:

=COUNTIF(range, criteria)

range : berupa referensi mewakili satu atau beberapa sel untuk dihitung, misalnya A1:A10

criteria: Kondisi tertentu yang menjadi syarat apakah sel tersebut akan dihitung atau tidak. Argumen kriteria bisa berupa bilangan, tanggal, string yang mewakili persamaan, maupun text tertentu. Kriteria ini juga bisa disimpan dalam sel yang dijadikan referensi.

Rumus berikut merupakan contoh sederhana cara menggunakan fungsi COUNTIF untuk menghitung berapa kali karyawan sebuah perusahaan yang tidak hadir karena sakit dalam kurun waktu 10 hari.

=COUNTIF(B2:B11,"sakit")

Jika criteria disimpan dalam sel E1, maka rumus berikut lebih dianjurkan karena lebih fleksible:

=COUNTIF(B2:B11,E1)

Catatan: criteria bersifat case insensitif sehingga tidak ada masalah apakah penulisan criteria menggunakan huruf kecil ataupun huruf kapital.

Contoh-Contoh Rumus Fungsi COUNTIF

Ternyata manfaat fungsi COUNTIF tidak sesederhana syntax nya. Dengan modifikasi argumen criteria, maka kita akan mendapatkan kegunaan beragam dari fungsi COUNTIF.

Rumus COUNTIF untuk Text dan Bilangan (exact match)

Mari kita lihat lembali contoh rumus COUNTIF untuk menghitung jumlah hari absensi karyawan sakit seperti dicontohkan pada sub bab pertama.

=COUNTIF(B2:B11,"sakit")

Perhatikan bagian-bagian dari argumen fungsi tersebut:

range = yaitu berupa referensi sel B2:B11

Tanda koma (,) berguna sebagai delimiter, atau pemisah argumen. Jika komputer anda menggunakan seting Indonesia, mungkin delimiternya adalah tanda titik koma atau semicolon (;)

Sebuah  kata dalam tanda kutip, yaitu kata "sakit" berperan sebagai argumen criteria. Daripada mengetikan  text secara langsung, kita dapat menggunakan sebuah referensi sel yang mengandung text atau kata yang menjadi kriteria. Anggaplah criteria absensi disimpan di sel E1, maka rumus untuk menghitung banyaknya absensi tertentu dapat dituliskan sebagai berikut:

=COUNTIF(B2:B11,E1)

Contoh diatas menggunakan kritera text. Bagaimana dengan kriteria bilangan?

Misalnya untuk menghitung berapa orang anak berumur 10 tahun dapat diilustrasikan sebagai berikut:

Perhatikan rumus yang digambarkan dalam ilustsrasi tersebut:

=COUNTIF(B2:B9,10)

Berbeda dengan kriteria text, penulisan kriteria bilangan tidak memerlukan tanda petik.

Dalam prakteknya, mungkin kita akan lebih mudah menggunakan kriteria yang disimpan dalam referensi sel daripada mengetikannya langsung pada rumus. Anggaplah kriteria disimpan dalam sel E1, maka rumusnya dapat ditulis begini:

=COUNTIF(B2:B9,E1)

Rumus COUNTIF Menggunakan Karakter Wildcard

Penggunaan karakter wilcard dalam rumus COUNTIF sangat berguna untuk menghitung banyaknya sel berisi text dengan kata kunci tertentu. Sebagai contoh adalah untuk menghitung jumlah nama siswa yang memiliki satu atau beberapa huruf awal tertentu atau mengandung kata tertentu (misal: marga atau nama keluarga)

Anggaplah kita memiliki sekumpulan nama siswa yang disimpan pada kolom A (range A2:A9) dalam lembar kerja excel / spreadsheet.

Pertanyaan: Bagaimana rumus excel untuk menghitung berapa banyak nama siswa yang diawali huruf tertentu? anggaplah nama siswa tersebut diawali huruf “B”.

Jawaban: Gunakan fungsi COUNTIF dengan argumen criteria berupa karakter “B” diikuti karakter wilcard asterisk (*). Sehingga rumus dapat dituliskan sebagai berikut:

=COUNTIF(A2:A9,"B*")

Seandainya text criteria disimpan di sel G1,maka rumus COUNTIF diatas dapat diubah menjadi:

=COUNTIF(A2:A9,G1&"*")

Silahkan dicoba dengan manipulasi berbagai posisi kata kunci:

Menghitung banyaknya siswa yang mengandung huruf  tertentu, tidak peduli dimana posisi huruf tersebut:

=COUNTIF(A2:A9,"*"&G1&"*")

Menghitung banyaknya siswa dengan huruf akhir tertentu

=COUNTIF(A2:A9,"*"&G1)

Dalam prakteknya kita bisa menggunakan text yang panjangnya lebih dari satu huruf sebagai kriteria, misalnya nama depan dan nama belakang. Silahkan dicoba dan dimodifikasi isi sel G1 dengan text yang lebih panjang dan  bervariasi. Kemudian perhatikan hasil yang diperoleh rumus COUNTIF.

Perlu diingat 

• Karakter wilcard asterisk (*) digunakan untuk mewakili karakter yang tidak ditentukan jumlahnya, sehingga sangat cocok untuk mencari nama dengan huruf awal tertentu, karena karakter yang menyusun nama, jumlahnya tidak tentu.
• Jika jumlah karakternya ditentukan maka gunakanlah karakter wildarcd tanda tanya (?)
• Untuk memperlakukan karakter wilcard sebagai karakter biasa, gunakan tanda gelombang (~) sebelum mengetikan karakter wilcard

Berikut contoh cara penulisan karakter wilcard dalam argumen critera fungsi COUNTIF

Contoh	Keterangan
"Mr *"	Mewakili text “Mr “ diikuti karakter bebas lainnya dengan jumlah tidak dibatasi
"Mr ?????"	Mewakili text “Mr “ diikuti 5 karakter bebas setelahnya.
"Mr ~***"	Secara persis mewakili karakter “Mr ***”
"Mr ~???"	Secara persis mewakili karakter “Mr ???”

Rumus Excel COUNTIF Untuk Menghitung Banyaknya Sel Kosong/Tidak Kosong

Contoh-contoh rumus excel berikut akan memperlihatkan bagaimana fungsi COUNTIF  dapat digunakan untuk menghitung banyaknya sel kosong (blank) dan sel tidak kosong (non blank) dalam range tertentu.

COUNTIF not blank - Menghitung Sel Tidak Kosong

Untuk menghitung banyaknya sel tidak kosong, kita dapat menggunakan rumus sebagai berikut:

=COUNTIF(range,"*")

atau

=COUNTIF(range,"<>"&"")


COUNTIF blank - Menghitung Sel Kosong

Sebaliknya, kita juga dapat menghitung banyaknya sel  kosong. Untuk itu gunakan logika terbalik dari contoh sebelumya, sehingga rumus menjadi:

=COUNTIF(range,"<>"&"*")

atau

 =COUNTIF(range,"")

Perlu diingat: Sebenarnya excel sudah menyediakan fungsi khusus untuk menghitung sel kosong dalam range tertentu, yaitu fungsi COUNTBLANK, Cukup ketikan =COUNTBLANK(range), maka kita sudah dapatkan banyaknya sel kosong dalam range.

Menghitung Banyaknya Sel Berisi Bilangan Dengan Nilai Lebih Besar, Lebih Kecil atau Sama Dengan Nilai Kriteria.

Apa yang perlu dilakukan untuk menghitung banyaknya sel yang memiliki nilai lebih besar, lebih kecil, atau sama dengan kriteria yang ditentukan?

Cukup tambahkan saja operator komparasi yang tepat sesuai kriteria, seperti contoh dalam tabel berikut:

Criteria	Contoh Rumus	Keterangan
Hitung jika  nilai lebih dari	=COUNTIF(B2:B9,">11")	Menghitung banyaknya sel dalam range B2:B9 yang bernilai lebih dari 11
Hitung jika nilai kurang dari	=COUNTIF(B2:B9,"<11")	Menghitung banyaknya sel dalam range B2:B9 yang bernilai kurang dari 11
Hitung jika nilai sama dengan	=COUNTIF(B2:B9,"=11")	Menghitung banyaknya sel dalam range B2:B9 yang bernilai sama dengan 11
Hitung jika nilai tidak sama dengan	=COUNTIF(B2:B9,"<>11")	Menghitung banyaknya sel dalam range B2:B9 yang nilainya tidak sama dengan 11
Hitung jika nilai minimal sama dengan	=COUNTIF(B2:B9,">=11")	Menghitung banyaknya sel dalam range B2:B9 yang bernilai 11 atau lebih.
Hitung jika nilai maksimal sama dengan	=COUNTIF(B2:B9,"<=11")	Menghitung banyaknya sel dalam range B2:B9 yang bernilai 11 atau kurang.

Perhatikan: sama halnya dengan penggunaan fungsi SUMIF, maka dalam fungsi COUNTIF juga kita harus menggunakan tanda petik dua untuk kriteria yang menggunakan kombinasi bilangan dan operator komparasi.

Contoh rumus COUNTIF dalam tabel di atas menggunakan kriteria yang diketikan langsung dalam rumus. Jika kita ingin menempatkan kriteria dalam sel supaya kedepannya dapat lebih mudah merubah kriteria tanpa merubah rumus. Maka gunakan operator ampersand (&) untuk menggabungkan operator komparasi dengan referensi yang menjadi kriteria.

Contohnya:
=COUNTIF(B2:B9,">11")

Jika kriteria disimpan di sel D1, maka rumus tersebut dapat dirubah menjadi:
=COUNTIF(B2:B9,">"&D1)

Untuk lebih mudah memahaminya, perhatikan gambar berikut:

Sebagian pembaca mungkin bertanya-tanya dalam hati. Bagaimana cara memperlakukan tanda lebih dari (>), kurang dari (<) dan sama dengan (=) sebagai karakter biasa, bukan sebagai operator komparasi (perbandingan).

Untuk kasus tersebut kita bisa menggagungkan operator komparasi dengan karakter wilcard.

Misalnya : =COUNTIF(B2:B9,"*>11*")

Formula tersebut akan menghitung banyaknya sel dalam range B2:B9 dimana sel tersebut mengandung text “>11”, Dalam contoh tersebut, tanda lebih dari (>) terbaca sebagai karakter biasa

Menggunakan Fungsi COUNTI F dengan Kriteria Tanggal

Jika kita ingin menghitung banyaknya tanggal tertentu, maka gunakan fungsi COUNTIF dengan criteria data tanggal. Hal serupa dapat dilakukan untuk menghitung berapa banyak data tanggal sebelum atau sesudah tanggal tertentu, tentunya hampir sama penggunannya dengan fungsi COUNTIF kriteria bilangan yaitu menggunakan operator komparasi “<” , “>”, dan “=”

Perhatikan contoh rumus dalam tabel berikut:

Criteria	Contoh Rumus	Keterangan
Menghitung banyaknya tanggal tertentu	=COUNTIF(B2:B9,"12/15/2016")	Menghitung banyaknya tanggal 15 Desember 2016 pada range B2:B9
Menghitung banyaknya tanggal tertentu dan tanggal sesudahnya.	=COUNTIF(B2:B9,">=12/15/2016")	Menghitung banyaknya tanggal 15 Desember 2016 dan tanggal sesudahnya pada range B2:B9
Menghitung banyaknya tanggal dimulai dari beberapa hari sebelum tanggal ditentukan	=COUNTIF(B2:B9,">="&G1-7)	Menghitung banyaknya tanggal pada range B2:B9, dimulai dari 7 hari sebelum tanggal yang disimpan di sel G1

Dengan memperhatikan  contoh rumus atau formula dalam tabel diatas, kita bisa mengetikan langsung tanggal sebagai kriteria (contoh: "12/15/2016") dan menggunakan referensi sel (contoh : G1).

Selain dari kedua cara penulisan argumen kriteria diatas (ketik langsung tanggal maupun referensi sel), kita juga dapat menggunakan fungsi lainnya sebagai criteria. Misalnya fungsi TODAY.  Fungsi ini berguna untuk mendapatkan data tanggal sekarang sesuai setting komputer.

Criteria	Contoh Rumus
Menghitung banyaknya tanggal yang sama dengan tanggal sekarang	=COUNTIF(B2:B9,TODAY())
Menghitung banyaknya tanggal sebelum tanggal sekarang	=COUNTIF(B2:B9,"<"&TODAY())
Menghitung banyaknya tanggal setelah tanggal sekarang	=COUNTIF(B2:B9,">"&TODAY())
Menghitung banyaknya tanggal yang sama dengan tanggal seminggu kedepan dari sekarang	=COUNTIF(B2:B9,"="&TODAY()+7)
Menghitung banyaknya tanggal dimulai tanggal tertentu tetapi kurang dari tanggal tertentu	=COUNTIF(B2:B9, ">=12/1/2016")-COUNTIF(B2:B9, ">12/15/2016")

Berikut contoh formula menggunakan fungsi TODAY sebagai argumen criteria dalam fungsi COUNTIF. Rumus ini digunakan untuk menghitung banyaknya tagihan yang sudah jatuh tempo.

Keterangan: artikel ini ditulis pada tanggal 16 Desember 2016, sehingga rumus TODAY() menghasilkan nilai tanggal 16 Desember 2016

Fungsi COUNTIF dengan Kriteria Ganda

Sebenarnya fungsi COUNTIF di excel tidak di-design untuk menghitung banyaknya sel yang memenuhi kriteria atau kondisi yang lebih dari satu. Namun demikian fungsi ini juga dapat digunakan untuk keperluan tersebut terutama pada excel versi 2003 atau yang lebih lama. Untuk excel versi 2007 ke atas, tugas ini sudah digantikan oleh fungsi COUNTIFS (lihat: ada huruf “S”)

Contoh 1 : Fungsi COUNTIF dengan Kondisi Ganda berupa bilangan

Fungsi COUNTIF kriteria ganda umumnya digunakan untuk menghitung banyaknya data yang memenuhi rentang kriteria atau kondisi tertentu, dibatasi nilai minimal dan nilai maksimal.

Misalnya untuk menghitung jumlah anak berumur 10 s.d 12 tahun sesuai data umur pada range B2:B9 maka dapat dirumuskan sebagai berikut:

=COUNTIF(B2:B9,">=10")-COUNTIF(B2:B9,">12")

Contoh 2 : Fungsi COUNTIF dengan Kondisi Ganda berupa Text

Mari kita perhatikan satu lagi contoh rumus COUNTIF dengan dua kriteria untuk menghitung banyaknya item barang yang mengandung text tertentu.

Anggaplah anda sedang belanja di pasar buah, dan ingin menghitung berapa banyak item belanjaan buah jeruk dan mangga.

=COUNTIF(A2:A9,"*Jeruk*")+COUNTIF(A2:A9,"*mangga*")

Perhatikan penggunaan karakter wildcard asterisk pada contoh di atas. Karakter tersebut digunakan untuk mewakili sejumlah karakter lainnya termasuk string kosong (tidak ada karakter sama sekali), baik sebelum maupun sesudah kata kunci. Sehingga contoh rumus tersebut dapat menghitung semua benda yang mengandung kata mangga atau jeruk.

Dengan cara yang sama kita juga bisa menambahkan fungsi COUNTIF lainnya untuk menghitung lebih banyak kriteria.

Contohnya: untuk menghitung semua item belanja jeruk, mangga dan pisang maka dapat menggunakan rumus berikut:

=COUNTIF(A2:A9,"*Jeruk*")+COUNTIF(A2:A9,"*mangga*")+COUNTIF(A2:A9,"*pisang*")

Menggunakan Fungsi COUNTIF untuk Menemukan Nilai Duplikat dan Nilai Unik

Kegunaan lainnya dari fungsi COUNTIF pada Excel adalah untuk menemukan nilai duplikat, baik pada satu kolom, antara dua kolom maupun dalam baris.

Contoh 1 : Menemukan dan Menghitung Banyaknya Duplikat dalam 1 Kolom

Sebenarnya dari awal pembahasan kita sudah memahami cara menghitung banyaknya duplikat data di excel. Hal ini karena fungsi COUNTIF sendiri berguna untuk menghitung sel dengan kondisi yang sama.  Artinya jika kita menghitung sel dengan kondisi yang persis sama, maka kita juga bisa mengetahui adanya duplikat jika rumus menghasilkan nilai lebih dari satu.

Menggunakan Kolom Bantu

Untuk duplikat, kita bisa menandainya dengan bantuan operator komparasi lebih dari (>) untuk mengecek hasil fungsi COUNTIF. Jika hasil lebih dari 1 maka ditandai TRUE, dan jika tidak ada dplikat maka ditandai FALSE. cek TRUE/FALSE ini disimpan dalam sebuah kolom bantu.

Sebagai contoh, rumus sederhana =COUNTIF($A$2:$A$10,$A2)>1 dapat digunakan untuk mengecek apakah nilai dalam sel A2 memiliki duplikat atau tidak pada range A2:A10. Dengan mengcopy rumus tersebut ke baris dibawahnya maka kita bisa mengecek semua duplikat pada range A2:A10

Selanjutnya, untuk menghitung banyaknya duplikaat yaitu dengan carmenghitung banyaknya TRUE dalam kolom bantu seperti rumus berikut:
=COUNTIF(B2:B10,TRUE)

Tanpa Kolom Bantu

Untuk menghitung banyaknya nilai duplikat tanpat kolom bantu, maka kita dapat menggunakan fungsi SUMPRODUCT yang dikombinasikan dengan COUNTIF.

=SUMPRODUCT((COUNTIF(A2:A10,A2:A10)>1)*(A2:A10<>""))

Untuk lebih jelasnya, perhatikan screenshoot berikut yang mengilustrasikan bagaimana cara menggunakan rumus excel untuk menghitung nilai duplikat, baik menggunakan kolom bantu ataupun tidak.

Contoh 2 : Menghitung Banyaknya Duplikasi Antara Dua Kolom

Jika kita memiliki dua list terpisah, katakanlah  list nama pada kolom B dan C. Kemudian kita ingin mengetahui berapa kali nama-nama tersebut muncul pada kedua kolom.

Cara menghitungnya adalah menggunakan kombinasi antara fungsi SUMPRODUCT dengan fungsi COUNTIF

➽ Menghitung banyaknya nama di kolom A yang muncul kembali di kolom B

=SUMPRODUCT((COUNTIF(A2:B10,B2:B10)>1)*(B2:B10<>""))

➽ Menghitung banyaknya nama di kolom B yang tidak ada di kolom A

=SUMPRODUCT((COUNTIF(A2:B10,B2:B10)=1)*(B2:B10<>""))

Contoh diatas hanya digunakan untuk list sampai baris 10. Untuk data yang lebih banyak silahkan dimodifikasi referensi range tersebut. Misal: A2:B10 menjadi A2:B100 atau A2:B1000 dan seterusnya.

Contoh 3 : Menghitung Banyaknya Nilai Duplikat dan Nilai Unik dalam Sebuah Baris

Kombinasi antara fungsi SUMPRODUCT  dan fungsi COUNTIF  juga diperlukan untuk  menghitung banyaknya nilai duplikat maupun nilai unik dalam baris,

➽ Menghitung banyaknya nilai duplikat dalam baris (range A1:M1)

=SUMPRODUCT((COUNTIF(A1:M1,A1:M1)>1)*(A1:M1<>""))

➽ Menghitung banyaknya nilai unik dalam baris (range A1:M1)

=SUMPRODUCT((COUNTIF(A1:M1,A1:M1)=1)*(A1:M1<>""))

Penjelasan dan Pemecahan Masalah Seputar Penggunaan Fungsi COUNTIF

Setidaknya ada 5 masalah yang paling sering ditanyakan seputar penggunaan fungsi COUNTIF pada Excel. Berikut akan  akan disampaikan kelima masalah tersebut dan solusinyaj. Semoga bermanfaat.

1. COUNTIF Pada Range Terputus

Pertanyaan: Apakah Fungsi COUNTIF dapat digunakan terhadap range yang terputus, atau beberapa sel yang diseleksi.

Jawaban : Fungsi COUNTIF tidak dapat bekerja pada range yang terputus, gabungan beberapa range atau beberapa sel.

Solusi: untuk mendapatkan perhitungan pada beberapa range atau sel yang terpisah maka kita harus menggunakan beberapa fungsi COUNTIF.

Rumus yang salah:
=COUNTIF(A2,B5,C2,">0")

Rumus yang Benar:
 =COUNTIF(A2,">0") + COUNTIF(B5,">0") + COUNTIF(C2,">0")

Alternatif solusi lainnya adalah menggunakan kombinasi fungsi SUM, COUNTIF dan INDIRECT
Printscreen berikut menggambarkan cara penggunaan jumlah bilangan 0 pada dua range yang berbeda yaitu range B2:B10 dan C2:C10

Cara 1 : Menggunakan beberapa fungsi COUNTIF

=COUNTIF(B2:B10,0)+COUNTIF(C2:C10,0)

Cara 2 : Menggunakan kombinasi SUM, COUNTIF dan INDIRECT.

=SUM(COUNTIF(INDIRECT({"B2:B10","C2:C10"}),0))

2. Ampersand dan tanda petik dalam rumus COUNTIF

Pertanyaan: Kapan kita perlu menggunakan tanda ampersand (&) dan tanda petik ("") di dalam formula COUNTIF?

Jawaban: Penggunaan ampersand dan tanda kutip dalam fungsi COUNTIF mungkin agak membingungkan. Saya sendiri merasakan hal demikian ketika pertama kali bergelut dengan rumus ini. Namun dengan menelaahnya dengan sesama, kita akan memahami bahwa penggunaan ampersand dan tanda  petik dua ternyata adalah untuk membangun string argumen criteria. Ikuti aturan main berikut dalam menggunakan ampersand dan tanda petik:

• Jika kita menggunakan bilangan atau referensi sel sebagai criteria yang persis sama (exact match) maka tanda ampersand maupun tanda petik tidak diperlukan

Contoh:
=COUNTIF(A1:A10,10)
=COUNTIF(A1:A10,C1

• Jika kriteria yang digunakan berupa text, karakter wildcard atau operator komparasi yang digabung dengan bilangan maka gunakanlah tanda petik.

Contoh:
=COUNTIF(A2:A10,"pisang")
=COUNTIF(A2:A10,"*")
=COUNTIF(A2:A10,">10")

• Jika kita menggunakan expresi logika dengan operator komparasi yang mengacu pada sel referensi atau fungsi excel lainnya, maka kita perlu meletakan operator komparasi dalam tanda petik ("") dan menggunakan ampersand  (&) untuk menggabungkan operator komparasi dengan referensi atau fungsi lainnya.

Contoh:
=COUNTIF(A2:A10,">"&D2)
=COUNTIF(A2:A10,"<="&TODAY())

• Jika kita merasa ragu apakah ampersand dan tanda petik diperlukan atau tidak, maka kita dapat mencoba keduanya. Biasanya kedua cara tersebut sama-sama bekerja dan menghasilkan nilai yang sama.

Contoh:
Formula =COUNTIF(C2:C8,"<=5")
Sama saja hasilnya jika dituliskan =COUNTIF(C2:C8,"<="&5)

3. Menghitung Banyaknya Sel Dengan Warna Tertentu

Pertanyaan : Bagaimana cara menghitung sel berdasarkan warna

Jawaban : Sangat disayangkan bahwa fungsi COUNTIF ternyata tidak bisa digunakan untuk menghitung banyaknya sel berdasarkan warna.

Solusi : Satu-satunya cara yang dapat digunakan untuk menghitung banyaknya sel berdasarkan warna adalah menggunakan macro atau code VBA.

Contoh:

Code VBA berikut dapat digunakan untuk menghitung banyaknya sel pada rangeWarna sesuai warna pada selAcuan

Copy code berikut pada modul standar dalam vba editor (untuk membuka vba editor , tekan ALT + F11, kemudian tekan insert, dan tekan module untuk memunculkan module standar baru)

Function hitungWarna(selAcuan As Range, rangeWarna As Range)
Dim sel As Range
For Each sel In rangeWarna
    If sel.Interior.Color = selAcuan.Interior.Color Then
        hitungWarna = hitungWarna + 1
    End If
Next
End Function

Fungsi hitungWarna ini kita kenal sebagai salah satu contoh dari User Defined Function (UDF = fungsi yang dibuat sendiri oleh user). Fungsi ini dapat digunakan dalam lembar kerja excel seperti halnya menggunakan fungsi biasa,

Contoh rumus :   =hitungwarna(D1,A2:A9)

Rumus tersebut digunakan untuk menghitung sel pada range A2:A9 dengan warna yang sama dengan sel D1.

5. Rumus COUNTIF menghasilkan nilai Error #NAME?

Pertanyaan: Bagaimana cara mengatasi error #NAME? pada rumus COUNTIF?

Jawaban : error #NAME? disebabkan karena kesalahan penulisan/spelling fungsi, penulisan referensi, atau kesalahan penggunaan nama range dan nama sel.
Misal COUNTIF diketik COUNTIFF

Solusi : Cek kembali carai penulisan fungsi, range, nama range atau nama sel. Jika menggunakan nama range atau nama sel, cek apakah nama tersebut sudah didefinisikan.

5. Rumus Excel COUNTIF tidak bekerja

Pertanyaan : Saya sudah membuat rumus COUNTIF dengan syntax yang benar. Namun kenapa tidak menghasilkan data yang benar ketika dicopy ke sel lain.

Jawaban: Hal ini disebabkan calculation setting di-set manual.

Solusi : Tekan F9 atau ubah calculation setting menjadi automatic. Penjelasan lebih detail tentang kenapa rumus excel tidak berfngsi dapat dibaca pada artikel: Rumus Excel Tidak Berfungsi? Inilah Penyebabnya!

Akhir kata, demikian pembahasan fungi COUNTIF dan contoh-contoh rumus terapannya dalam lembar kerja excel. Semoga bermanfaat.

Artikel Terkait

• Fungsi SUMIF dan Rumus Penjumlahan Bersyarat Pada Excel
• Fungsi SUM dan Rumus Penjumlahan Pada Excel
• Rahasia Cara Cepat AutoSum
• Rumus Excel Tidak Berfungsi? Inilah Penyebabnya!

Referensi:
https://www.ablebits.com/office-addins-blog/2014/07/02/excel-countif-examples/