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/

Fungsi SUMIF dan Rumus Penjumlahan Bersyarat

Belajar excel kali ini dimaksudkan untuk membahas tehnik penjumlahan bersyarat menggunakan fungsi SUMIF. Penjelasan diupayakan sedetail dan selengkap mungkin disertai contoh- contoh praktikal rumus SUMIF dengan kriteria bilangan, text, tanggal dan karakter wildcard. Pada bagian akhir akan dibahas bagaimana mengatasi beberapa permasalahan dan error seputar penggunaan fungsi SUMIF.

Sesuai namanya, SUMIF merupakan gabungan SUM dan IF, dapat diartikan “Jumlahkan Jika”. Dengan kata lain fungsi ini digunakan untuk melakukan penjumlahan jika syarat atau kondisi tertentu terpenuhi. Oleh karena itu sering juga diistilahkan sebagai penjumlahan kondisional (conditional sum)

Fungsi SUMIF dapat digunakan baik pada excel versi lama (2003 atau yang lebih awal) maupun pada excel versi baru (2007,2010,2013 dan 2016). Menarik sekali, jika kita sudah memahami rumus SUMIF, maka kita juga akan lebih mudah untuk memahami rumus fungsi lainnya yang sejenis seperti SUMIFS, COUNTIF, COUNTIFS dan AVERAGEIF.

• Rumus Fungsi Excel SUMIF - Syntax dan Cara Penggunaan
• Contoh-Contoh Rumus Excel SUMIF
• Penjumlahan Beryarat Bilangan Lebih Rendah, Lebih Tinggi atau Sama Dengan.
• Rumus SUMIF dengan Syarat Kriteria Text
• Rumus SUMIF Menggunakan Operator Komparasi Dengan Referensi Sel
• Contoh-Contoh Rumus SUMIF Menggunakan Karakter Wildcard
• Rumus SUMIF Untuk Menjumlahkan Bilangan Terbesar Atau Terkecil
• Rumus SUMIF Untuk Penjumlahan Bersyarat Terkait Sel Kosong/Tidak Kosong
• Rumus SUMIF Untuk Penjumlahan Bersyarat  dengan Kriteria Tanggal
• Rumus SUMIF Untuk Penjumlahan Bilangan Pada Beberapa Kolom
• Solusi Masalah Rumus SUMIF Error, tidak Bekerja atau Tidak Berfungsi dengan Benar

Syntax Dan Cara Penggunaan Fungsi SUMIF di Excel

Fungsi SUMIF digunakan untuk melakukan operasi penjumlahan bersyarat dengan berdasarkan kriteria atau kondisi tertentu. Adapun Syntax dari fungsi ini adalah sebagai berikut:

SUMIF(range, criteria, [sum_range])

Dari ketiga argumen fungsi SUMIF tersebut, Argumen pertama (range) dan kedua (criteria) bersifat wajib, sedangkan argumen yang ketiga (sum_range) bersifat opsional atau boleh diabaikan. Akan tetapi, meskipun bersifat opsional, argumen ke-3 penting dan sering diperlukan terutama jika argumen pertama berisi data text.

Range = Sekelompok sel yang akan dievaluasi , misalnya A1:A10

Criteria = syarat atau kondisi yang harus terpenuhi. Kriteria ini bisa berbentuk bilangan, text, tanggal, expresi logika, referensi sel maupun fungsi excel lainnya. misalnya kita dapat menggunakan kritera seperti 3, 10, 0, “jeruk”, “10/12/2016””, “<4”, “B2” dan sebagainya.

Perlu diingat! Kriteria text yang mengandung simbol matematika harus diawali dan diakhiri dengan tanda petik 2 ("). Sedangkan untuk kriteria numerik, tidak diperlukan tanda petik.

Sum_range = kumpulan sel untuk dijumlahkan jika kondisi terpenuhi. Argumen ini bersifat opsional dan jika tidak ada maka rumus SUMIF akan menjumlahkan data pada argumen pertama (argumen range)

Untuk memperjelas gambaran syntax fungsi SUMIF di excel mari kita perhatikan contoh berikut.

Anggaplah anda memiliki  list beberapa produk pada kolom A dan jumlahnya pada Kolom C.

Selanjutnya kita akan mencoba menjumlahkan semua stock produk tertentu, misalnya nanas:

Perhatikan gambar di atas dengan seksama dan selanjutnya mari kita definisikan masing masing argumen untuk fungsi SUMIF

range - A2:A9
criteria - "Nanas"
sum_range - C2:C9

Kemudian susunlah parameter-parameter tersebut di dalam fungsi SUMIF untuk melakukan penjumlahan bersyarat, =SUMIF(A2:A9,"Nanas",C2:C9)

Contoh rumus excel tersebut mendemonstrasikan penggunaan fungsi SUMIF yang paling sederhana dengan kriteria text nama buah nanas.

Dikarenakan kriteria “Nanas” disimpan di sel F1, maka rumus di atas dapat ditulis ulang kembali dengan cara mengganti argumen criteria  “Nanas” menjadi referensi sel F1.

=SUMIF(A2:A9,"F1",C2:C9)

Penting diketahui: Dalam prakteknya, sum_range tidak harus sama ukuran atau dimensinya dengan argumen range. Fungsi SUMIF akan menjadikan sel pertama paling atas kiri dari dari argumen sum_range kemudian menghitung dengan mengambil ukuran dimensi yang sama dengan argumen range.

Dengan memperhatikan kembali contoh rumus SUMIF diatas, maka kita dapat menyediakan sel C2 atau C2:C4, atau bahkan C2:C100 sebagai argumen sum_range. Dan hasilnya yang diperoleh sama saja.

Satu hal yang harus dipastikan adalah sel pertama dari argumen sum_range harus sejajar dengan sel pertama dari argumen range. 

Sebagaimana contoh diatas, sel yang sejajar dari kedua argumen tersebut adalah

A2 = sel pertama dari referensi argumen range 

C2 = sel pertama dari referensi argumen sum_range

Akan tetapi, untuk kemudahan membaca rumus, disarankan untuk mengetikan referensi sum_range dengan ukuran atau dimensi yang sama dengan referensi range. 

Dalam kaitan contoh diatas, disarankan menggunakan referensi C2:C9 sebagai argumen sum_range, dimana referensi tersebut berdimensi sama dengan referensi A2:A9 yang merupakan argumen range.

Beberapa Contoh Bagaimana cara Menggunakan SUMIF di Excel

Diharapkan contoh sederhana yang sudah ditampilkan di atas dapat memberikan pemahaman dasar bagaimana fungsi SUMIF bekerja. Berikutnya kita akan menemukan beberapa contoh rumus yang mendemonstrasikan bagaimana SUMIF dapat bekerja dengan berbagai kriteria dan berbagai jenis set data yang berbeda.

Fungsi SUMIF  dengan Kriteria Lebih Dari, Kurang Dari atau Sama Dengan

Mari kita perhatikan beberapa rumus SUMIF yang dapat digunakan untuk menjumlahkan bilangan dengan nilai lebih dari (>), kurang dari (<) atau sama dengan (=) nilai kriteria yang ditentukan.

Catatan : Perhatikan bahwa pada rumus SUMIF, kita menggunakan operator komparasi (operator perbandingan) diikuti bilangan atau text yang berada dalam tanda petik dua ("")

Criteria	Operator	Contoh Rumus	Keterangan
Jumlahkan Jika Lebih Dari	>	=SUMIF(B2:B10, ">5")	Menjumlahkan bilangan pada kumpulan sel B2:B10 jika nilai bilangan  lebih dari 5
Jumlahkan Jika Kurang Dari	<	=SUMIF(B2:B10, "<10", C2:C10)	Menjumlahkan bilangan pada sum_range C2:C10 jika sel sejajar pada range B2:B10 bernilai kurang dari 10
Menjumlahkan jika sama dengan	= (Dapat ditiadakan)	=SUMIF(B2:B10, "="&D1) or =SUMIF(B2:B10,D1)	Menjumlahkan bilangan pada range B2:B10 jika nilainya sama dengan nilai di sel D1. Argumen kriteria bisa merujuk langsung pada referensi.
Menjumlahkan jika tidak sama dengan	<>	=SUMIF(B2:B10, "<>"&D1, C2:C10)	Menjumlahkan bilangan pada sum_range C2:C10 jika sel sejajar di range B2:B10 berbeda nilainya dengan sel D1
Menjumlahkan jika nilai lebih dari atau sama dengan	>=	=SUMIF(B2:B10, ">=5")	Menjumlahkan bilangan pada range B2:B10 jika nilainya minimum 5
Menjumlahkan jika kurang dari atau sama dengan	<=	=SUMIF(B2:B10, "<=10", C2:C10)	Menjumlahkan  bilangan pada sum_range C2:C10 jika sel sejajar di range B2:B10 bernilai maksimum 10.

Bagaimana Cara Menggunakan Fungsi SUMIF Excel dengan Kriteria Berupa Text

Selain syarat bilangan, fungsi SUMIF juga memungkinkan penggunanya untuk menambahkan nilai tergantung syarat text pada sel sejajar di kolom lainnya.

Silahkan diperhatikan kembali contoh-contoh rumus SUMIF berikut untuk menjumlahkan dengan syarat text persis sama (exact match) atau secara sama sebagian (partial match).

Criteria	Contoh Rumus	Keterangan
Menjumlahkan Jika Sama Dengan	Exact match: =SUMIF(A2:A8, "pisang", C2:C8)	Menjumlahkan bilangan pada sum_range C2:C8, jika sel sejajar  pada range A2:A8 hanya berisi text “pisang”
	Partial match: =SUMIF(A2:A8, "*pisang*", C2:C8)	Menjumlahkan bilangan pada sum_range C2:C8, jika sel sejajar  pada range A2:A8 mengandung  text “pisang”
Menjumlahkan jika sama dengan	Exact match: =SUMIF(A2:A8, "<>pisang", C2:C8)	Menjumlahkan bilangan pada sum_range C2:C8, jika sel sejajar pada range A2:A8 tidak hanya berisi text “pisang”. Makanya jika berisi pisang raja, pisang ambon tetap akan terjumlahkan.
	Partial match: =SUMIF(A2:A8, "<>*pisang*", C2:C8)	Menjumlahkan bilangan pada range sel C2:C8, jika sel sejajar  pada range A2:A8 tidak mengandung text “pisang” sama sekali.

Selanjutya mari kita lihat penjumlahan secara persis (eksak) dengan kriteria “Jumlahkan Jika Tidak Sama Dengan”.  Sebagaimana pada screenshot berikut, untuk menjumlahkan jumlah stock semua produk selain “ pisang raja”

=SUMIF(A2:A9,"<>Pisang Raja",C2:C9)

Catatan: Seperti kebanyakan fungsi Excel lainnya, SUMIF juga besifat case-insensitive artinya tidak membedakan huruf kecil maupun huruf kapital. Oleh karenanya tidak ada bedanya apakah anda menuliskan “pisang” atau “PISANG”.

Menggunakan Operator Komparasi (Perbandingan) dengan Referensi Sel

Untuk mendapatkan rumus yang lebih fleksibel maka sebaiknya kita menempatkan kriteria di dalam sel, alih-alih mengetikannya langsung dalam rumus. Ini bertujuan supaya ketika ada perubahan kriteria maka kita tidak perlu repot mengganti rumus, tapi cukup dengan mengganti kriteria pada sel yang menjadi referensi.

Catatan : Gunakan operator ampersand (&) atau fungsi CONCATENATE  untuk menggabungkan operator komparasi dan referensi sel supaya dapat dijadikan sebagai argumen kriteria dalam rumus SUMIF, sebagaimana digambarkan dalam screenshot di atas..

=SUMIF(A2:A9,"<>"&F1,C2:C9)

Juga dapat dituliskan sebagai berikut:

=SUMIF(A2:A9,CONCATENATE("<>",F1),C2:C9)

Untuk logika “sama dengan”, kita bisa menggunakan operator “=” ataupun tidak, sehingga kedua contoh formula berikut akan menghasilkan nilai yang sama.

Formula 1: =SUMIF(A2:A8, "="&F1, C2:C8) 

Formula 2: =SUMIF(A2:A8, F1, C2:C8)

Cara Menggunakan Rumus SUMIF dengan Karakter Wildcard

Karakter wildcard digunakan untuk melakukan penjumlahan bersyarat text secara parsial. Maksudnya adalah mengambil bagian text tertentu sebagai argumen criteria dalam rumus SUMIF, oleh karena itu sering disebut dengan istilah partial match.

Berikut jenis-jenis karakter wildcard yang dapat digunakan

• Asterik (*) = memakili sejumlah karakter
• Tanda Tanya (?) = mewakili karakter tunggal pada posisi spesifik

Contoh 1 : Menjumlahkan nilai dengan kriteria text parsial

Anggaplah, kita akan menjumlah kan sesuatu yang terkait dengan pisang.Tidak peduli jenisnya atau  bendanya apa, yang penting mengandung kata “pisang”.

Penjumlahan bersyarat text “pisang” di bagian awal text kriteria

=SUMIF(A2:A9,"Pisang*",C2:C9)

Penjumlahan bersyarat text “pisang” di bagian akhir text kriteria

=SUMIF(A2:A9,"*Pisang",C2:C9)

Penjumalahan bersyarat text pisang di sembarang posisi dalam text kriteria

=SUMIF(A2:A9,"*Pisang*",C2:C9)

Perhatikan ilustrasi berikut:

Dan untuk menjadikan rumus lebih fleksibel maka sebaiknya kriteria ditempatkan dalam sel. Misalnya di sel H1, sehingga rumus SUMIF menjadi:

Penjumlahan bersyarat partial match di bagian awal text kriteria

=SUMIF(A2:A9,H1&"*",C2:C9)

Penjumlahan bersyarat text “pisang” di bagian akhir text kriteria

=SUMIF(A2:A9,"*"&H1,C2:C9)

Penjumalahan bersyarat text pisang di sembarang posisi dalam text kriteria

=SUMIF(A2:A9,"*"&H1&"*",C2:C9

Perhatikan kembali contoh diatas: gunakan operator ampersand (&) untuk menggabungkan referensi dengan  karakter wildcard asterisk (*) dalam tanda kutip.

Contoh 2 . Menjumlahkan bilangan dengan syarat sejumlah huruf atau karakter.

Anggaplah kita menetapkan sejumlah karakter sebagai syarat penjumlahan menggunakan fungsi SUMIF. Misalnya menjumlahkan bilangan pada range C2:C9 jika sel sejajar pada range B2:A9 mengandung text dengan panjang 5 karakter:

=SUMIF(B2:B9, "?????", C2:C9)

Contoh 3  : Menjumlahkan sel yang berkesesuaian dengan text tertentu

Jika kita bekerja dengan beberapa type data dalam lembar kerja excel, kemudian kita ingin menjumlahkan beberapa bilangan, anggaplah dalam sebuah sum_range dengan syarat sel sejajar di range lainnya.

=SUMIF(B2:B9,"?*", C2:C9) 
– Menjumlakan bilangan yang ada di Sel C2:C9 jika sel sejajar di kolom C mengandung minimal 1 karakter.

=SUMIF(B2:B9,"*", C2:C9)
– Menjumlakan bilangan yang ada di Sel C2:C9 jika sel sejajar di kolom B berisi, termasuk string dengan panjang nol yang diperoleh dari rumus lainnya, misalnya =””

Kedua rumus diatas mengabaikan nilai non text seperti error, boolean, bilangan dan data.

Contoh 4. Memperlakukan * dan ? sebagai karakter biasa.

Terkadang mungkin kita ingin menggunakan * dan ? sebagai karakter biasa, bukan sebagai wildcard, maka gunakanlan tanda gelombang / tidle (~) sebelum tanda * atau ?. 

=SUMIF(A2:A8, "~?", C2:C8)

Perhatikan screenshot diatas. Contoh ke-1 s.d ke-2 menunjukan cara penggunaan  asterisk (*) dan tanda tanya (?) sebagai karakter wildcard. Sedangkan contoh ke-3 dan ke-4 menggabarkan cara penggunaan kedua karakter tersebut sebagai karakter biasa.

Mari kita jelaskan contoh-contoh tersebut. Perhatikan range B2:B9, penggunaan angka 1 hanya contoh saja untuk menyederhanakan perhitungan penjumlahan sehingga dapat lebih mudah difahami.

Sebagai wildcard:

Contoh1 :   =SUMIF(B2:B9,"*",C2:C9)

Menghasilkan nilai 7,  karena kriteria  "*" menunjukan sel di range B2:B9 harus ada minimal satu karakter sehingga sel sejajar di rang C2:C9 dapat dijumlahkan. Dan sel di range B2:B9 yang memenuhi syarat tersebut ada 7 sel.

Contoh2 :   =SUMIF(B2:B9,"?",C2:C9)

Menghasilkan nilai 5,  karena kriteria  "?" menunjukan sel di range B2:B9 harus terdiri atas satu karakter saja sehingga sel sejajar di range C2:C9 dapat dijumlahkan. Dan sel di range B2:B9 yang memenuhi syarat tersebut ada 5 sel.

Sebagai karakter biasa

Contoh3 :   =SUMIF(B2:B9,"~*",C2:C9)

Menghasilkan nilai 1,  karena kriteria  "~*"  menunjukan sel di range B2:B9 harus hanya berisi text "*" saja sehingga sel sejajar di range C2:C9 dapat dijumlahkan. Dan sel di range B2:B9 yang memenuhi syarat tersebut hanya ada 1 sel.

Contoh4 :   =SUMIF(B2:B9,"~?",C2:C9)

Menghasilkan nilai 2,  karena kriteria  "~?"  menunjukan sel di range B2:B9 harus hanya berisi text "?" saja sehingga sel sejajar di range C2:C9 dapat dijumlahkan. Dan sel di range B2:B9 yang memenuhi syarat tersebut ada 2 sel.

Menjumlahkan Bilangan Terbesar Atau Terkecil Dalam Sebuah Range

Pada bagian ini sedikit melenceng dari pembahasan fungsi SUMIF, tapi masih berhubungan dengan penjumlahan bersyarat. Untuk menjumlahkan bilangan terbesar atau terkecil dari sebuah range excel maka kita dapat menggabungkan fungsi SUM dengan fungsi LARGE atau SMALL.

Contoh 1. Menambahkan beberapa bilangan terbesar/terkecil

Untuk menambahkan beberapa bilangan terbesar,  maka anda dapat mengetiknya langsung di formula, misalnya:

Menjumlahkan 5 bilangan terbesar pada range A1:A10

=SUM(LARGE(A1:A10,{1,2,3,4,5})) 

Demikian juga untuk menumlahkan beberapa bilangan terkecil

Menjumlahkan 5 bilangan terkecl pada range A1:A10

=SUM(SMALL(A1:A10,{1,2,3,4,5})) 

Contoh 2. Menambahkan Lebih Banyak Bilangan Teratas atau Terbawah

Pada contoh pertama, kita sudah mengetahui cara menjumlahkan 5 bilangan terbesar dan terkecil dengan cara mengetikan {1,2,3,4,5} dalam argumen fungsi LARGE. Cara tersebut cocok dilakukan untuk data yang sedikit dan batasan terbesar dan terkecil nya juga sedikit. 

Untuk data yang lebih besar dan batasan terbesar dan tekecilnya banyak, maka kita dapat menggunakan formula array menggunakan fungsi SUM, LARGE dan menambahkan fungsi ROW dan INDIRECT untuk mendefinisikan kriteria terbesar/terkecilnya.

Misalnya: Untuk menjumlahkan 10 bilangan terbesar pada range A1:A15, maka kita dapat menggunakan rumus sebagai berikut:

=SUM(LARGE(A1:A15,(ROW(INDIRECT("1:10")))))

Dan untuk menjumlahkan 10 bilangan terkecil, maka gunakan rumus berikut:

=SUM(SMALL(A1:A15,(ROW(INDIRECT("1:10")))))

Karena rumus tersebut merupakan rumus array, maka jangan lupa untuk menekan CTR + Shift + Enter setelah mengetikan formula.

Contoh 3. Menjumlahkan  bilangan terbesar atau terkecil dengan banyak bilangan yang bervariasi.

Jika anda termasuk orang yang tidak nyaman ketika harus merubah formula setiap kali ada perubahan berapa bilangan terbesar dan terkecil untuk dijumlahkan, maka sebaiknya batasan berapa bilangannya di simpan di dalam sel, kemudian rumus penjumlahan bilangan terbesar/terkecil dibuat menggunakan referensi sel tersbut.

Contohnya: untuk menjumlahkan 10 bilangan terbesar pada range A1:A15, jika angka 10 disimpan di sel C1 maka dapat dituliskan rumus sebagai berikut:

=SUM(LARGE(A1:A15,(ROW(INDIRECT("1:"&C1)))))

Untuk menjumlahkan 10 bilangan terkecil pada range A1:A15, jika angka 10 disimpan di sel C2 maka dapat dituliskan rumus sebagai berikut:

=SUM(SMALL(A1:A15,(ROW(INDIRECT("1:"&C2)))))

Karena merupakan rumus array, maka jangan lupa untuk menekan CTR+SHIFT+ENTER setelah mengetikan rumus tersebut untuk mendapatkan hasil yang benar.

Rumus SUMIF Untuk Penjumlahan Bersyarat Sel Sejajar Yang Kosong / Tidak Kosong

Ada 2 jenis sel kosong yaitu:

Sel kosong mutlak, artinya tidak ada apapun di dalam sel tersebut, termasuk formula yang menghasilkan string kosong. Gunakan kriteria "=" untuk sel kosong jenis ini.

Contoh: Rumus berikut untuk  menjumlahkan bilangan di range C2:C10 jika sel sejajar di range A2:A10 benar-benar kosong.

=SUMIF(A2:A10,"=",C2:C10)

Sel Bernilai Blank/kosong: mencakup kosong jenis pertama dan kosong yang disebabkan oleh formula, misalnya formula ="". Untuk mendefinisikan sel kosong jenis ini maka gunakan kriteria "" dalam rumus SUMIF.

=SUMIF(A2:A10,"",C2:C10)

Kedua contoh formula diatas mengevaluasi sel-sel yang berada pada kolom range A2:A10, kemudian jika merupakan sel kosong, maka sel lain dengan baris yang sama di range C2:C10 akan dijumlahkan.

Jika anda bermaksud untuk menjumlahkan nilai sel di kolom C yang sejajar dengan sel di kolom A yang tidak kosong maka gunakan “<>” sebagai kriteria di rumus SUMIF, contohnya:

=SUMIF(A2:A10,"<>",C2:C10)

Formula diatas menghitung nilai dari sel yang sejajar dengan sel tidak kosong yang dijadikan acuan termasuk sel kosong yang diperoleh dari formula (zero length string)

Rumus SUMIF untuk Penjumlahan Bersyarat Sel Sejajar Bersisi Data Tanggal 

Cara menggunakan rumus SUMIF untuk penjumlahan bersyarat tanggal, pada dasarnya sama saja dengan penggunaan fungsi SUMIF bersyarat type data lainnya.  Dalam hal ini, kita juga dapat menggunakan operator komparasi (perbandingan) sebagaimana sudah dijelaskan pada sebuah sub bab sebelumnya.

Seandainya kita ingin menjumlahkan bilangan bedasarkan tanggal sekarang, maka kita dapat menggunakan kombinasi fungsi SUMIF dengan TODAY sebagaimana contoh berikut:

Kriteria	Contoh Formula
Menjumlahkan nilai berdasarkan tanggal sekarang	=SUMIF(A2:A10, TODAY(), C2:C10)
Menjumlahkan nilai sebelum tanggal sekarang	=SUMIF(A2:A10, "<"&TODAY(), C2:C10)
Menjumlahkan nilai setelah tanggal sekarang	=SUMIF(A2:A10, ">"&TODAY(), C2:C10)
Menjumlahkan nilai di 7 hari kemudian.	=SUMIF(A2:A10, "="&TODAY()+7, C2:C9)

Bagaimana cara menjumlahkan nilai sesuai rentang tanggal tertentu.

Mungkin anda pernah bertanya. Bagaimana Cara Menjumlahkan data antara 2  tanggal?

Jawabannya adalah mengunakan kombinasi antara 2 fungsi SUMIF. 

Misalnya: untuk menjumlahkan nilai yang ada di sel C2:C100 jika kolom A berisi tanggal antara mulai 1 Desember 2016 s.d 31 Desember 2016, maka rumus dapat dituliskan sebagai berikut:

=SUMIF(C2:C100, ">=12/1/2016", C2:C100) - SUMIF(A2:A100, ">12/31/2016", C2:C100)

Formula tersebut mungkin kelihatan sedikit lebih ribet, Namun jika diperhatikan dengan lebih seksama, sebenarnya rumus tersebut sangat simple. 

Fungsi SUMIF  pertama digunakan untuk menjumlahkan nilai di sel C2:C100 dimana  sel berkesesuaian di kolom A lebih dari atau sama dengan tanggal pertama. Kemudian anda kurangkan dengan nilai yang lebih dari tanggal kedua yang diperoleh menggunakan fungsi SUMIF ke-2

Bagaimana Menjumlahkan Nilai Pada Beberapa Kolom.

Untuk memahami masalah dengan lebih baik, mari kita perhatikan dengan seksama contoh berikut: 

Anggaplah anda memiliki sebuah tabel berisi data penjualan bulanan beberapa produk dalam beberapa bulan.

Pertanyaannya: bagaimana anda bisa mendapatkan total produk buah tertentu yang terjual di semua wiayah dalam waktu 3 bulan terakhir?

Mengingatkan lagi bahasan sebelumnya, bahwa dimensi argumen sum_range ditentukan oleh dimensi argumen range. Itulah mengapa rumus berikut tidak akan bekerja sesuai harapan.

=SUMIF(A2:A10,"apel",C2:E10) 

Mengapa? Karena rumus tersebut hanya akan menjumlahkan data penjualan apel hanya di kolom C.

Solusi paling sederhana dan logis adalah dengan menggunakan kolom bantu berisi total data beberapa bulan di masing-masing baris.

Cukup masukan rumus SUM pada sel F2, kemudian copy ke baris berikutnya di kolom F

Setelah itu anda dapat menggunakan rumus SUMIF seperti biasa

=SUMIF(A2:A10,"apel", F2:F10)

Dan jika  kriteria apel disimpan di sel A14, maka rumus tersebut dapat dituliskan:

=SUMIF(A2:A10,A14,F2:F10)

Jika anda lebih suka melakukan penjumlahan bersyarat tanpa menggunakan kolom bantu, maka anda dapat menggunakan beberapa rumus SUMIF terpisah, kemudian menjumlahkan nya menggunaan operator tambah (+) atau fungsi SUM, sesuai contoh berikut:

=SUMIF(A2:A10,A14,C2:C10)+SUMIF(A2:A10,A14,D2:D10)+SUMIF(A2:A10,A14,E2:E10)

Atau

=SUM(SUMIF(A2:A10,A14,C2:C10),SUMIF(A2:A10,A14,D2:D10),SUMIF(A2:A10,A14,E2:E10))

Alternative cara lainnya adalah menggunakan fungsi SUMPRODUCT, seperti contoh berikut:

=SUMPRODUCT((A2:A10=A14)*C2:E10)

Atau menggunakan rumus array

{=SUM((A2:A10=A14)*C2:E10)}

Keempat rumus diatas, sama-sama akan menghasilkan 2.280.000

Sering Ditanyakan : Mengapa Rumus SUMIF Tidak Bekerja/Berfungsi

Ada beberapa alasan kenapa rumus SUMIF tidak bekerja atau tidak menghasilkan perhitungan yang benar. Kadang-kadang nilai yang dihasilkan rumus SUMIF tidak sesuai dengan yang diharapkan karena type data di sel atau beberapa argumen tidak cocok. Berikut adalah beberapa hal yang harus dicek untuk memastikan fungsi SUMIF dapat bekerja dengan benar.

1. Parameter range dan sum_range harus berupa range, bukan array.

Parameter pertama (range) dan parameter kedua (sum_range) dalam rumus SUMIF harus berupa referensi rang seperti A1:A10. Jika anda mencoba dengan lainnya misalnya menggunakan array seperti {1,2,3} maka akan terjadi error.

Rumus yang benar: =SUMIF(A1:A3, "apel", C1:C3)

Rumus yang salah: =SUMIF({1,2,3}, "apel", C1:C3)

2. Cara untuk menjumlahkan nilai dari sheet lain atau workbook lain

Sebagaimana hampir semua function excel lainnya, fungsi SUMIF juga bisa menggunakan referensi sheet lain atau bahkan file exel lainnya yang berbeda.

Sebagai contoh, rumus berikut dimaksudkan untuk menjumlahkan nilai di sel F2:F9 di sheet 1, dari file excel bernama Book1 jika sel berkesesuian di kolom A, dengan nama  sheet “apel”.

=SUMIF([Book1.xlsx]Sheet1!$A$2:$A$9,"apel",[Book1.xlsx]Sheet1!$F$2:$F$9)

Akan tapi rumus tersebut tidak akan bekerja ketika book1 ditutup atau close. Ini terjadi karena range yang dijadikan referensi oleh rumus SUMIF, di referensi balik menjadi array. Dan dikarenakan array tidak diizinkan pada rumus sumif, maka rumus tersebut akan menghasilkan error #VALUE!

3. Untuk menghindari problem, pastikan parameter sum dan sum_range memiliki ukuran jumlah baris dan kolom yang sama.

Sebagaimana disampaikan pada permulaan bahasan ini. Pada versi excel yang  lebih moderen, parameer sum dan sum_range tidak harus memiliki ukuran yang sama. Di exel 2000 atau versi yang lebih awal, ukuran yang berbeda akan menyebabkan problem. Ini juga kata orang ya, karena saya belum pernah mencobanya di excel 2000.

 Akan tetapi, meskipun excel versi terbaru seperti excel 2010, 2013 dan 2016, rumus SUMIF yang lebih komplek jika sum_range berbeda jumlah barisnya dengan argumen range, maka kebanyakan user perlu extra lebih untuk memahaminya.

Oleh karena itu secara praktisnya adalah selalu menggunakan ukuran yang sama pada parameter range dan sum_range

Demikian pembahasan dasar-dasar penggunaan fungsi SUMIF untuk penjumlahan bersyarat dengan kriteria bilangan, kriteria text dan karakter wildcard, serta kriteria tanggal. 

Semoga bermanfaat.

Artikel Terkait:

• Fungsi SUM dan Rumus Penjumlahan Pada Excel
• Rahasia Cara Cepat AutoSUM
• Rumus Excel Tidak Berfungsi? Inilah Penyebabnya!
• Memahami Rumus VLOOKUP Dengan Studi Kasus

Referensi:

https://www.ablebits.com/office-addins-blog/2014/11/04/excel-sumif-function-formula-examples/