Excel Mastery Indonesia

Excel Mastery
Indonesia

Tutorial Excel • satu syarat tidak cukup, dua atau tiga syarat baru menjawab

Cara Menghitung dan Menjumlahkan Data dengan Banyak Kriteria di Excel Pakai COUNTIFS dan SUMIFS

Atasanmu bertanya: berapa pesanan Kopi Susu yang sudah Terkirim? Kamu sudah bisa menjawabnya dengan COUNTIF — jawabannya 3. Lalu pertanyaan lanjutannya datang: berapa total nominalnya? Dan berapa Kopi Susu yang Terkirim di Jakarta saja? Di titik ini satu syarat tidak lagi cukup. Kalau kamu menjawab dengan filter lalu menghitung manual, hasilnya bergantung pada matamu — dan pada 200 baris, satu baris yang terlewat mengubah totalnya diam-diam.

Jawaban singkat: pakai COUNTIFS untuk menghitung baris yang memenuhi semua syarat, dan SUMIFS untuk menjumlahkan nominalnya. Keduanya memakai logika DAN: sebuah baris hanya dihitung kalau semua syaratnya terpenuhi. Rumusnya =COUNTIFS(B2:B13;"Kopi Susu";C2:C13;"Terkirim") dan hasilnya 3; pasangannya =SUMIFS(E2:E13;B2:B13;"Kopi Susu";C2:C13;"Terkirim") hasilnya Rp135.000 — bukan perkiraan, dan bisa dicek ulang kapan pun karena datanya tetap di artikel ini.

Ke praktik langkah-demi-langkah ↓

Konsep: tiap syarat datang berpasangan, dan SUMIFS menaruh kolom yang dijumlah di depan

COUNTIFS dan SUMIFS adalah versi jamak dari COUNTIF dan SUMIF: sama-sama bekerja dengan syarat, tapi menerima lebih dari satu. Setiap syarat tambahan selalu ditulis sebagai satu pasangan — range kriterianya, lalu kriterianya:

=COUNTIFS(range1; kriteria1; [range2; kriteria2]; ...)

=SUMIFS(sum_range; range1; kriteria1; [range2; kriteria2]; ...)

Perhatikan satu perbedaan yang paling sering membuat orang salah: pada SUMIF, kolom yang dijumlah ditulis paling belakang; pada SUMIFS, kolom yang dijumlah ditulis paling depan, sebelum semua pasangan kriteria. Kalau kamu menyalin urutan SUMIF ke dalam SUMIFS, hasilnya 0 — jebakan ini dibahas tuntas di bawah.

Perbandingan fungsi satu kriteria dan banyak kriteria
FungsiYang dikerjakanJumlah syaratUrutan kolom yang dijumlah
COUNTIFMenghitung banyaknya barisSatu—
COUNTIFSMenghitung banyaknya barisBanyak (logika DAN)—
SUMIFMenjumlahkan nominalSatuPaling belakang
SUMIFSMenjumlahkan nominalBanyak (logika DAN)Paling depan
AVERAGEIFSMenghitung rata-rata nominalBanyak (logika DAN)Paling depan

Aturan praktisnya: kalau kamu sudah memakai SUMIF, biasakan langsung memakai SUMIFS — ia bekerja juga dengan satu syarat, dan urutan argumennya konsisten begitu syarat kedua ditambahkan. Dan ingat, semua pasangan kriteria di COUNTIFS dan SUMIFS dihubungkan dengan DAN: baris yang dihitung harus lulus semua syarat. Untuk logika ATAU, caranya berbeda — lihat jebakan ketiga.

Praktik: 12 baris pesanan, 6 hitungan dalam 6 langkah

Buat workbook baru dan ketik tabel ini persis seperti di bawah: kolom A nomor, kolom B produk, kolom C wilayah, kolom D status, kolom E nominal. Data ini sengaja dibuat tetap, supaya setiap hasil di artikel ini bisa kamu cocokkan tanpa menebak-nebak. Tabel ini memakai produk yang sama dengan tutorial COUNTIF — bedanya, sekarang ada kolom wilayah, jadi dua atau tiga syarat bisa digabung.

Data 12 baris pesanan untuk latihan COUNTIFS dan SUMIFS
No.Produk (B)Wilayah (C)Status (D)Nominal (E)
1Kopi SusuJakartaTerkirim45000
2Teh MelatiBandungTerkirim75000
3Kopi SusuJakartaDiproses45000
4Roti CokelatJakartaTerkirim60000
5Kopi SusuBandungTerkirim45000
6Teh MelatiJakartaTerkirim25000
7Kopi TubrukBandungDiproses80000
8Kopi SusuJakartaTerkirim45000
9Roti CokelatBandungTerkirim60000
10Kopi SusuBandungDibatalkan45000
11Teh MelatiJakartaDiproses30000
12Kopi SusuJakartaDibatalkan45000
  1. Hitung dengan dua syarat teks. Di sel kosong ketik =COUNTIFS(B2:B13;"Kopi Susu";D2:D13;"Terkirim"). Hasilnya 3: baris 1, 5, dan 8. Baris 3 tidak ikut karena masih Diproses, baris 10 dan 12 tidak ikut karena Dibatalkan. Untuk wilayah, =COUNTIFS(C2:C13;"Jakarta";D2:D13;"Terkirim") hasilnya 4 (baris 1, 4, 6, 8). Cek silang: pesanan Terkirim seluruhnya ada 7 (baris 1, 2, 4, 5, 6, 8, 9), Diproses 3, Dibatalkan 2 — 7 + 3 + 2 = 12, sama dengan jumlah baris.
  2. Jumlahkan nominalnya dengan SUMIFS. Ketik =SUMIFS(E2:E13;B2:B13;"Kopi Susu";D2:D13;"Terkirim"). Hasilnya 135000: 45000 + 45000 + 45000 dari tiga baris yang sama. Perhatikan urutannya: kolom nominal (E) ditulis dulu, baru pasangan-pasangan kriterianya. Untuk Jakarta yang Terkirim, =SUMIFS(E2:E13;C2:C13;"Jakarta";D2:D13;"Terkirim") hasilnya 175000 (45000 + 60000 + 25000 + 45000).
  3. Tambah syarat ketiga. Untuk Kopi Susu yang Terkirim di Jakarta, ketik =COUNTIFS(B2:B13;"Kopi Susu";C2:C13;"Jakarta";D2:D13;"Terkirim"). Hasilnya 2 (baris 1 dan 8), dan pasangannya =SUMIFS(E2:E13;B2:B13;"Kopi Susu";C2:C13;"Jakarta";D2:D13;"Terkirim") hasilnya 90000. Tidak ada batas praktis jumlah pasangan kriteria — tapi setiap pasangan harus lengkap: range, lalu kriterianya. Pasangan yang kriterianya ketinggalan membuat Excel menolak rumusnya.
  4. Gabungkan syarat teks dengan perbandingan angka. Untuk pesanan Terkirim di atas Rp50.000, ketik =COUNTIFS(E2:E13;">50000";D2:D13;"Terkirim"). Hasilnya 3: 75000, 60000, dan 60000 (baris 2, 4, 9). Baris 7 (80000) tidak ikut karena masih Diproses. Pasangannya, =SUMIFS(E2:E13;E2:E13;">50000";D2:D13;"Terkirim") hasilnya 195000 (75000 + 60000 + 60000). Tanda > tetap wajib di dalam tanda kutip, sama seperti pada COUNTIF.
  5. Ambil angka pembanding dari sel, jangan ketik ulang. Tulis 50000 di G2, lalu ketik =COUNTIFS(E2:E13;">"&G2;D2:D13;"Terkirim"). Hasilnya tetap 3. Tanda & menggabungkan teks ">" dengan isi sel G2 menjadi kriteria ">50000". Keuntungannya: ganti isi G2 menjadi 40000 dan hasilnya langsung berubah tanpa mengedit rumus. Kalau kamu menulis ">G2" (G2 di dalam kutip), Excel justru mencari sel yang isinya teks ">G2" — dan hasilnya 0.
  6. Rata-rata dengan AVERAGEIFS, lalu kunci range-nya. Rata-rata nominal Kopi Susu yang Terkirim: =AVERAGEIFS(E2:E13;B2:B13;"Kopi Susu";D2:D13;"Terkirim") hasilnya 45000 (135000 dibagi 3). Kalau kamu mau membuat ringkasan per produk — tulis nama produk di kolom G, rumusnya di kolom H sebagai =SUMIFS($E$2:$E$13;$B$2:$B$13;G2) lalu tarik ke bawah — hasilnya: Kopi Susu 270000, Teh Melati 130000, Roti Cokelat 120000, Kopi Tubruk 80000. Jumlahkan keempatnya: 270000 + 130000 + 120000 + 80000 = 600000, sama dengan =SUM(E2:E13) — ini cara cek yang dipakai lagi di akhir. Tanda $ mengunci range supaya tidak bergeser saat ditarik; cara lengkapnya ada di tutorial referensi absolut.

Cara cek sebelum melaporkan angkanya: jumlahkan hasil tiap kriteria dalam satu kolom dan bandingkan dengan totalnya. Untuk produk: 270000 + 130000 + 120000 + 80000 = 600000, sama dengan total kolom nominal. Untuk status: 7 + 3 + 2 = 12, sama dengan jumlah baris. Kalau jumlahnya kurang, ada baris yang belum terhitung — biasanya karena kriterianya tidak persis sama dengan isi sel, misalnya spasi berlebih. Bersihkan dulu dengan TRIM, sesuai tutorial menghapus spasi berlebih, lalu hitung ulang.

Catatan locale: rumus di artikel ini memakai titik koma sebagai pemisah argumen, sesuai Excel berbahasa Indonesia. Di Excel berbahasa Inggris, ganti titik koma menjadi koma: =COUNTIFS(B2:B13,"Kopi Susu",D2:D13,"Terkirim"). Nama fungsinya tetap COUNTIFS dan SUMIFS di kedua bahasa.

4 jebakan umum dan cara memperbaikinya

  1. Kolom yang dijumlah tertukar: hasilnya 0. Rumus =SUMIFS(B2:B13;E2:E13;">50000") menghasilkan 0, bukan 275000. Sebabnya: kolom pertama SUMIFS adalah kolom yang dijumlah — di sini kolom B berisi nama produk (teks), dan jumlah dari teks adalah 0. Yang benar: =SUMIFS(E2:E13;E2:E13;">50000") hasilnya 275000 (75000 + 60000 + 80000 + 60000). Ingat: SUMIFS selalu menaruh kolom angka di depan; SUMIF menaruhnya di belakang. Kalau hasil SUMIFS-mu 0 padahal datanya jelas ada, cek dulu argumen pertamanya.
  2. Range tidak sama panjang: #VALUE! Rumus =SUMIFS(E2:E13;B2:B12;"Kopi Susu";C2:C13;"Jakarta") menghasilkan #VALUE! karena satu range hanya 11 baris (B2:B12) sementara yang lain 12 baris. Semua range dalam satu COUNTIFS/SUMIFS wajib sama besar dan sama bentuknya. Perbaikannya: samakan ujung range-nya — di contoh ini B2:B12 harus menjadi B2:B13.
  3. COUNTIFS selalu DAN, bukan ATAU. Rumus =COUNTIFS(B2:B13;"Kopi Susu";B2:B13;"Teh Melati") hasilnya 0, karena tidak ada satu baris pun yang produknya Kopi Susu dan Teh Melati sekaligus. Untuk “Kopi Susu atau Teh Melati”, jumlahkan dua rumus: =COUNTIFS(B2:B13;"Kopi Susu")+COUNTIFS(B2:B13;"Teh Melati") hasilnya 9 (6 + 3). Aturan yang sama berlaku untuk SUMIFS.
  4. Perbandingan dari sel ditulis tanpa &: hasilnya 0. Rumus =COUNTIFS(E2:E13;">G2";D2:D13;"Terkirim") menghasilkan 0 karena Excel membaca ">G2" sebagai teks biasa, bukan sebagai “lebih dari isi sel G2”. Perbaikannya: gabungkan dengan &, ">"&G2, seperti pada langkah 5 — atau tulis angkanya langsung di dalam kutip, ">50000", kalau nilainya memang tidak akan berubah.

Cara cek cepat sebelum mengirim laporannya: bandingkan jumlah hasil tiap kriteria dengan totalnya (di contoh ini 12 baris dan Rp600.000). Kalau jumlahnya pas, kriteriamu sudah mencakup semua baris. Kalau kurang, cari baris yang belum terhitung — biasanya masalah urutan argumen, range yang tidak sama panjang, atau kriteria yang belum masuk daftar.

Latihan: empat hitungan, cek sendiri hasilnya

  1. Berapa pesanan Bandung yang Terkirim? Hasil yang benar: 3 — baris 2, 5, dan 9. Rumusnya =COUNTIFS(C2:C13;"Bandung";D2:D13;"Terkirim").
  2. Berapa total nominal Roti Cokelat yang Terkirim? Hasil yang benar: 120000 — 60000 + 60000 (baris 4 dan 9). Rumusnya =SUMIFS(E2:E13;B2:B13;"Roti Cokelat";D2:D13;"Terkirim").
  3. Berapa pesanan Diproses dengan nominal di atas Rp50.000? Hasil yang benar: 1 — hanya baris 7 (80000). Rumusnya =COUNTIFS(D2:D13;"Diproses";E2:E13;">50000").
  4. Berapa rata-rata nominal Teh Melati yang Terkirim? Hasil yang benar: 50000 — (75000 + 25000) dibagi 2. Rumusnya =AVERAGEIFS(E2:E13;B2:B13;"Teh Melati";D2:D13;"Terkirim").

Setelah hitungan banyak kriteria ini beres, langkah berikutnya yang sering dibutuhkan adalah merapikan nominal per kategori menjadi laporan yang bisa dicek — lihat cara membuat rekap penjualan di Excel — atau kembali ke hub tutorial Excel untuk memilih masalah berikutnya.