Kelemahan Rumus SUMIFS

kelemahan rumus sumifs
Berikut adalah pertanyaan-pertanyaan yang tidak bisa diselesaikan dengan rumus SUMIFS dan solusi untuk menyelesaikannya.

Pertanyaan #1.
Berapa total qty stok di “Apotek Satu” dan “Apotek Dua”?

Pertanyaan ini mempunyai 2 kriteria. Semua kriteria berada dikolom yang sama, yaitu kolom A yang berisi nama-nama apotek.

fungsi sum

Jika menggunakan rumus SUMIFS akan menghasilkan nilai 0, karena tidak mungkin dalam satu cell yang sama berisi nama “Apotek Satu” dan “Apotek Dua”. Lihat gambar dibawah, rumus SUMIFS menghasilkan nilai 0.

fungsi sumif

Solusi dengan rumus SUMIF, rumus OR dan kolom bantuan

Tujuan kolom bantuan adalah menghasilkan nilai TRUE/FALSE dari kriteria-kriteria yang diberikan. Data TRUE/FALSE kemudian digunakan rumus SUMIF untuk menghitung total stok yang sesuai dengan kriteria.

Kolom bantuan akan diisi dengan rumus OR, kenapa OR?, karena kedua kriteria tidak harus terpenuhi semua. Salah satu saja terpenuhi nilai stok bisa dihitung.

  • Letakkan kursor dicell D2
  • Ketik formula untuk rumus OR
  • Parameter logical1 diisi dengan mencek apakah cell A2 sama dengan “Apotek Satu”
  • Parameter logical2 diisi dengan mencek apakah cell A2 sama dengan “Apotek Dua”

Jika formula sudah diketik dengan lengkap akan menjadi seperti rumus berikut

=OR(A2=”Apotek Satu”,A2=”Apotek Dua”)

Kopi cell D2 kemudian paste dirange D3:D14. Hasilnya seperti gambar dibawah.

fungsi sumifs

Nilai TRUE muncul diapotek dengan nama “Apotek Satu” dan “Apotek Dua”, selain kedua nama tersebut menghasilkan nilai FALSE. Nilai TRUE inilah yang akan digunakan oleh rumus SUMIF untuk menghitung total stok di “Apotek Satu” dan “Apotek Dua”

  • Letakkan kursor dicell C15
  • Ketik formula untuk rumus SUMIF
  • Parameter range diisi dengan range D2:D14 range yang berisi nilai TRUE/FALSE
  • Parameter criteria diisi dengan TRUE
  • Parameter sum_range diisi dengan range C2:C14 range yang berisi informasi stok

Jika formula sudah diketik dengan lengkap akan menjadi seperti rumus berikut

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

Hasilnya adalah 165, total stok “Apotek Satu” dan “Apotek Dua”

array formula

Solusi dengan rumus SUM dan array formula

Letakkan kursor dicell C15. Ketik formula berikut

=SUM(((A2:A14=”Apotek Satu”)+(A2:A14=”Apotek Dua”)>=1)*(C2:C14))

Akhiri dengan menekan tombol CTRL+SHIFT+ENTER secara bersamaan. Hasilnya sama 165.

fungsi or

Pertanyaan #2.
Berapa total pajak mobil yang harganya kurang dari 150juta atau lebih besar dari 200juta?

Pertanyaan ini sama seperti pertanyaan #1. Ada 2 kriteria dikolom yang sama, yaitu kolom B yang berupa angka, sedangkan pertanyaan #1 data dikolom A berupa teks.

Sama seperti pertanyaan #1, jika diselesaikan dengan rumus SUMIFS akan menghasilkan nilai 0. Lihat gambar dibawah.

fungsi and

Solusi dengan rumus SUMIF dan kolom bantuan

Kolom bantuan akan diisi dengan rumus OR yang akan mencek mobil dibaris manakah yang harganya kurang dari 150 juta atau lebih dari 200 juta.

  • Letakkan kursor dicell D2
  • Ketik formula untuk rumus OR
  • Parameter logical1 diisi dengan mencek apakah cell B2 lebih kecil dari 150 juta
  • Parameter logical2 diisi dengan mencek apakah cell B2 lebih besar dari 200 juta

Jika formula sudah diketik dengan lengkap akan menjadi seperti rumus berikut

=OR(B2<150000000,B2>200000000)

Kopi cell D2 kemudian paste dirange D3:D14. Hasilnya seperti gambar dibawah.

sumifs

Selanjutnya menggunakan rumus SUMIF untuk menjumlahkan nilai pajak untuk data-data yang mempunyai nilai TRUE dikolom bantuannya.

  • Letakkan kursor dicell C10
  • Ketik formula untuk rumus SUMIF
  • Parameter range diisi dengan range D2:D8 range yang berisi nilai TRUE/FALSE
  • Parameter criteria diisi dengan TRUE
  • Parameter sum_range diisi dengan range C2:C8 range yang berisi data pajak

Jika formula sudah diketik dengan lengkap akan menjadi seperti rumus berikut

=SUMIF(D2:D8,TRUE,C2:C8)

Hasilnya adalah 13.042.500. Total pajak untuk semua mobil yang harganya kurang dari 150 juta atau lebih dari 200 juta.

rumus excel sumifs

Solusi dengan rumus SUM dan array formula

Letakkan kursor dicell C15. Ketik formula berikut

=SUM(((B2:B8<150000000)+(B2:B8>200000000)>=1)*(C2:C8))

Akhiri dengan menekan tombol CTRL+SHIFT+ENTER secara bersamaan. Hasilnya sama 13.042.500.

cara menggunakan rumus sumifs

Pertanyaan #3.
Berapa total pajak MPV yang harganya kurang dari 150juta atau lebih besar dari 200juta?

Ada 3 kriteria. 1 kriteria dikolom A yaitu mobil MPV, 2 kriteria ada dikolom B yaitu harga mobil kurang dari 150juta atau lebih dari 200juta. Kriteria #2 dan #3 tidak harus  terpenuhi semua, tetapi hasil evaluasi kriteria #2, #3 dan kriteria #1 harus terpenuhi semua.

Solusi dengan rumus SUMIF dan kolom bantuan

Ada 2 rumus logika yang akan digunakan dikolom bantuan. Rumus OR digunakan untuk mengevaluasi kriteria #2 dan #3 yang tidak harus terpenuhi semua. Rumus AND digunakan untuk mengevaluasi hasil rumus OR dan kriteria #1.

Satu rumus tambahan yang akan digunakan adalah rumus SEARCH yang akan digunakan untuk mencari nama mobil yang ada kata MPVnya.

    • Letakkan kursor dicell D2
    • Ketik formula untuk rumus AND
  • Parameter logical1 rumus AND diisi dengan kriteria #1 yang mencek mobil dengan nama MPV. Untuk mencek digunakan rumus SEARCH
  • Parameter find_text rumus SEARCH diisi dengan kata “MPV*”, ada wildcard * dibelakangnya karena akan mencari semua mobil dengan kata depannya MPV.
  • Parameter within_text rumus SEARCH diisi dengan cell C2, cell yang akan dicari apakah ada kata MPVnya atau tidak
  • Ketik tanda “>0” setelah rumus SEARCH untuk mengevaluasi hasil rumus SEARCH. Artinya jika rumus SEARCH menghasilkan angka lebih besar dari 0, kata MPV ada dicell A2
  • Parameter logical2 rumus AND diisi dengan rumus OR
  • Parameter logical1 rumus OR diisi dengan kriteria #2 B2<150000000
  • Parameter logical2 rumus OR diisi dengan kriteria #3 B2>200000000

Jika formula sudah diketik dengan lengkap akan menjadi seperti rumus berikut

=AND(SEARCH(“MPV*”,A2)>0,OR(B2<150000000,B2>200000000))

Kopi cell D2 kemudian paste dirange D3:D8. Hasilnya seperti gambar dibawah.

rumus sumifs

Muncul error #VALUE!, error ini muncul karena rumus SEARCH tidak berhasil menemukan kata MPV, dan ini tidak masalah, karena yang dibutuhkan adalah kolom bantuan dengan nilai TRUE.

Kolom bantuan dengan nilai FALSE adalah mobil MPV tetapi harganya tidak sesuai dengan kriteria #2 dan #3. Kolom bantuan dengan nilai TRUE adalah data yang memenuhi 3 kriteria yang telah ditentukan.

Gunakan rumus SUMIF untuk mengetahui berapa total pajak mobil dengan 3 kriteria diatas.

  • Letakkan kursor dicell C10
  • Ketik formula untuk rumus SUMIF
  • Parameter range diisi dengan range D2:D8
  • Parameter criteria diisi dengan TRUE
  • Parameter sum_range diisi dengan range C2:C8 range yang berisi data pajak

Jika formula sudah diketik dengan lengkap akan menjadi seperti rumus berikut

=SUMIF(D2:D8,TRUE,C2:C8)

Hasilnya adalah 7.818.000. Total pajak untuk semua mobil MPV yang harganya kurang dari 150 juta atau lebih dari 200 juta.

rumus excel sumifs

Solusi dengan rumus SUM dan array formula

Letakkan kursor dicell C15. Ketik formula berikut

=SUM((IFERROR(SEARCH(“MPV*”,A2:A8)>0,0))
*((B2:B8<150000000)+(B2:B8>200000000)>0)
*(C2:C8))

Akhiri dengan menekan tombol CTRL+SHIFT+ENTER secara bersamaan. Hasilnya sama 7.818.000.

kelemahan rumus sumifs

Rumus diatas menggunakan rumus IFERROR yang baru tersedia diexcel 2007, jika tidak mempunyai Excel 2007 atau yang lebih baru bisa menggunakan gabungan rumus IF dan rumus ISERROR

=SUM(IF(ISERROR(SEARCH(“MPV*”,A2:A8)>0),FALSE,SEARCH(“MPV*”,A2:A8)>0)
*((B2:B8<150000000)+(B2:B8>200000000)>0)
*(C2:C8))

rumus sumifs excel

Mana solusi terbaik?

Secara tampilan solusi dengan fungsi SUM dan array formula memberikan tampilan yang lebih bagus, tanpa adanya kolom bantuan semua kelemahan rumus SUMIFS bisa diselesaikan dengan baik. Tetapi tidak semua terbisa menggunakan array formula. Buku-buku excel yang terbit juga jarang membahas tentang array formula.

Solusi menggunakan rumus SUMIF dan kolom bantuan adalah solusi terbaik jika adanya kolom bantuan tidak dipermasalahkan, toh kolom bantuan tersebut bisa juga disembunyikan atau ditaruh diworksheet lain :).

popup_buku_rumus_excel_1
Array formula dibahas dengan detail dibab 12 buku “Rumus Excel”. Apa saja yang dibahas bisa dilihat didaftar isi.

LIHAT DAFTAR ISI

 

Kami juga mempunyai 12 video tutorial tentang array formula dikursus online rumus excel. Daftar video tutorialnya bisa dilihat disini.

Video Tutorial