Cara Penggunaan SUMIF dan COUNTIF di Excel

Cara Penggunaan SUMIF dan COUNTIF di Excel

Cara Penggunaan SUMIF dan COUNTIF di Excel

Daftar Isi Artikel

Cara Penggunaan SUMIF dan COUNTIFS serta SUMIFS, COUNTIF di excel sangat berguna dan kuat untuk analisis data. Jika ada Function Hall of Fame Excel, fungsi keluarga ini harus disertakan. Dalam artikel ini, saya akan mendemonstrasikan banyak cara berbeda untuk menggunakan fungsi-fungsi ini, dengan fokus utama pada semua jenis kriteria yang berbeda.

Fungsi SUMIF dan COUNTIF memungkinkan Anda untuk menjumlahkan atau menghitung sel secara bersyarat berdasarkan satu kondisi , dan kompatibel dengan hampir semua versi Excel:

= SUMIF ( criteria_range , kriteria , jumlah_rentang )

= COUNTIF ( kriteria_rentang , kriteria )

Fungsi SUMIFS dan COUNTIFS memungkinkan Anda menggunakan beberapa kriteria , tetapi hanya tersedia dimulai dengan Excel 2007:

= SUMIFS ( jumlah_rentang , kriteria_rentang1 , criteria1 , kriteria_rentang2 , criteria2 , …)

= COUNTIFS ( kriteria_rentang1 , criteria1 , kriteria_rentang2 , criteria2 , …)

AVERAGEIF dan AVERAGEIFS juga merupakan bagian dari fungsi keluarga ini dan memiliki sintaks yang sama dengan SUMIF dan SUMIFS.

Artikel ini (bookmark):

  • Contoh SUMIF dan COUNTIF
  • Menggunakan Tanggal sebagai Kriteria
  • SUMIFS Contoh: Laporan Pendapatan dan Beban
  • SUMIF dan COUNTIF Antara Dua Angka (1 <x <10)
  • SUMIF dan COUNTIF dengan ketentuan ATAU
  • Tabel Ringkasan Jenis Kriteria
  • Pencocokan Peka Huruf Besar-Kecil Menggunakan SUMPRODUCT
  • MAXIFS dan MINIFS
  • Catatan Lainnya

1) Contoh SUMIF dan COUNTIF

Kami akan memulai dengan menggunakan tabel penjualan produk untuk mendemonstrasikan beberapa rumus SUMIF dan COUNTIF yang berbeda. Saya telah membuat daftar beberapa contoh di bawah ini. Untuk melihat rumus ini beraksi dan mencobanya sendiri, Anda dapat mengunduh file contoh di bawah ini:

Baca Juga  Cara melihat beberapa lembar kerja Excel secara bersamaan

Unduh File Contoh (SumIf-CountIf.xlsx)

Tabel Penjualan untuk Mendemonstrasikan SUMIF dan COUNTIF

Kriteria adalah Nilai Teks

Contoh: Jumlah Penjualan di mana Kategori sama dengan “siswa”

= SUMIF ( category_range , “SISWA” , sales_range )

CATATANRumus ini juga akan cocok dengan “SISWA” karena kelompok fungsi SUMIF¬†tidak peka huruf besar / kecil¬†.¬†Anda dapat menggunakan¬†karakter wildcard¬†dalam string teks, seperti¬†“? S *”¬†untuk mencocokkan nilai dengan huruf kedua s.

Tidak Sama Dengan (<>)

Contoh: Jumlah Penjualan di mana Model TIDAK sama dengan “B”

= SUMIF ( rentang_model , “<> B” , rentang_penjualan )

Contoh: Jumlah Penjualan yang Kategori TIDAK mengandung huruf “u”

= SUMIF ( category_range , “<> * u *” , sales_range )

Kriteria adalah Perbandingan Teks Alfabetis

Contoh: Jumlah Penjualan dengan Model kurang dari “C”

= SUMIF ( rentang_model , “<C” , rentang_penjualan )

Kriteria adalah Perbandingan Numerik

Contoh: Jumlah produk dengan harga lebih dari $ 40

= COUNTIF ( kisaran_harga , “> 40” )

CATATANSaat menggunakan kriteria numerik, COUNTIF dan SUMIF mengabaikan nilai teks. Nilai tanggal numerik bisa menjadi pengecualian untuk itu (lihat di bawah untuk perbandingan tanggal).

Kriteria cocok dengan Sel Kosong atau Sel Kosong

Contoh: Hitungan produk dengan On Sale kosong

= COUNTIF ( rentang_penjualan , “” )

Kriteria cocok dengan Sel Tidak Kosong

Contoh: Jumlah produk yang diobral (di mana On Sale tidak kosong)

= COUNTIF ( rentang_sale_singkat , “<>” )

Kriteria mencakup Referensi Sel

Contoh: Jumlah Penjualan di mana Harga lebih besar dari nilai di sel H3

= SUMIF ( rentang_harga , “>” & H3 , rentang_penjualan )

Kriteria ada di sel lain

Contoh: Jumlah Penjualan menggunakan kriteria yang ditentukan di sel K9

= SUMIF ( criteria_range , K9 , sales_range )

CATATANTeknik ini berguna saat Anda ingin mengizinkan pengguna untuk memilih atau memasukkan kriteria atau string pencarian yang berbeda.

Sel K9 dapat berisi nilai seperti “pelajar” atau “40” atau string kriteria seperti “> 40” atau “<> pelajar”

Beberapa Kriteria

Contoh: Jumlah Penjualan dengan Kategori = “pelajar”¬†DAN¬†Harga> 30

= SUMIFS ( sales_range , category_range , “SISWA” , PRICE_RANGE , “> 30” )

Contoh: SUMIFS antara dua tanggal

= SUMIFS ( jumlah_rentang , DATE_RANGE , “> = 1/1/2017” , DATE_RANGE , “<2017/01/31” )

CATATANFungsi SUMIFS, COUNTIFS, dan AVERAGEIFS digunakan untuk beberapa kondisi DAN. 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 angka berurutan yang dimulai dengan 1 untuk 1/1/1900. Pemformatan sel mungkin menampilkan tanggal dengan cara berbeda, tetapi perbandingan COUNTIF dan SUMIF didasarkan pada nilai yang disimpan dalam sel, bukan cara sel diformat. Itu bagus, karena kami biasanya ingin membandingkan tanggal dan angka tanpa harus khawatir tentang bagaimana formatnya.

Kriteria adalah Tanggal

= COUNTIF ( kriteria_rentang , = 3/1/17 “ )

Saat menggunakan kriteria seperti “= 3/1/17” atau “Mar 1, 2017”, Excel akan mengenali kriteria sebagai tanggal dan akan menghitung semua¬†nilai tanggal¬†dalam¬†criteria_range 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 ( kriteria_rentang , “> 3/1/17” )

Saat menggunakan <,>, <=, atau> =, Excel masih mengenali kriteria sebagai tanggal, tetapi tidak mengonversi nilai teks dalam criteria_range ke nilai tanggal.

Baca Juga  Tutorial cara membuat aplikasi converter dari cm ke Inci dengan Excel

Perbandingan dengan HARI INI

Anda dapat menggunakan fungsi TODAY untuk membuat perbandingan berdasarkan tanggal sekarang, seperti ini:

= SUMIF ( rentang_tanggal , “<” & HARI INI () , rentang_jumlah )

3) SUMIFS Contoh: Laporan Pendapatan dan Beban

SUMIFS sangat berguna dalam register akun , penganggaran, dan spreadsheet pengelolaan uang untuk meringkas pengeluaran berdasarkan kategori dan antara dua tanggal.

Contoh SUMIFS di bawah ini menjumlahkan kolom Jumlah dengan 3 kriteria: (1) Kategori cocok dengan “Bahan Bakar”, (2) Tanggal lebih besar dari 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:

Contoh SUMIFS - Laporan Pendapatan dan Beban

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 AND x <4). Namun, jika Anda mencoba membuat lembar bentang 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 Formula menggunakan COUNTIFS
1 <x <4 = COUNTIFS (¬†rentang¬†, “> 1”,¬†rentang¬†, “<4”)
Kondisi Formula 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. Ingatlah bahwa nilai tanggal disimpan sebagai angka.

Contoh: SUMIF antara dua tanggal (1/1/2017 <= tanggal <= 1/31/2017)

= SUMIF ( jumlah_rentang , DATE_RANGE , “<= 1/31/2017” ) – SUMIF ( jumlah_rentang , DATE_RANGE , “<2017/01/01” )

5) SUMIF dan COUNTIF dengan Kondisi ATAU

COUNTIFS dan SUMIFS menangani beberapa ketentuan DAN, tetapi ketentuan ATAU seperti X <2 ATAU X> 3 biasanya lebih mudah ditangani dengan mengevaluasi setiap ketentuan secara terpisah, lalu 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 ketentuan “= yes”.¬†Ketentuan “<40” akan tumpang tindih dengan ketentuan “> 20”.¬†Jika ketentuannya tumpang tindih, Anda mungkin akan menghitung atau menambahkan nilai dua kali.¬†Jika ada kemungkinan kondisi tumpang tindih, 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 mengenali bahwa FALSE + FALSE = 0 dan TRUE + FALSE = 1 dan TRUE + TRUE = 2. Ini berarti bahwa agar kondisi OR logis menjadi benar, kita dapat memeriksa apakah jumlah dari dua atau lebih kondisi adalah> 0.

Jumlah Penjualan di mana Model sama dengan A atau B.

= SUMPRODUCT ( penjualan , 1 * ((( model = “A”) + ( model = “B”))> 0))

Jumlah Penjualan dengan Model = “A” atau Harga> 45

= SUMPRODUCT ( penjualan , 1 * ((( model = “A”) + ( harga > 45))> 0))

6) SUMIF dan COUNTIF 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 menyediakan cara untuk melakukan pencocokan peka huruf besar / kecil.

Baca Juga  Cara Penggunaan Linier Programming dengan Excel

Jumlah Penjualan di mana Kategori sama persis dengan “pelajar” (peka huruf besar-kecil)

= SUMPRODUCT ( penjualan , 1 * PERSIS ( kategori , “siswa” ))

Jumlah Penjualan dengan Kategori berisi “Stu” (peka huruf besar-kecil)

= SUMPRODUCT ( penjualan , 1 * ISNUMBER ( TEMUKAN ( “Stu” , kategori )))

7) Formula 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 beberapa 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 dengan Model = “A”

(rumus array) = MAX ( IF ( model_range = “A”, price_range , “”))

8) Ringkasan Berbagai Jenis Kriteria

JENIS KRITERIA CONTOH PERTANDINGAN …
Nilai Teks “ya” atau “= ya” “ya” atau “Ya” (tidak peka huruf besar / kecil), tanda sama dengan adalah opsional
Nilai Teks dengan Wildcard “=? s *” nilai teks dengan huruf kedua adalah “s” atau “S”
Perbandingan Teks Alfabetis “<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 sama dengan “<> 0” nilai tidak sama dengan nilai 0
Tidak Kosong “<>” nilai yang tidak kosong (rumus yang mengembalikan “” tidak 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 3/1/17 serta nilai teks seperti “3/1/17” atau “1 Mar 2017”
<atau> Tanggal “> 1/1/2017” nilai tanggal lebih besar dari 1/1/2017 (nilai teks diabaikan)

Beberapa kriteria, seperti kecocokan peka huruf besar kecil, mungkin hanya dapat dilakukan dengan rumus array atau SUMPRODUCT.

Anda tidak menggunakan fungsi AND, NOT, OR, ISBLANK, ISNUMBER, ISERROR, atau fungsi serupa lainnya sebagai kriteria untuk SUMIF dan COUNTIF. Namun, Anda bisa menggunakan fungsi ini dalam rumus SUMPRODUCT (tapi itu tidak dalam cakupan artikel ini).

9) Catatan Lainnya

  • Perbandingan didasarkan pada nilai yang disimpan di dalam sel, bukan pada bagaimana sel diformat.
  • Nilai kesalahan baik dalam¬†sum_range¬†dan¬†criteria_range¬†diabaikan.
  • Rumus SUMIFS dan COUNTIFS umumnya lebih cepat daripada rumus SUMPRODUCT atau rumus larik.
  • 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 larik menggunakan Ctrl + Shift + Enter.
  • Kriteria COUNTIF dan SUMIF bisa berupa daftar seperti {“> 1”, “<4”}, tetapi fungsi mengembalikan larik yang berisi hasil untuk kondisi terpisah, bukan jumlah kedua kondisi (tidak sama dengan COUNTIFS atau SUMIFS).

 

Tinggalkan Komentar

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *

Need Help? Chat with us