SUMIF, SUMIFS, COUNTIF, dan COUNTIFS sangat berguna untuk analisis data. Dalam tutorial excel SUMIF, SUMIFS, COUNTIF, dan COUNTIFS, saya akan mendemonstrasikan banyak cara berbeda untuk menggunakan fungsi-fungsi ini. Dengan fokus utama pada semua tipe kriteria yang berbeda.
Fungsi SUMIF dan COUNTIF memungkinkan Anda menjumlahkan atau menghitung sel secara kondisional berdasarkan satu kondisi, dan kompatibel dengan hampir semua versi Excel:
= SUMIF ( criteria_range , kriteria , jumlah_rentang )
= COUNTIF ( criteria_range , kriteria )
Fungsi SUMIFS dan COUNTIFS memungkinkan Anda menggunakan beberapa kriteria , tetapi hanya tersedia mulai dari Excel 2007:
= SUMIFS ( sum_range , criteria_range1 , criteria1 , criteria_range2 , criteria2 ,...)
= COUNTIFS ( criteria_range1 , criteria1 , criteria_range2 , criteria2 ,...)
AVERAGEIF dan AVERAGEIFS juga merupakan bagian dari keluarga fungsi ini dan memiliki sintaks yang sama dengan SUMIF dan SUMIFS.
1) Contoh SUMIF dan COUNTIF
Kami akan memulai dengan menggunakan tabel penjualan produk untuk mendemonstrasikan beberapa rumus SUMIF dan COUNTIF yang berbeda. Saya telah mencantumkan beberapa contoh di bawah ini. Untuk melihat formula ini beraksi dan mencobanya sendiri, Anda dapat mengunduh file contoh di bawah ini:
Download file contoh disini.
Kriteria adalah Nilai Teks
Contoh: Jumlah Penjualan di mana Kategori sama dengan “siswa”
= SUMIF ( kategori=SUMIF(category_range,"student",sales_range)
CATATAN : Rumus ini juga akan cocok dengan “Student” karena keluarga fungsi SUMIF tidak peka huruf besar-kecil . Anda dapat menggunakan karakter wildcard dalam string teks, seperti “?s*” untuk mencocokkan nilai di mana huruf kedua adalah s.
Tidak Sama Dengan (<>)
Contoh: Jumlah Penjualan dimana Model TIDAK sama dengan “B”
=SUMIF(model_range,”<>B”,sales_range)
Contoh: Jumlah Penjualan dimana Kategori TIDAK mengandung huruf “u”
=SUMIF(category_range,”<>u“,sales_range)
Kriteria adalah Perbandingan Teks Abjad
Contoh: Jumlah Penjualan di mana Model kurang dari “C”
=SUMIF(model_range,”<C”,sales_range)
Kriteria adalah Perbandingan Numerik
Contoh: Jumlah produk dengan harga lebih dari $40
=COUNTIF(price_range,”>40″)
CATATANSaat menggunakan kriteria numerik, COUNTIF dan SUMIF mengabaikan nilai teks. Nilai tanggal numerik dapat menjadi pengecualian untuk itu (lihat di bawah untuk perbandingan tanggal).
Kriteria cocok dengan Sel Kosong atau Sel Blank
Contoh: Hitungan produk di mana On Sale kosong
=COUNTIF(on_sale_range,””)
Kriteria cocok dengan Sel Non-Kosong
Contoh: Jumlah produk yang dijual (di mana On Sale tidak boleh kosong)
=COUNTIF(on_sale_range,”<>”)
Kriteria termasuk Referensi Sel
Contoh: Jumlah Penjualan di mana Harga lebih besar dari nilai di sel H3
=SUMIF(price_range,”>”&H3,sales_range)
Kriteria ada di Sel Lain
Contoh: Jumlah Penjualan menggunakan kriteria yang ditentukan dalam sel K9
=SUMIF(criteria_range,K9,sales_range)
Teknik ini berguna ketika Anda ingin mengizinkan pengguna untuk memilih atau memasukkan kriteria atau string pencarian yang berbeda.
Sel K9 dapat berisi nilai seperti “student” atau “40” atau string kriteria seperti “>40” atau “<> student “.
Beberapa Kriteria
Contoh: Jumlah Penjualan dimana Category = “student” AND Price > 30
=SUMIFS(sales_range,category_range,”student”,price_range,”>30″)
Contoh: SUMIFS antara dua tanggal
=SUMIFS(sum_range,date_range,”>=1/1/2017″,date_range,”<1/31/2017″)
Fungsi SUMIFS, COUNTIFS, dan AVERAGEIFS digunakan untuk beberapa kondisi AND. Rumus untuk kondisi OR sedikit lebih rumit (lihat di bawah).
2) Menggunakan Tanggal sebagai Kriteria
Saat menggunakan tanggal sebagai kriteria untuk fungsi COUNTIF dan SUMIF, Excel melakukan beberapa hal menarik, bergantung pada apakah Anda menggunakan “=” atau “<” sebagai kriteria dan apakah tanggal dalam rentang kriteria disimpan sebagai nilai tanggal numerik atau teks nilai-nilai.
Ingat : Nilai tanggal disimpan di Excel sebagai nomor urut yang dimulai dengan 1 untuk 1/1/1900. Pemformatan sel dapat menampilkan tanggal dengan cara yang berbeda, tetapi perbandingan COUNTIF dan SUMIF didasarkan pada nilai yang disimpan dalam sel, bukan cara sel diformat. Itu bagus, karena dalam SUMIF, SUMIFS, COUNTIF, dan COUNTIFS sangat berguna untuk analisis data. Dalam tutorial excel SUMIF, SUMIFS, COUNTIF, dan COUNTIFS kami biasanya ingin membandingkan tanggal dan angka tanpa harus khawatir tentang bagaimana mereka diformat.
Kriteria adalah Tanggal
=COUNTIF(criteria_range,”=3/1/17″)
Saat menggunakan kriteria seperti “=3/1/17” atau “1 Mar 2017”, Excel akan mengenali kriteria sebagai tanggal dan akan menghitung semua nilai tanggal dalam kriteria_rentang yang cocok dengan tanggal tersebut. Excel JUGA akan menghitung tanggal yang dikenali yang disimpan sebagai nilai teks dalam criteria_range , seperti “1 Maret 2017” dan “3/1/2017” (tetapi bukan “1 Maret 2017” karena Excel tidak mengenalinya sebagai tanggal) .
Kriteria Lebih Besar atau Kurang dari Tanggal
=COUNTIF(criteria_range,”>3/1/17″)
Saat menggunakan <, >, <=, atau >=, Excel masih mengenali kriteria sebagai tanggal, tetapi tidak mengonversi nilai teks dalam criteria_range ke nilai tanggal.
Perbandingan dengan HARI INI
Anda dapat menggunakan fungsi TODAY untuk membuat perbandingan berdasarkan tanggal saat ini, seperti ini:
=SUMIF(date_range,”<“&TODAY(),sum_range)
3) Contoh SUMIFS: Laporan Pendapatan dan Beban
SUMIFS sangat berguna dalam register akun , penganggaran, dan spreadsheet pengelolaan uang untuk meringkas pengeluaran menurut kategori dan di antara dua tanggal.
Contoh SUMIFS di bawah ini menjumlahkan kolom Jumlah dengan 3 kriteria:
- (1) Kategori cocok dengan “Fuel”
- (2) Tanggal lebih besar atau sama dengan tanggal mulai, dan
- (3) Tanggal kurang dari atau sama dengan tanggal akhir.
=SUMIFS(amount_range, category_range, “Fuel”, date_range, “>=1/1/2018”, date_range, “<=1/31/2018”)
Tangkapan layar berikut menunjukkan contoh dari file unduhan:
4) SUMIF dan COUNTIF Antara Dua Angka (1 < x < 4)
COUNTIFS dan SUMIFS dapat dengan mudah menangani kondisi seperti 1 < x < 4 (yang berarti x > 1 DAN x < 4). Namun, jika Anda mencoba membuat spreadsheet yang kompatibel dengan versi Excel yang lebih lama, Anda dapat menggunakan COUNTIF atau SUMIF dengan mengurangi hasil kondisi x <= 1 dari hasil kondisi x < 4.
Kondisi Rumus menggunakan COUNTIFS 1 < x < 4 =COUNTIFS( rentang ,”>1″, rentang ,”<4″) Kondisi Rumus hanya menggunakan COUNTIF 1 < x < 4 =COUNTIF( rentang ,”<4″) – COUNTIF( rentang ,”<=1″) 1 <= x < 4 =COUNTIF( rentang ,”<4″) – COUNTIF( rentang ,”<1″) 1 < x <= 4 =COUNTIF( rentang ,”<=4″) – COUNTIF( rentang ,”<=1″) 1 <= x <= 4 =COUNTIF( rentang ,”<=4″) – COUNTIF( rentang ,”<1″)
Kebutuhan umum untuk rumus ini adalah menjumlahkan nilai di antara dua tanggal. Ingat bahwa nilai tanggal disimpan sebagai angka.
Contoh: SUMIF antara dua tanggal ( 1/1/2017 <= tanggal <= 1/31/2017 )
=SUMIF(sum_range,date_range,”<=1/31/2017″)-SUMIF(sum_range,date_range,”<1/1/2017″)
5) SUMIF dan COUNTIF dengan OR Kondisi
COUNTIFS dan SUMIFS menangani beberapa kondisi AND, tetapi kondisi OR seperti X<2 OR X>3 biasanya lebih mudah ditangani dengan mengevaluasi setiap kondisi secara terpisah dan kemudian menambahkan hasilnya. Kedua rumus di bawah ini pada dasarnya melakukan hal yang sama.
Kondisi Rumus x < 2 atau 3 < x =COUNTIF( rentang ,”<2″) + COUNTIF( rentang ,”>3″) x < 2 atau 3 < x =SUM(COUNTIF( rentang ,{“<2″,”>3″}))
Untuk menghindari sel penghitungan ganda, kondisi tidak boleh tumpang tindih. Misalnya, kondisi “=*e*” akan tumpang tindih dengan kondisi “=yes”. Kondisi “<40” akan tumpang tindih dengan kondisi “>20”. Jika kondisinya tumpang tindih, Anda mungkin akan menghitung atau menambahkan nilai dua kali. Jika ada kemungkinan kondisi tumpang tindih, maka Anda mungkin perlu menggunakan rumus SUMPRODUCT seperti yang dijelaskan di bawah ini.
Gunakan SUMPRODUCT untuk kondisi ATAU yang tumpang tindih
Kunci untuk menghindari penghitungan ganda adalah dengan mengenali bahwa FALSE+FALSE=0 dan TRUE+FALSE=1 dan TRUE+TRUE=2. Ini berarti bahwa untuk kondisi logika OR benar, kita dapat memeriksa apakah jumlah dari dua atau lebih kondisi > 0.
Jumlah Penjualan di mana Model sama dengan A atau B.
=SUMPRODUCT(sales,1*( ((model=”A”)+(model=”B”))>0 ))
Jumlah Penjualan di mana Model = “A” atau Harga > 45
=SUMPRODUCT(sales,1*( ((model=”A”)+(price>45))>0 ))
6) SUMIF dan COUNTIF yang peka huruf besar/kecil
Keluarga SUMIF tidak memiliki opsi peka huruf besar/kecil, jadi kita perlu kembali menggunakan rumus array atau SUMPRODUCT. Fungsi FIND dan EXACT keduanya menyediakan cara untuk melakukan pencocokan peka huruf besar/kecil.
Jumlah Penjualan di mana Kategori sama persis dengan “siswa” (peka huruf besar-kecil)
=SUMPRODUCT(sales,1*EXACT(category,”student”))
Jumlah Penjualan di mana Kategori berisi “Stu” (peka huruf besar-kecil)
=SUMPRODUCT(sales,1*ISNUMBER(FIND(“Stu”,category)))
7) Rumus MAXIF atau MINIF
MAXIFS dan MINIFS adalah fungsi Excel baru yang tersedia dalam rilis terbaru (Excel untuk Microsoft 365, Excel 2019). Sintaksnya mirip dengan SUMIFS, memungkinkan Anda menggunakan banyak kriteria.
Versi Excel yang lebih lama tidak memiliki fungsi MAXIFS atau MINIFS, tetapi Anda dapat menggunakan rumus array seperti ini (ingat untuk menekan Ctrl+Shift+Enter):
Contoh: Temukan Harga maksimum di mana Model = “A”
(array formula) =MAX(IF(model_range=”A”,price_range,””))
8) Ringkasan Berbagai Jenis Kriteria
| JENIS KRITERIA | CONTOH | PERSAMAAN |
|---|---|---|
| Nilai Teks | “ya” atau “= ya” | “ya” atau “Ya” (tidak peka huruf besar/kecil), tanda sama dengan opsional |
| Nilai Teks dengan Wildcard | “=?s*” | nilai teks di mana huruf kedua adalah “s” atau “S” |
| Perbandingan Teks Abjad | “<C” | nilai teks menurut abjad kurang dari “C” |
| Sama dengan Nilai Numerik | 20 atau “= 20” | nilai numerik sama dengan 20 |
| Kurang dari atau Sama dengan | “<=20” | nilai numerik kurang dari atau sama dengan 20 |
| Lebih dari atau sama dengan | “>=20” | nilai numerik lebih besar dari atau sama dengan 20 |
| Tidak sebanding dengan | “<>0” | nilai tidak sama dengan nilai 0 |
| Tidak Kosong | “<>” | nilai yang tidak kosong (rumus yang mengembalikan “” tidak boleh kosong) |
| Kosong atau Kosong | “” | nilai yang kosong dan rumus yang mengembalikan “” |
| Sama dengan Nilai Sel | A42 atau “=”&A42 | nilai sama dengan nilai di sel A42 |
| Perbandingan dengan Nilai Sel | “>”&A42 | nilai lebih besar dari nilai di sel A42 |
| Sama dengan Tanggal | “=3/1/17” | nilai tanggal sama dengan 1/3/17 serta nilai teks seperti “3/1/17” atau “1 Maret 2017” |
| < atau > sebuah Tanggal | “>1/1/2017” | nilai tanggal lebih besar dari 1/1/2017 (nilai teks diabaikan) |
Beberapa kriteria, seperti kecocokan peka huruf besar/kecil, hanya dimungkinkan dengan rumus larik atau SUMPRODUCT.
Anda tidak boleh menggunakan fungsi AND, NOT, OR, ISBLANK, ISNUMBER, ISERROR, atau fungsi serupa lainnya sebagai kriteria untuk SUMIF dan COUNTIF. Namun, Anda dapat menggunakan fungsi ini dalam rumus SUMPRODUCT (tetapi itu tidak termasuk dalam cakupan artikel ini).
Artikel lain yang mungkin anda suka : Menambah password di file excel.
9) Catatan Lainnya
- Perbandingan didasarkan pada nilai yang disimpan dalam sel, bukan pada cara sel diformat.
- Nilai kesalahan di sum_range dan criteria_range diabaikan.
- Rumus SUMIFS dan COUNTIFS umumnya lebih cepat daripada rumus SUMPRODUCT atau rumus array.
- The jumlah_rentang dan criteria_range argumen dapat referensi (misalnya A2: A42), bernama rentang atau formula yang mengembalikan berbagai (seperti INDEX, OFFSET, atau TIDAK LANGSUNG).
- Biasanya, Anda ingin sum_range dan criteria_range memiliki panjang yang sama. Lihat dokumentasi di situs Microsoft (direferensikan di bawah) untuk informasi tentang apa yang terjadi ketika sum_range dan criteria_range tidak sama panjangnya.
- Kriteria COUNTIF dan SUMIF dapat berupa rentang (misalnya A2:A3) jika Anda memasukkan rumus sebagai rumus array menggunakan Ctrl+Shift+Enter.
- Kriteria COUNTIF dan SUMIF dapat berupa daftar seperti {“>1″,”<4”}, tetapi fungsi mengembalikan array yang berisi hasil untuk kondisi terpisah, bukan jumlah dari kedua kondisi (tidak sama dengan COUNTIFS atau SUMIFS).
Semoga tutorial excel SUMIF, SUMIFS, COUNTIF, dan COUNTIFS dapat bermanfaat untuk anda. Terimakasih sudah berkunjung, jika ada pertanyaan silahkan tinggalkan dikolom komentar ya.
1 comment