Rumus SUMIF dengan 2 Kriteria

Rumus SUMIF dengan 2 Kriteria

oleh

Rumus SUMIF dibuat dengan hanya ada 1 kriteria yang bisa digunakan, tetapi Excel yang lebih baru mempunyai rumus yang bisa menggunakan beberapa kriteria yaitu rumus SUMIFS.

Artikel ini akan membahas solusi menggunakan rumus SUMIFS atau tanpa rumus SUMIFS. Sebagai catatan tambahan, walaupun rumus SUMIFS bisa digunakan untuk menghitung nilai dengan beberapa kriteria, tetap saja ada kelemahannya.

Misal ada data penjualan obat dan pertanyaannya adalah:

“Berapa jumlah WRP Diet di Apotek Dua?”

Rumus SUMIF tidak bisa digunakan secara langsung untuk menjawab pertanyaan tersebut. Ada 3 solusi yang bisa digunakan, salah satunya menggunakan rumus SUMIF dengan kolom bantuan.

Solusi #1, Menggunakan Rumus SUMIFS

Ini adalah solusi paling mudah tanpa harus menambahkan kolom bantuan. Masalahnya hanya satu, rumus SUMIFS baru tersedia di Excel 2007 atau yang lebih baru.

  1. Letakkan kursor di sel C15.
  2. Ketik formula SUMIFS.
  3. Parameter sum_range diisi dengan range C2:C14.
  4. Parameter criteria_range1 diisi dengan range A2:A14.
  5. Parameter criteria1 diisi dengan “APOTEK DUA”.
  6. Parameter criteria_range2 diisi dengan range B2:B14.
  7. Parameter criteria2 diisi dengan “wrp diet”.

Rumusnya:

=SUMIFS(C2:C14,A2:A14,"APOTEK DUA",B2:B14,"wrp diet")

Tekan tombol Enter, hasilnya adalah 12.

Solusi #2, Menggunakan Rumus SUMIF, Rumus Logika AND dan Kolom Bantuan

Rumus SUMIF tetap bisa digunakan dengan 2 kriteria atau lebih, tetapi harus dibantu dengan kolom bantuan. Isi kolom bantuan berupa rumus logika yang mengevaluasi kriteria-kriteria yang ditentukan.

Untuk contoh di atas terdapat 2 kriteria:

  • Nama apotek = “APOTEK DUA”
  • Nama produk = “WRP Diet”

Karena kedua kriteria harus terpenuhi secara bersamaan, maka digunakan rumus AND.

Kolom bantuan diletakkan di kolom D. Hasilnya berupa nilai TRUE atau FALSE yang akan digunakan oleh rumus SUMIF.

Letakkan kursor di sel D2 lalu ketik:

=AND(A2="APOTEK DUA",B2="wrp diet")

Salin rumus tersebut ke range D3:D14.

Langkah berikutnya adalah menggunakan data pada kolom bantuan untuk rumus SUMIF.

  1. Letakkan kursor di sel C15.
  2. Ketik formula SUMIF.
  3. Parameter range diisi dengan D2:D14.
  4. Parameter criteria diisi dengan TRUE.
  5. Parameter sum_range diisi dengan C2:C14.

Rumusnya:

=SUMIF(D2:D14,TRUE,C2:C14)

Tekan tombol Enter, hasilnya adalah 12.

Solusi #3, Menggunakan Rumus SUM dan Array Formula

Solusi ini mengatasi keterbatasan solusi pertama yang hanya tersedia pada Excel 2007 ke atas, serta menghilangkan kebutuhan kolom bantuan seperti pada solusi kedua.

Metode ini dapat digunakan pada Excel versi lama dan tidak memerlukan kolom tambahan.

Letakkan kursor di sel C15 lalu ketik rumus berikut:

=SUM((A2:A14="APOTEK DUA")*(B2:B14="wrp diet")*(C2:C14))

Setelah selesai, tekan Ctrl + Shift + Enter secara bersamaan.

Perhatikan bahwa setelah berhasil, Excel akan menampilkan tanda kurung kurawal {} pada formula. Tanda tersebut tidak diketik secara manual, melainkan muncul karena formula dimasukkan sebagai Array Formula.

Kelemahan Rumus SUMIFS

Walaupun fungsi SUMIFS dapat menyelesaikan perhitungan dengan beberapa kriteria, tidak semua kasus dapat ditangani dengan mudah.

Sebagai contoh:

  • Pertanyaan “Berapa total qty barang Apotek Satu dan Apotek Dua?” tidak dapat diselesaikan langsung dengan satu fungsi SUMIFS.
  • Memang bisa menggunakan dua fungsi SUMIF yang dijumlahkan, tetapi cara tersebut masih memiliki keterbatasan.
  • Pertanyaan “Berapa total qty barang Apotek Satu dan total qty WRP Diet?” juga tidak dapat diselesaikan hanya dengan menggabungkan beberapa SUMIF.

Untuk kasus-kasus seperti itu, Array Formula menjadi solusi yang lebih fleksibel tanpa harus menggunakan kolom bantuan.

Mana Solusi Terbaik?

Jika menggunakan Excel 2007 atau versi yang lebih baru, maka Solusi #1 (SUMIFS) adalah pilihan terbaik karena paling mudah digunakan.

Jika menggunakan Excel versi lama, maka Solusi #3 (Array Formula) merupakan pilihan terbaik karena tidak memerlukan kolom bantuan dan lebih fleksibel dalam menangani berbagai kondisi perhitungan.

Tinggalkan komentar