Berikut adalah beberapa contoh kasus yang tidak dapat diselesaikan secara langsung menggunakan rumus SUMIFS serta solusi alternatif yang dapat digunakan.
Pertanyaan #1: Berapa Total Qty Stok di Apotek Satu dan Apotek Dua?
Pertanyaan ini mempunyai dua kriteria yang berada pada kolom yang sama, yaitu kolom A yang berisi nama apotek.
Jika menggunakan rumus SUMIFS, hasil yang diperoleh adalah 0 karena tidak mungkin sebuah sel pada kolom A berisi nama “Apotek Satu” dan “Apotek Dua” secara bersamaan.
Solusi dengan Rumus SUMIF, OR dan Kolom Bantuan
Kolom bantuan digunakan untuk menghasilkan nilai TRUE atau FALSE berdasarkan kriteria yang ditentukan. Nilai tersebut kemudian digunakan oleh rumus SUMIF untuk menghitung total stok.
Masukkan rumus berikut pada kolom bantuan:
=OR(A2="Apotek Satu",A2="Apotek Dua")
Salin rumus tersebut ke seluruh baris data.
Setelah itu gunakan rumus SUMIF berikut untuk menjumlahkan stok:
=SUMIF(D2:D14,TRUE,C2:C14)
Hasilnya adalah 165, yaitu total stok dari Apotek Satu dan Apotek Dua.
Solusi dengan Rumus SUM dan Array Formula
Alternatif lain adalah menggunakan array formula berikut:
=SUM(((A2:A14="Apotek Satu")+(A2:A14="Apotek Dua")>=1)*(C2:C14))
Setelah mengetik rumus, tekan CTRL+SHIFT+ENTER. Hasil yang diperoleh tetap 165.
Pertanyaan #2: Berapa Total Pajak Mobil yang Harganya Kurang dari 150 Juta atau Lebih dari 200 Juta?
Pada kasus ini terdapat dua kriteria yang berada pada kolom harga yang sama.
Jika menggunakan SUMIFS secara langsung, hasilnya adalah 0 karena kedua kriteria tersebut tidak mungkin terpenuhi secara bersamaan.
Solusi dengan Rumus SUMIF dan Kolom Bantuan
Gunakan rumus OR pada kolom bantuan:
=OR(B2<150000000,B2>200000000)
Salin rumus ke seluruh baris data.
Setelah itu gunakan rumus SUMIF berikut:
=SUMIF(D2:D8,TRUE,C2:C8)
Hasilnya adalah 13.042.500, yaitu total pajak untuk mobil yang harganya kurang dari 150 juta atau lebih dari 200 juta.
Solusi dengan Rumus SUM dan Array Formula
=SUM(((B2:B8<150000000)+(B2:B8>200000000)>=1)*(C2:C8))
Akhiri dengan menekan CTRL+SHIFT+ENTER. Hasil yang diperoleh tetap sama yaitu 13.042.500.
Pertanyaan #3: Berapa Total Pajak Mobil MPV yang Harganya Kurang dari 150 Juta atau Lebih dari 200 Juta?
Pada kasus ini terdapat tiga kriteria:
- Mobil harus termasuk kategori MPV.
- Harga kurang dari 150 juta.
- Atau harga lebih dari 200 juta.
Kriteria harga menggunakan logika OR, sedangkan hasilnya digabungkan dengan kriteria MPV menggunakan logika AND.
Solusi dengan Rumus SUMIF dan Kolom Bantuan
Gunakan rumus berikut pada kolom bantuan:
=AND(SEARCH("MPV*",A2)>0,OR(B2<150000000,B2>200000000))
Salin rumus ke seluruh data.
Setelah itu gunakan rumus SUMIF berikut:
=SUMIF(D2:D8,TRUE,C2:C8)
Hasilnya adalah 7.818.000, yaitu total pajak untuk semua mobil MPV yang memenuhi kriteria harga.
Solusi dengan Rumus SUM dan Array Formula
=SUM((IFERROR(SEARCH("MPV*",A2:A8)>0,0))*((B2:B8<150000000)+(B2:B8>200000000)>0)*(C2:C8))
Akhiri dengan menekan CTRL+SHIFT+ENTER.
Jika menggunakan Excel versi lama yang belum mendukung IFERROR, gunakan rumus berikut:
=SUM(IF(ISERROR(SEARCH("MPV*",A2:A8)>0),FALSE,SEARCH("MPV*",A2:A8)>0)*((B2:B8<150000000)+(B2:B8>200000000)>0)*(C2:C8))
Mana Solusi Terbaik?
Array formula memberikan tampilan yang lebih rapi karena tidak membutuhkan kolom bantuan. Namun, tidak semua pengguna Excel memahami cara kerja array formula sehingga penggunaannya terkadang kurang praktis.
Di sisi lain, penggunaan rumus SUMIF dengan kolom bantuan lebih mudah dipahami dan dikelola. Jika keberadaan kolom bantuan tidak menjadi masalah, metode ini sering menjadi pilihan terbaik karena lebih mudah dipelajari dan dipelihara.