Total bersyarat: COUNTIF, SUMIF, AVERAGEIF, dan versi S-nya

Idenya

Kafenya sudah berkembang dan sekarang punya cabang di tiga kota. Setiap akhir minggu, pemiliknya menerima satu daftar berisi semua penjualan dari ketiga cabang yang tercampur jadi satu. SUM bisa memberi tahu total keseluruhannya, tapi jarang sekali itu pertanyaan yang sebenarnya. Yang ingin diketahui adalah berapa penjualan Jakarta dibanding Bandung. Berapa pesanan Drinks yang masuk. Apakah rata-rata pesanan Food lebih besar daripada rata-rata minuman.

Setiap pertanyaan itu berupa total, hitungan, atau rata-rata, tapi hanya untuk sebagian baris. Kamu bisa saja mengurutkan daftarnya lalu menjumlahkan setiap kelompok secara manual, tapi minggu depan kamu harus mengulang semuanya. Karena itu Excel punya versi COUNT, SUM, dan AVERAGE yang sudah dilengkapi filter. Kamu memberi tahu baris mana yang diikutkan, misalnya "hanya yang cabangnya Jakarta", dan fungsi itu mengabaikan sisanya.

Sheet modul ini adalah daftar mingguan tersebut: sepuluh penjualan dari tiga cabang, di baris 2 sampai 11:

COUNTIF

Yang paling sederhana adalah COUNTIF, yang menjawab "ada berapa?". Kamu memberinya range tempat mencari dan nilai yang dicari, yang oleh Excel disebut kriteria, lalu fungsi itu menghitung berapa sel yang cocok:

# Hitung pesanan Drinks
H1: =COUNTIF(D2:D11, "Drinks")
# Hitung pesanan Jakarta
H2: =COUNTIF(B2:B11, "Jakarta")

H1: 6
H2: 4

Enam dari sepuluh penjualan adalah minuman, dan empat dari sepuluh penjualan terjadi di Jakarta. Sama seperti perbandingan di modul 6, kriteria teks tidak membedakan huruf besar dan kecil, jadi "drinks" juga akan cocok.

SUMIF dan AVERAGEIF

Menghitung banyaknya memang berguna, tapi yang paling dipedulikan pemilik kafe adalah uang. Untuk menjumlahkan pendapatan Jakarta, Excel harus melakukan dua hal sekaligus: melihat kolom Branch untuk menentukan baris mana yang dihitung, lalu menjumlahkan angka dari kolom Revenue untuk baris-baris itu. Jadi SUMIF menerima tiga argumen: range yang dicek, kriterianya, dan range yang dijumlahkan:

SUMIF(range yang dicek, kriteria, range yang dijumlahkan)

AVERAGEIF bekerja dengan cara yang persis sama, tapi memberimu rata-rata, bukan total:

# Jumlahkan pendapatan Jakarta
I1: =SUMIF(B2:B11, "Jakarta", F2:F11)
# Rata-rata pendapatan pesanan Food
I2: =AVERAGEIF(D2:D11, "Food", F2:F11)

I1: 697000
I2: 173333.3333

Cara yang enak untuk membaca SUMIF adalah dengan mengucapkannya: "di mana kolom B berisi Jakarta, jumlahkan kolom F". Jakarta punya empat penjualan (300000, 135000, 162000, dan 100000), dan jumlahnya 697000. Ketiga pesanan Food rata-ratanya sekitar 173333.

Kedua range sebaiknya berukuran sama, karena Excel memasangkannya baris per baris: baris ke-5 di kolom Branch berpasangan dengan baris ke-5 di kolom Revenue. Kalau satu range lebih panjang dari yang lain, barisnya tidak lagi sejajar dan jawabannya diam-diam jadi salah.

Menulis kriteria

Sejauh ini kriterianya berupa kata biasa, seperti "Jakarta", yang artinya "persis sama dengan ini". Tapi sering kali kamu ingin sesuatu yang lebih longgar, seperti "10 barang atau lebih" atau "apa saja selain Food". Caranya dengan mengawali kriteria dengan salah satu tanda perbandingan dari modul 6, ditulis di dalam tanda kutip. Ada juga dua karakter wildcard untuk mencocokkan sebagian teks:

# Hitung pesanan berisi 10 barang atau lebih
J1: =COUNTIF(E2:E11, ">=10")
# Hitung pesanan yang bukan Food
J2: =COUNTIF(D2:D11, "<>Food")
# Jumlahkan pendapatan semua produk yang diawali Kopi
J3: =SUMIF(C2:C11, "Kopi*", F2:F11)

J1: 4
J2: 7
J3: 925000

Tanda * di "Kopi*" artinya "apa pun boleh ada setelah ini", jadi cocok dengan Kopi Susu sekaligus Kopi Hitam. Ini praktis saat nama produk ditulis dengan pola yang konsisten dan kamu ingin semua jenis kopi dijumlahkan bersama tanpa menyebutnya satu per satu.

Kriteria dari sebuah sel

Mengetik ">=10" langsung di rumus memang bisa, tapi artinya kalau manajer ingin melihat pesanan 8 barang atau lebih, seseorang harus mengedit rumusnya. Lebih enak kalau angkanya ditaruh di selnya sendiri, supaya siapa pun bisa mengubahnya. Untuk memakai sel di dalam kriteria, tanda perbandingannya tetap di dalam tanda kutip, lalu selnya digabungkan dengan & (simbol penggabung, yang dibahas lengkap di modul 8). Di sini jumlah minimumnya diketik di H1:

H1: 10
# Hitung pesanan dengan jumlah minimal sebanyak yang diketik di H1
H2: =COUNTIF(E2:E11, ">="&H1)

H1: 10
H2: 4

Beberapa syarat: COUNTIFS, SUMIFS, AVERAGEIFS

Pertanyaan sungguhan sering punya lebih dari satu syarat. "Berapa penjualan Jakarta?" itu satu syarat. "Berapa penjualan Jakarta untuk minuman?" itu dua, dan sebuah baris hanya boleh dihitung kalau keduanya benar. Untuk itulah ada versi yang berakhiran S. Fungsi-fungsi ini menerima pasangan "range yang dicek, kriteria" sebanyak yang kamu perlukan, dan sebuah baris hanya dihitung kalau cocok dengan semuanya, persis seperti AND di modul 6:

# Hitung pesanan Drinks di Jakarta
L1: =COUNTIFS(B2:B11, "Jakarta", D2:D11, "Drinks")
# Jumlahkan pendapatan Drinks di Jakarta
L2: =SUMIFS(F2:F11, B2:B11, "Jakarta", D2:D11, "Drinks")
# Rata-rata jumlah barang pesanan Drinks di Bandung
L3: =AVERAGEIFS(E2:E11, B2:B11, "Bandung", D2:D11, "Drinks")

L1: 3
L2: 562000
L3: 9.5

MAXIFS, MINIFS, dan tanggal

MAXIFS dan MINIFS mencari nilai terbesar atau terkecil di antara baris-baris yang cocok, jadi bisa menjawab pertanyaan seperti "berapa pesanan minuman terbesar kita?" atau "berapa penjualan terkecil di Surabaya?". Cara menulisnya sama dengan SUMIFS: range yang dilihat di depan, lalu pasangan-pasangan syaratnya.

Filter lain yang sangat umum adalah berdasarkan tanggal, karena laporan biasanya membahas suatu periode: minggu ini, bulan ini, sejak promosi dimulai. Ingat dari modul 1 bahwa Excel menyimpan tanggal sebagai angka, jadi tanggal yang lebih akhir adalah angka yang lebih besar. Artinya kamu bisa memakai tanda perbandingan biasa, seperti ">=" untuk "pada atau setelah". Untuk menulis tanggalnya, pakai DATE(tahun, bulan, hari) lalu gabungkan dengan tandanya memakai &, sama seperti sel:

# Pesanan Drinks terbesar
M1: =MAXIFS(F2:F11, D2:D11, "Drinks")
# Penjualan Surabaya terkecil
M2: =MINIFS(F2:F11, B2:B11, "Surabaya")
# Pendapatan sejak 4 Maret
M3: =SUMIFS(F2:F11, A2:A11, ">="&DATE(2026,3,4))
# Hitung pesanan dari 3 sampai 4 Maret
M4: =COUNTIFS(A2:A11, ">="&DATE(2026,3,3), A2:A11, "<="&DATE(2026,3,4))

M1: 300000
M2: 180000
M3: 1102000
M4: 4

Pesanan minuman terbesar adalah yang 300000 dari Jakarta, dan penjualan terkecil Surabaya adalah 180000. M3 menjumlahkan semua yang terjual sejak 4 Maret.

M4 layak diperhatikan lebih teliti, karena memakai kolom yang sama dua kali. Tidak ada satu kriteria yang artinya "antara tanggal 3 dan 4", jadi kamu memberi dua syarat pada kolom tanggal: "pada atau setelah tanggal 3" dan "pada atau sebelum tanggal 4". Hanya tanggal yang lolos kedua pengecekan yang dihitung, dan hasilnya empat penjualan dari dua hari itu.

Tabel ringkasan

Sekarang kamu bisa menggabungkan semuanya dan menjawab pertanyaan pertama pemilik kafe dengan benar, lewat tabel kecil berisi pendapatan setiap cabang. Kamu bisa saja menulis SUMIFS terpisah untuk setiap cabang, tapi dengan referensi absolut dari modul 4, satu rumus bisa mengisi seluruh tabel. Nama cabang ada di kolom H, dan setiap baris mencari cabangnya sendiri:

H1: Branch
H2: Jakarta
H3: Bandung
H4: Surabaya
I1: Revenue
I2:I4: =SUMIFS($F$2:$F$11, $B$2:$B$11, H2)

H1: Branch
H2: Jakarta
H3: Bandung
H4: Surabaya
I1: Revenue
I2: 697000
I3: 559000
I4: 675000

Range datanya dikunci dengan $, karena setiap baris harus mencari di daftar penjualan yang sama. Kriteria H2 dibiarkan relatif, jadi saat rumusnya diisi ke bawah, referensinya bergeser ke H3 lalu H4, dan setiap baris mengambil nama cabangnya sendiri. Kalau cabang keempat dibuka, kamu cukup menambahkan namanya di bawah Surabaya lalu mengisi rumusnya satu baris lagi.

Yang baru saja kamu buat adalah laporan ringkasan buatan tangan, dan ini kebutuhan yang sangat umum. Modul 15 menunjukkan PivotTable, yang membuat tabel seperti ini untukmu secara otomatis.

Rangkuman

• COUNTIF(range, kriteria) menghitung yang cocok; SUMIF dan AVERAGEIF menjumlahkan atau merata-ratakan kolom lain di baris yang cocok.
• Kriteria bisa berupa "teks", "<>teks", ">=10", wildcard "Kopi*" dan "?", atau ">="&sel.
• COUNTIFS, SUMIFS, AVERAGEIFS mensyaratkan semua syarat; range jumlah atau rata-ratanya ada di depan.
• Kunci range data dan biarkan kriterianya relatif untuk mengisi tabel ringkasan.