SUMIFS, COUNTIFS, dan Conditional Formatting — Analisis Data Bertingkat dari Nol

Bayangkan Anda seorang manajer penjualan yang mengawasi tujuh produk kopi di lima wilayah, dijual oleh delapan salesperson, sepanjang dua belas bulan. Setiap transaksi tercatat sebagai satu baris di Excel — setahun penuh, tabel itu membengkak menjadi 500 baris. Lalu dalam rapat pagi, direktur melempar lima pertanyaan berurutan: berapa omzet Kopi Arabika di Q4? Wilayah mana yang paling lesu? Salesperson siapa yang omzet per transaksinya paling tinggi? Berapa banyak transaksi yang melampaui Rp 2 juta? Produk mana yang secara konsisten tampil di atas rata-rata?

Anda bisa menjawabnya satu per satu dengan filter manual dan kalkulator. Tapi lima pertanyaan itu berubah setiap minggu. Yang Anda butuhkan adalah rumus yang menjawab “jumlahkan Omzet untuk kondisi A dan kondisi B dan kondisi C”. Itulah pekerjaan SUMIFS. Saudara kembarnya, COUNTIFS, menghitung banyaknya baris yang memenuhi kondisi; AVERAGEIFS menghitung rata-ratanya. Tetapi angka saja tidak cukup — untuk meyakinkan direktur, omzet wilayah perlu disajikan dengan warna: merah untuk yang rendah, hijau untuk yang tinggi, gradasi di antaranya. Itulah pekerjaan conditional formatting.

Artikel ini menuntun Anda dari nol: sintaks SUMIFS dengan satu lalu banyak kriteria, COUNTIFS dan AVERAGEIFS, matriks Produk × Kuartal yang otomatis, lalu color scale, data bars, icon sets, dan aturan berbasis rumus. Semua langkah bisa Anda ikuti langsung dengan berkas latihan yang sudah disediakan.

Apa Itu Conditional Aggregation

Conditional aggregation adalah operasi meringkas data dengan satu atau lebih syarat (kriteria). Tiga fungsi dalam keluarga ini bekerja sebagai pasangan simetris:

Ketiganya punya bentuk yang konsisten: tentukan apa yang dirangkum dulu, lalu daftarkan pasangan kriteria (kolom-sumber, nilai-yang-dicari) sebanyak yang Anda butuhkan. Tidak ada batas praktis pada jumlah kriteria.

SUMIF vs SUMIFS. Excel punya dua versi: SUMIF (tanpa S) hanya menerima satu kriteria, SUMIFS (dengan S) menerima banyak kriteria. Susunan argumennya berbeda: SUMIF(range, criteria, [sum_range]) menempatkan rentang kriteria di depan, sedangkan SUMIFS(sum_range, criteria_range1, criteria1, ...) menempatkan rentang penjumlahan di depan. Aturan praktis: selalu pakai SUMIFS, bahkan ketika hanya butuh satu kriteria — argumen sum_range di depan lebih jelas, dan Anda tidak perlu menulis ulang rumus ketika nanti butuh menambah kriteria kedua.

Conditional formatting adalah pelengkap visual. Ia mengubah tampilan sel berdasarkan nilainya: warna gradient untuk magnitude, batang horizontal untuk perbandingan, atau penyorotan presisi untuk kasus khusus. Dalam analisis nyata, keduanya bekerja berurutan — SUMIFS menghasilkan angka, conditional formatting membuat angka itu langsung terbaca tanpa harus dipelototi baris demi baris.

Inilah seluruh perjalanan analisis bertingkat dalam satu gambar:

Persiapan: Unduh Berkas Latihan

Agar Anda tidak sekadar membaca, sediakan berkas latihan berikut yang berisi dataset 500 baris penjualan kopi (7 produk × 5 wilayah × 8 salesperson × 12 bulan) dan lima lembar latihan bertahap.

⬇ Unduh sumifs-cf-latihan.xlsx

Berkas berisi tujuh lembar:

• PETUNJUK — ikhtisar isi workbook dan legenda warna.
• DATA — 500 baris transaksi mentah (Tanggal, Kuartal, Bulan, Wilayah, Produk, Salesperson, Kuantitas, Omzet, Diskon, Harga Satuan). Sudah berbentuk Excel Table bernama tblPenjualan.
• 01-SUMIFS-Basic — SUMIFS satu & dua kriteria, COUNTIFS, AVERAGEIFS. Sel kriteria berwarna biru tebal.
• 02-Multi-Criteria — matriks 7 Produk × 4 Kuartal via SUMIFS, plus blok COUNTIFS untuk validasi.
• 03-Color-Scale — color scale 2 dan 3 warna pada omzet per wilayah, per produk, dan matriks Salesperson × Wilayah.
• 04-Data-Bars — data bars plus highlight top-3 dan bottom-2 salesperson.
• 05-Formula-HL — conditional formatting berbasis rumus: di atas rata-rata, duplikat, akhir pekan, diskon besar, dan top-3 omzet.

Sel input berwarna biru tua dan tebal — silakan diubah untuk menguji rumus. Sel rumus berwarna hitam dan hitung otomatis.

Bagian 1 — SUMIFS: Menjumlahkan dengan Banyak Kriteria

SUMIFS menjumlahkan sel-sel di sum_range yang semua pasangan kriterianya terpenuhi. Logikanya adalah DAN (AND): tiap baris harus lulus uji setiap kriteria untuk ikut dijumlahkan. Jika satu saja gagal, baris itu diabaikan.

Sintaks

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Tiga peran utama yang berulang:

Setiap kriteria tambahan selalu hadir sebagai pasangan: satu criteria_range dan satu criteria. Lupa memberi pasangan (menulis criteria tanpa criteria_range) menghasilkan error rumus.

Latihan 1.1 — Satu Kriteria: Omzet per Wilayah

Pertanyaan paling sederhana: berapa total Omzet untuk wilayah Jabodetabek?

Di lembar 01-SUMIFS-Basic, sel C4 berisi kriteria wilayah (Jabodetabek). Rumus di sel C11:

=SUMIF(tblPenjualan[Wilayah], $C$4, tblPenjualan[Omzet])

Cara membaca: “Jumlahkan kolom Omzet, tetapi hanya untuk baris yang Wilayah-nya sama dengan isi sel C4.” Ini adalah bentuk SUMIF klasik (satu kriteria). Perhatikan urutannya: rentang kriteria (Wilayah) di depan, rentang penjumlahan (Omzet) di belakang.

Sekarang bandingkan dengan bentuk SUMIFS, yang juga menangani satu kriteria:

=SUMIFS(tblPenjualan[Omzet], tblPenjualan[Wilayah], $C$4)

Urutannya terbalik: sum_range (Omzet) di depan, lalu pasangan kriteria. Hasilnya identik, tetapi bentuk SUMIFS inilah yang akan kita perluas. Ubah C4 menjadi Jawa Barat — angka berubah seketika. Satu rumus, semua wilayah.

Latihan 1.2 — Dua Kriteria: Wilayah + Produk

Pertanyaan kedua menyempit: berapa Omzet Kopi Arabika di Jabodetabek? Sekarang ada dua kondisi yang harus terpenuhi bersamaan. Inilah momen SUMIFS bersinar. Sel C12:

=SUMIFS(tblPenjualan[Omzet], tblPenjualan[Wilayah], $C$4, tblPenjualan[Produk], $C$5)

Cara membaca: “Jumlahkan Omzet untuk baris yang Wilayah-nya = C4 DAN Produk-nya = C5.” Excel memeriksa tiap baris: hanya yang lulus kedua uji sekalian yang ikut dijumlahkan. Hasilnya jauh lebih kecil dari Latihan 1.1 karena lingkupnya mengecil dari satu wilayah menjadi satu produk di satu wilayah.

Kenapa tidak pakai dua SUMIF? Orang yang belum mengenal SUMIFS kadang mencoba: jumlahkan Omzet untuk Jabodetabek, kurangi jumlah Omzet untuk non-Arabika. Ini tidak hanya rumit, tetapi salah secara logika. SUMIFS menguji kedua kondisi pada baris yang sama; pendekatan pengurangan menguji kondisi pada himpunan baris berbeda. Untuk logika DAN, hanya SUMIFS yang benar.

Latihan 1.3 — Tiga Kriteria: Tambahkan Kuartal

Tambahkan kriteria ketiga untuk ringkasan presisi: Omzet Kopi Arabika di Jabodetabek selama Q4. Cukup daftarkan pasangan kriteria ketiga:

=SUMIFS(tblPenjualan[Omzet], tblPenjualan[Wilayah], $C$4, tblPenjualan[Produk], $C$5, tblPenjualan[Kuartal], $C$6)

Pola jelas: setiap kriteria baru adalah sepasang argumen di akhir. Tidak ada batas struktural. Ubah C6 dari Q4 ke Q1 — omzet Q4 yang ramai liburan langsung dibandingkan dengan Q1 yang lebih sepi, dalam satu sel.

Bagian 2 — COUNTIFS dan AVERAGEIFS

SUMIFS menjumlahkan, tetapi dua pertanyaan analitik sama seringnya muncul: berapa banyaknya transaksi, dan berapa rata-ratanya. Keduanya punya saudara bentuk yang sama persis.

COUNTIFS — Menghitung Banyaknya Baris

COUNTIFS menghitung banyaknya baris yang memenuhi semua kriteria. Perbedaan kunci dari SUMIFS: tidak ada sum_range — yang dihitung adalah baris itu sendiri, bukan nilai numerik.

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Di sel C14, hitung jumlah transaksi Kopi Arabika di Jabodetabek:

=COUNTIFS(tblPenjualan[Wilayah], $C$4, tblPenjualan[Produk], $C$5)

Hasilnya bilangan bulat — misalnya 14, artinya ada empat belas transaksi yang memenuhi kedua kondisi. Ini jawaban untuk “berapa kali”, yang tidak bisa dijawab SUMIFS (yang menjawab “berapa total nilai”).

Kriteria Numerik: Lebih Besar / Lebih Kecil

COUNTIFS sering dipakai dengan kriteria numerik yang ditulis sebagai teks. Pertanyaan: berapa banyak transaksi dengan Omzet di atas Rp 2.000.000? Sel C15:

=COUNTIFS(tblPenjualan[Omzet], ">2000000")

Tanda kutip penting: ">2000000" adalah teks yang memuat operator dan nilai. Operator yang didukung: ">N", "<N", ">=N", "<=N", "<>N". Tanpa tanda kutip, Excel mencari nilai persis 2000000.

Kriteria dinamis dengan referensi sel. Kalau ambang batas ingin diubah tanpa menyentuh rumus, simpan ambang di sel (misalnya D1) dan gabungkan teks: ">" & D1. Rumus lengkap: =COUNTIFS(tblPenjualan[Omzet], ">"&D1). Inilah pola yang membuat dasbor interaktif.

AVERAGEIFS — Rata-rata Bersyarat

AVERAGEIFS menghitung rata-rata average_range untuk baris yang memenuhi semua kriteria. Susunannya sama dengan SUMIFS:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Di sel C16, rata-rata Omzet per transaksi untuk Kopi Arabika di Jabodetabek:

=AVERAGEIFS(tblPenjualan[Omzet], tblPenjualan[Wilayah], $C$4, tblPenjualan[Produk], $C$5)

Hubungan matematisnya rapi: AVERAGEIFS = SUMIFS ÷ COUNTIFS untuk pasangan kriteria yang sama. Sel C16 dibagi C14 harus sama dengan C12. Hubungan ini berguna untuk validasi — kalau ketiganya tidak konsisten, ada yang salah di kriteria Anda.

Hati-hati menafsirkan rata-rata. Rata-rata omzet per transaksi yang tinggi bisa berarti produk mahal dengan transaksi besar, atau produk yang laku sedikit tetapi kebetulan nilainya besar. Pasangkan AVERAGEIFS dengan COUNTIFS agar Anda tahu berapa transaksi menjadi dasar rata-rata itu. Rata-rata dari tiga transaksi tidak boleh dipakai untuk keputusan besar.

Latihan 2.1 — Triplet Lengkap

Untuk setiap pasangan kriteria yang Anda analisis, biasakan menghitung ketiganya berurutan: SUMIFS (total nilai), COUNTIFS (banyaknya kejadian), AVERAGEIFS (nilai rata-rata per kejadian). Tiga angka bersama-sama memberi gambaran lengkap; satu angka saja menyesatkan. Sel C17 ada pemeriksaan silang: rata-rata Omzet untuk semua data (=AVERAGE(tblPenjualan[Omzet])). Bandingkan dengan rata-rata segmen Anda — selisih keduanya adalah sinyal apakah segmen overperform atau underperform terhadap keseluruhan.

Bagian 3 — Matriks Ringkasan: Produk × Kuartal

SUMIFS satu sel menjawab satu pertanyaan. Tetapi laporan eksekutif jarang berbentuk “satu angka” — ia berbentuk matriks: produk di baris, kuartal di kolom, omzet di perpotongan. Membuatnya untuk setiap sel (28 sel untuk 7 produk × 4 kuartal) tampak melelahkan, tetapi kuncinya adalah referensi campuran: kunci kolom dengan $ di depan huruf, kunci baris dengan $ di depan angka, biarkan sisanya beradaptasi saat rumus disalin.

Anatomi Referensi Campuran

Di lembar 02-Multi-Criteria, header kuartal ada di baris 4 (B4 = Q1, …, E4 = Q4), dan nama produk di kolom A (A5 = Kopi Arabika, dst.). Rumus di sel B5:

=SUMIFS(tblPenjualan[Omzet], tblPenjualan[Produk], $A5, tblPenjualan[Kuartal], B$4)

Dua jenis kunci yang membuat matriks bekerja:

• $A5 — $ di depan A mengunci kolom (selalu ambil dari kolom A), tetapi baris 5 bebas berubah saat disalin ke bawah. Salin ke B6, dan Excel mengubahnya menjadi $A6 — mengambil produk dari baris 6.
• B$4 — $ di depan 4 mengunci baris (selalu ambil dari baris 4), tetapi kolom B bebas berubah saat disalin ke kanan. Salin ke C5, dan Excel mengubahnya menjadi C$4 — mengambil kuartal dari kolom C.

Gabungan keduanya berarti: satu rumus di B5, lalu disalin ke seluruh matriks B5:E11, menghasilkan 28 perhitungan yang benar. Anda menulis rumus sekali, Excel mengadaptasi 27 salinannya.

Grand Total dengan SUM Biasa

Setelah matriks terisi, baris Grand Total di bawah dan kolom Grand Total di kanan adalah penjumlahan biasa dari rentang matriks — bukan SUMIFS baru:

F5:  =SUM(B5:E5)      ' total baris Kopi Arabika
B12: =SUM(B5:B11)     ' total kolom Q1

Angka di perpotongan kanan-bawah (F12) harus sama dengan total Omzet seluruh data. Kalau berbeda, ada kriteria yang tidak menangkap semua baris — bug yang penting dideteksi. Lembar latihan menyertakan blok kedua yang memakai COUNTIFS dengan struktur yang sama; angka di perpotongan kanan-bawah blok itu harus 500 — total baris di dataset. Kalau bukan 500, ada produk yang lolos dari daftar atau kuartal yang salah ketik.

SUMIFS vs Pivot Table — mana yang dipakai? Keduanya menjawab pertanyaan yang sama. Pivot Table (lihat artikel Pivot Table) menyelesaikan matriks ini tanpa rumus — tarik Produk ke Rows, Kuartal ke Columns, Omzet ke Values. SUMIFS menyelesaikannya dengan rumus yang transparan dan bisa Anda kontrol presisi. Aturan praktis: untuk eksplorasi cepat, pakai Pivot Table; untuk laporan yang harus terkunci ke template tetap dan di-refresh otomatis tiap data berubah, pakai SUMIFS.

Bagian 4 — Conditional Formatting: Color Scale

Setelah angka ada, tantangan berikutnya adalah membaca angka itu dengan cepat. Tabel 7 produk × 4 kuartal penuh angka rupiah — mana yang besar, mana yang kecil? Otak lebih cepat menangkap warna daripada membandingkan tujuh digit. Color scale mengubah rentang sel menjadi gradient warna yang memetakan magnitude secara otomatis.

Color Scale 3 Warna

Pilihan paling informatif: tiga warna membentang dari nilai minimum, melalui titik tengah (median atau persentil-50), ke nilai maksimum. Skema paling umum: merah (rendah) → kuning (tengah) → hijau (tinggi) — konvensi lampu lalu lintas.

Untuk menerapkannya pada kolom Total Omzet per Wilayah di lembar 03-Color-Scale (rentang B6:B10):

1. Pilih sel (B6:B10).
2. Tab Home → Conditional Formatting → Color Scales.
3. Pilih gradient tiga warna (merah-kuning-hijau).

Excel otomatis menghitung minimum, median, dan maksimum, lalu mewarnai tiap sel sesuai posisinya. Anda tidak perlu ambang batas manual. Jenis titik yang bisa dipakai untuk start_type/mid_type/end_type: min/max (ekstrem data), percentile (misalnya 50 untuk median), num (angka eksplisit), atau formula. Default min/median/max aman untuk kebanyakan kasus.

Color Scale 2 Warna

Untuk data yang punya nilai nol sebagai titik acuan alami (pangsa persentase, margin), gradient dua warna sering lebih jelas. Lembar latihan mewarnai kolom Pangsa % (C6:C10) dengan putih → hijau: pangsa 0% putih, pangsa tertinggi hijau pekat. Wilayah dominan menonjol tanpa distraksi warna merah yang tidak bermakna untuk persentase.

Kapan Jangan Pakai Color Scale

Color scale punya kelemahan: ia selalu mewarnai semua sel dalam rentang, sehingga selalu ada yang “merah” dan “hijau” sekalipun semua nilai baik. Untuk menyorot pengecualian, gunakan formula-based highlight (Bagian 6) alih-alih color scale.

Bagian 5 — Conditional Formatting: Data Bars dan Icon Sets

Data Bars — Batang di Dalam Sel

Data bars menggambar batang horizontal di dalam sel, proporsional terhadap nilai relatif terhadap maksimum rentang. Efeknya seperti grafik batang mini yang terbenam di kolom angka — ideal untuk membandingkan ranking tanpa grafik terpisah.

Di lembar 04-Data-Bars, kolom Total Omzet per Salesperson (C6:C13) memiliki data bars hijau. Salesperson dengan omzet tertinggi dapat batang penuh; yang lain lebih pendek. Untuk menerapkan:

1. Pilih rentang (C6:C13).
2. Home → Conditional Formatting → Data Bars.
3. Pilih warna (gradient atau solid).

Keunggulan data bars dibanding color scale: ia menampilkan besaran relatif dengan jelas (panjang batang), bukan hanya kategori (warna). Kombinasi keduanya di kolom berbeda memberi dasbor yang kaya. Lembar latihan juga menerapkan data bars biru pada rata-rata Omzet per Produk (B17:B23).

Icon Sets — Simbol Berperingkat

Icon sets menambahkan simbol kecil di sebelah angka: panah naik/datar/turun, lingkaran warna, bendera, atau bintang. Berbeda dari color scale yang kontinu, icon sets mengotomatiskan kategorisasi menjadi 3–5 kelas.

Untuk menambahkannya: Home → Conditional Formatting → Icon Sets → pilih set. Yang paling intuitif untuk data numerik berperingkat adalah panah tiga arah (hijau naik, kuning datar, merah turun). Atur ambang tiap kelas lewat Manage Rules → Edit Rule: misalnya hijau untuk ≥ 67 persentil, kuning 33–67, merah < 33.

Tips praktis icon sets. Aturan default membagi tiga kelas rata berdasarkan persentil. Untuk data berdistribusi miring, atur ambang manual lewat Edit Rule — tanpa ini, kelas hijau bisa terisi nilai medioker. Icon sets paling berguna di kolom yang menyatakan perubahan (delta bulan-ke-bulan), bukan level absolut.

Bagian 6 — Formula-Based Highlight: Penyorotan Presisi

Color scale dan data bars bekerja otomatis berdasarkan nilai. Tetapi banyak pertanyaan analitik butuh logika kustom: “sorot baris yang omzetnya di atas rata-rata”, “tandai transaksi akhir pekan”, “cari duplikat”. Untuk semua ini, Excel menyediakan conditional formatting berbasis rumus (formula-based), yang mengevaluasi rumus logika per baris dan menyorot seluruh baris bila rumus menghasilkan TRUE.

Mekanisme Dasar

Aturan formula-based selalu mengikuti pola yang sama:

1. Pilih rentang yang ingin diwarnai (satu kolom atau seluruh tabel A6:J65).
2. Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3. Tulis rumus logika yang mengembalikan TRUE/FALSE, dengan referensi ke sel pertama di rentang atas-kiri (misalnya $H6 — kolom dikunci, baris bebas).
4. Atur format (warna isi, font tebal) untuk sel yang lulus uji.

Kunci penting: referensi dalam rumus CF relatif terhadap sel aktif rentang saat Anda menulis aturan. Kalau rentang mulai dari baris 6, tulis $H6, bukan $H$6. Saat Excel menerapkan aturan ke baris 7, ia mengubah $H6 menjadi $H7 otomatis — itulah yang membuat aturan bekerja per baris.

Aturan 1 — Di Atas Rata-rata

Sorot semua baris yang omzetnya melampaui rata-rata seluruh transaksi:

=$H6 > AVERAGE($H$6:$H$65)

Dua jenis referensi: $H6 (kolom terkunci, baris bebas) untuk omzet baris yang diuji; $H$6:$H$65 (sepenuhnya terkunci) untuk rentang rata-rata. Hasilnya: sekitar setengah baris tersorot, dan pola distribusinya langsung terlihat.

Aturan 2 — Jauh Di Bawah Rata-rata

Sorot baris yang omzetnya kurang dari 50% rata-rata — sinyal transaksi lemah:

=$H6 < AVERAGE($H$6:$H$65) * 0.5

Kali ini hanya segelintir baris tersorot — yang benar-benar pencilan rendah. Jauh lebih informatif daripada “di bawah rata-rata”, yang akan menyebabkan hampir setengah baris tersorot tanpa membantu identifikasi masalah.

Aturan 3 — Akhir Pekan

Data berbasis tanggal sering perlu memisahkan hari kerja dari akhir pekan. Lembar 05-Formula-HL menambahkan kolom helper B berisi =TEXT(A6,"ddd") menghasilkan Mon, Tue, …, Sun. Aturan CF lalu menyorot Sabtu dan Minggu:

=OR($B6="Sat", $B6="Sun")

Setiap transaksi akhir pekan otomatis berlatar kuning. Pola yang muncul — apakah omzet akhir pekan lebih tinggi (wajar untuk produk konsumen) atau lebih rendah (indikasi penjualan B2B) — menjadi pertanyaan analitik baru.

Aturan 4 — Diskon Besar

Transaksi dengan diskon tinggi sering perlu ditinjau — wajar atau ada salesperson yang terlalu dermawan? Sorot baris dengan diskon ≥ 10%:

=$I6 >= 0.1

Kolom dikunci ($I6) supaya aturan mengevaluasi kolom Diskon meskipun diterapkan ke seluruh baris tabel.

Aturan 5 — Top-N Otomatis

Untuk menyorot tiga transaksi terbesar, gabungkan dengan fungsi LARGE:

=$H6 >= LARGE($H$6:$H$65, 3)

LARGE(rentang, 3) mengembalikan nilai terbesar ke-3 di rentang. Setiap baris yang omzetnya ≥ nilai itu ikut tersorot. Ubah angka 3 ke 10 untuk top-10, ke 1 untuk hanya juara. Aturan ini otomatis beradaptasi saat data berubah.

Aturan 6 — Duplikat Berbasis Banyak Kolom

Excel punya aturan bawaan “Highlight Duplicate Values”, tetapi ia hanya bekerja pada satu kolom. Untuk mendeteksi duplikat berbasis kombinasi kolom (misalnya salesperson + produk yang sama muncul berkali-kali), gunakan COUNTIFS dalam rumus CF:

=COUNTIFS($F$6:$F$65, $F6, $E$6:$E$65, $E6) > 1

Cara membaca: “Hitung berapa baris yang Salesperson-nya sama dengan baris ini DAN Produk-nya sama dengan baris ini. Kalau lebih dari 1, pasangan ini muncul berulang — sorot.” Pola ini sangat berguna untuk audit data — duplikat kombinasi sering menandakan entri ganda atau pelanggaran batasan unik.

Urutan Aturan dan Prioritas

Saat beberapa aturan CF menimpa sel yang sama, Excel menerapkan aturan dari atas ke bawah dalam panel Manage Rules, dan berhenti di aturan pertama yang TRUE jika opsi “Stop If True” dicentang. Untuk mengatur ulang prioritas: Home → Conditional Formatting → Manage Rules, lalu sorot aturan dan pakai panah naik/turun. Letakkan aturan paling spesifik (top-3, duplikat) di atas aturan umum (di atas rata-rata), supaya pengecualian tidak ditimpa.

Cek Pemahaman

1. Manajer Anda menulis =SUMIFS(tblPenjualan[Omzet], tblPenjualan[Wilayah], "Jabodetabek"). Hasilnya muncul, tetapi ketika dia menambah kriteria kedua menjadi ..., tblPenjualan[Produk], Kopi Arabika), rumus mengembalikan error #NAME?. Apa sebabnya?

2. Anda membuat matriks Produk × Kuartal dengan rumus =SUMIFS(tblPenjualan[Omzet], tblPenjualan[Produk], A5, tblPenjualan[Kuartal], B4) di sel B5, lalu menyalinnya ke kanan dan ke bawah. Hasilnya semua sel salah. Mengapa, dan bagaimana memperbaikinya?

3. Anda menerapkan color scale merah-kuning-hijau pada kolom omzet 8 salesperson, dan ternyata 2 orang mendapat warna merah padahal performa mereka sebenarnya baik (di atas target perusahaan). Apa masalahnya, dan aturan CF apa yang lebih sesuai?

4. Anda ingin menyorot transaksi yang GAGAL memenuhi kedua kondisi berikut: omzet ≥ Rp 1.000.000 DAN kuantitas ≥ 10. Tulis rumus CF-nya.

=OR($H6<1000000, $G6<10)

Kesalahan Umum

Tidak memberi tanda kutip pada kriteria teks. SUMIFS(..., "Jabodetabek") benar; SUMIFS(..., Jabodetabek) menghasilkan #NAME? karena Excel menganggapnya nama range. Biasakan: kriteria teks selalu di dalam tanda kutip ganda.

Lupa referensi campuran di matriks. Menulis =SUMIFS(Omzet, Produk, A5, Kuartal, B4) tanpa $ menyebabkan hasil acak saat disalin. Kunci kolom referensi baris ($A5) dan kunci baris referensi kolom (B$4).

Mengira COUNTIFS menjumlahkan. COUNTIFS menghitung banyaknya baris, bukan nilai. COUNTIFS(tblPenjualan[Omzet], ">2000000") menghasilkan bilangan bulat (jumlah transaksi besar), bukan total rupiahnya. Untuk total nilai, pakai SUMIFS.

Memakai color scale untuk data dengan ambang eksternal. Color scale selalu mengisi sel “terendah” dengan merah dan “tertinggi” dengan hijau, sekalipun semuanya bagus. Untuk data dengan target dari luar, pakai formula-based highlight dengan ambang eksplisit.

Banyak aturan CF yang saling menimpa tanpa “Stop If True”. Beberapa aturan dengan format berbeda pada rentang yang sama bisa menghasilkan tampilan tak terduga — hanya aturan prioritas tertinggi yang menang. Atur urutan di Manage Rules, centang “Stop If True” untuk aturan mutlak, dan hapus aturan lama.

Menulis rumus CF dengan referensi absolut penuh. =$H$6>1000000 di rentang H6:H65 hanya mengevaluasi satu sel (H6) untuk semua baris. Yang benar: $H6 (kolom terkunci, baris bebas) supaya tiap baris dievaluasi terhadap omzetnya sendiri.

Mengabaikan error di kolom kriteria. Satu sel #N/A di kolom kriteria membuat SUMIFS mengabaikan baris itu diam-diam — jumlah tidak error, tetapi kurang dari seharusnya. Bersihkan error di sumber (IFERROR) sebelum membangun ringkasan.

Dipakai di Praktik

• Laporan penjualan bulanan. Manajer wilayah memakai SUMIFS untuk memantau omzet per produk per kuartal per wilayah, dengan conditional formatting menyorot wilayah di bawah target. Satu lembar, semua pertanyaan, tanpa pivot.
• Analisis kuesioner survei. COUNTIFS menghitung banyaknya responden per demografi, AVERAGEIFS menghitung rata-rata skor per kelompok. Color scale membuat perbedaan antarkelompok langsung terlihat.
• Audit transaksi keuangan. Akuntan memakai formula-based CF untuk mendeteksi duplikat, transaksi di atas rata-rata (anomali), dan transaksi akhir pekan (banyak fraud terjadi Sabtu/Minggu).
• Dasbor KPI real-time. SUMIFS yang mereferensi sel kriteria (misalnya dropdown bulan) menjadi dasbor interaktif: ganti bulan, seluruh matriks dan color scale beradaptasi. Tanpa macro, tanpa Pivot refresh.
• Riset kuantitatif tesis. Mahasiswa S2 yang mengolah data panel (perusahaan × tahun) memakai SUMIFS untuk meringkas variabel, lalu CF untuk inspeksi visual outlier sebelum uji asumsi regresi.

Lanjutan

• Pivot Table Excel — SUMIFS dan Pivot Table menjawab pertanyaan yang sama; kenali kapan masing-masing lebih efisien (template tetap vs eksplorasi cepat).
• VLOOKUP, INDEX-MATCH, XLOOKUP — lookup mengambil nilai individual; SUMIFS meringkas banyak nilai; keduanya sering dirantai dalam analisis (lookup kode ke nama, lalu SUMIFS per nama).
• Tendensi Sentral — AVERAGEIFS menghitung rata-rata bersyarat; kenali kapan rata-rata menyesatkan dan median lebih tepat.
• Memilih Topik Tesis (FINER) — analisis data bertingkat dengan SUMIFS/COUNTIFS adalah inti banyak tesis kuantitatif setelah data terkumpul.