Rumus SUMPRODUCT : Cara Membuat Ranking Bersyarat
Trik Menghitung Ranking dengan Beberapa Kondisi atau Syarat Berdasarkan Kategori
tingkat : MAHIR
Sintaksis
Berikut adalah sintaksis penulisan Rumus SUMPRODUCT untuk Ranking bersyarat atau Ranking dengan lebih dari satu kriteria.
//Konsep penulisan rumus SUMPRODUCT untuk Ranking bersyarat
=SUMPRODUCT( (DataKategori1=Teks1)*(DataKategori2=Teks2)*...*(DataKategoriX<AngkaX) ) + 1
//Descending atau ranking besar ke kecil, dengan menggunakan range
=SUMPRODUCT(($C$10:$C$24=C21) * ($E$10:$E$24>E21))+1
//Ascending atau ranking kecil ke besar, dengan menggunakan tabel
=SUMPRODUCT((tbl_data[Area]=tbl_data[@Area]) * (tbl_data[Penjualan]<tbl_data[@Penjualan]))+1
//Descending dengan mengakomodir nilai double atau duplikat
//perhatikan teknik kuncian cell terutama pada rumus COUNTIFS-nya
=SUMPRODUCT((name_rng_area=C21)*(name_rng_sales>E21))+COUNTIFS($C$21:C30,C21,$E$21:E30,E21)
Kegunaan Utama di Tempat Kerja
Ranking bersyarat atau Ranking dengan beberapa kondisi berdasarkan kategori, tidaklah jarang ditemui. Sederhana, tetapi cukup tricky, contoh kegunaannya adalah seperti:
- Evaluasi Penjualan Per Cabang: Menghitung urutan salesman terbaik di masing-masing kota tanpa terganggu oleh angka penjualan dari cabang lain.
- Penilaian Kinerja Karyawan (KPI): Menentukan peringkat nilai KPI staf berdasarkan departemen atau divisi secara otomatis.
- Analisis Produk Terlaris: Menentukan urutan barang paling laku per kategori produk pada laporan inventaris (inventory report).
Sifatnya yang dinamis membuat posisi peringkat otomatis ter-update begitu data angka diisi ulang, sehingga Anda tidak perlu melakukan sort tabel secara manual terus-menerus.
Error yang Sering Ditemukan dan Cara Mengatasinya
Saat menerapkan rumus SUMPRODUCT untuk ranking, ada beberapa tips pro agar error berikut ini tidak sampai terjadi:
- Lupa Mengunci Absolute Reference ($): Jika range tidak dikunci dengan tombol F4, akan menjadi bergeser semua. Baca disini penjelasan cara mengunci cell.
- Error #VALUE!: kemungkinan jika didalam suatu cell terdapat error juga, cara mengatasinya, pakai rumus IFERROR.
- Peringkat Kembar (Tie Rank): Jika ada dua salesman memiliki angka penjualan yang persis sama di kategori yang sama, keduanya akan mendapatkan nomor urut yang sama. Hal ini jarang terjadi, tapi jika ada, maka bisa dikombinasikan dengan rumus COUNTIFS.
- Typo pada Kategori: Perbedaan penulisan atau typo seperti “Jakarta” vs “Jakarta “, jelas akan mengakibatkan dianggap jadi kategori berbeda. Jika ada spasi berlebih maka bersihkan dengan rumus TRIM.
💡Pro Tips : jika datanya cukup banyak, maka kemungkinan error akan banyak terjadi, maka dari itu pastikan dulu raw data kita sudah bagus, benar, dan bersih.
FAQ – Pertanyaan yang sering ditanyakan
Rumus RANK biasa hanya dapat menghitung peringkat dari seluruh baris data secara global tanpa kriteria. Sementara SUMPRODUCT dapat menghitung ranking bersyarat berdasarkan kelompok atau kategori tertentu (misalnya per cabang atau per divisi).
Rumus SUMPRODUCT bekerja dengan mengalikan kondisi kriteria (misal: Cabang="Jakarta") dengan kondisi nilai yang lebih besar (misal: Omset > Sel_Tujuan). Hasil perkalian logika tersebut dikalkulasi menjadi angka urutan peringkat secara dinamis.
Error #VALUE! terjadi karena ada sel di dalam rentang data yang berisi teks, spasi kosong tersembunyi, atau format angka yang tidak valid. Pastikan seluruh rentang kolom nilai hanya berisi format numerik murni.
Ya, rumus ini terbukti 100% kompatibel dan berjalan lancar baik di Microsoft Excel (semua versi) maupun di Google Sheets tanpa memerlukan pengetikan kombinasi tombol Array (Ctrl + Shift + Enter).
Jika ada dua nilai yang persis sama, Anda bisa menambahkan sedikit kombinasi fungsi COUNTIFS di ujung rumus utama untuk memecah nilai kembar secara otomatis berdasarkan urutan kemunculan baris data.
