Rumus Excel Penjumlahan dengan Kriteria

Rumus Excel Penjumlahan dengan Kriteria
Excel menyediakan beberapa fungsi yang bisa menghitung dengan kriteria, apakah kriteria tersebut 1 atau lebih dari 1.

Misal ada data seperti gambar dibawah, data yang menampilkan ketersediaan mobil bekas disebuah showroom berdasarkan nama, merek dan typenya. Bagaimanakah rumus excel untuk menjumlahkan data mobil bekas dengan berbagai macam kombinasi kriteria.

Rumus Excel Penjumlahan dengan Kriteria

Rumus excel penjumlahan dengan 1 kriteria

Berapakah total mobil merek “Toyota” yang tersedia?

Ada 1 kriteria dipertanyaan diatas yaitu merek mobil dengan nama “Toyota”. Fungsi excel yang digunakan untuk menjumlahkan data dengan 1 kriteria adalah fungsi SUMIF.

  • Letakkan kursor dicell F4
  • Ketik formula untuk fungsi SUMIF
  • Parameter range diisi dengan range B2:B21, range yang berisi data merek mobil. Range inilah yang akan dievaluasi dengan kriteria yang dimasukkan diparameter criteria.
  • Parameter criteria diisi dengan “TOYOTA”, kriteria yang ditentukan oleh pertanyaan diatas.
  • Parameter sum_range diisi dengan range D2:D1, range yang berisi jumlah mobil yang tersedia.

Jadi jika data diparameter range sesuai dengan data diparameter criteria maka data yang dijumlahkan adalah data yang ada diparameter sum_range.

Berikut adalah hasil akhir formula yang sudah diketik dengan lengkap.

=SUMIF(B2:B21,”toyota”,D2:D21)

Hasilnya seperti gambar dibawah.

Rumus Excel Penjumlahan dengan Kriteria

Total mobil bekas yang tersedia dengan merek “Toyota” adalah 16 buah.

Untuk mempelajari lebih detail tentang fungsi SUMIF bisa dilihat dialamat berikut:

Rumus excel penjumlahan dengan 2 kriteria atau lebih

Berapakah total mobil yang tersedia dengan merek “Honda” dan type “Sedan”?

Ada 2 kriteria dipertanyaan diatas yaitu mobil dengan merek “Honda” dan mobil dengan type “Sedan”. Fungsi excel yang digunakan untuk menjumlahkan data dengan 2 kriteria atau lebih adalah fungsi SUMIFS.

Ada 2 hal yang harus diingat ketika menggunaka fungsi SUMIFS.

Pertama, fungsi ini tersedia mulai Excel 2007, jika mempunyai excel dengan versi lebih lama dari excel 2007 maka fungsi ini tidak bisa digunakan.

Kedua, semua kriteria yang dimasukkan didalam fungsi SUMIFS harus terpenuhi semua baru angka yang diinginkan dijumlahkan. Fungsi SUMIFS tidak bisa menyelesaikan pertanyaan “Berapakah total mobil dengan merek “Toyota” dan “Honda”. Pertanyaan tersebut mempunyai 2 kriteria tetapi tidak mungkin satu mobil mempunyai 2 merek sekaligus. Jika menggunakan fungsi SUMIFS bisa dipastikan hasilnya adalah 0.

Untuk lebih jelasnya bisa dibaca artikel berikut:

  • Letakkan kursor dicell F4
  • Ketik formula untuk fungsi SUMIFS
  • Parameter sum_range diisi dengan range D2:D21, range yang berisi jumlah mobil yang tersedia.
  • Parameter criteria_range1 diisi dengan range B2:B21, range yang berisi data merek mobil. Range inilah yang akan dievaluasi dengan kriteria pertama yang akan dimasukkan diparameter criteria1
  • Parameter criteria1 diisi dengan “HONDA”, kriteria pertama yang ditentukan oleh pertanyaan diatas.
  • Parameter criteria_range2 diisi dengan range C2:C21, range yang berisi data type mobil. Range inilah yang akan dievaluasi dengan kriteria kedua yang akan dimasukkan diparameter criteria2
  • Parameter criteria2 diisi dengan “SEDAN”, kriteria kedua yang ditentukan oleh pertanyaan diatas.

Ada sedikit perbedaan antara fungsi SUMIF dan fungsi SUMIFS. Fungsi SUMIF parameter sum_range ada diposisi terakhir, sedangkan difungsi SUMIFS ada diposisi pertama.

Berikut adalah hasil akhir formula yang sudah diketik dengan lengkap.

=SUMIFS(D2:D21,B2:B21,”HONDA”,C2:C21,”SEDAN”)

Hasilnya seperti gambar dibawah.

Rumus Excel Penjumlahan dengan Kriteria

Total mobil “Sedan” bekas yang tersedia dengan merek “Honda” adalah 5 buah.

Rumus excel penjumlahan dengan 2 kriteria atau lebih (alternatif lain)

Dengan pertanyaan yang sama dengan sebelumnya, bagaimana solusi rumus excel bagi yang tidak mempunyai Excel 2007 atau yang lebih baru.

Solusinya adalah menggunakan fungsi SUMIF dan kolom bantuan. Kolom bantuan digunakan untuk mengevaluasi 2 kriteria yang telah ditentukan. Hasilnya adalah TRUE/FALSE, hasil tersebut akan digunakan oleh fungsi SUMIF, data yang dijumlahkan adalah data yang mempunyai nilai TRUE.

Untuk mengevaluasi 2 kriteria yang harus terpenuhi semua bisa menggunakan fungsi logika AND.

Untuk lebih jelas tentang fungsi logika AND dan fungsi logika lainnya bisa dibaca dilink berikut:

Kolom bantuan diletakkan dikolom E.

  • Letakkan kursor dicell E2
  • Ketik formula untuk fungsi AND
  • Parameter logical1 diisi dengan B2=”HONDA”, artinya rumus ini akan mencek apakah cell B2 mempunyai data mobil dengan merek “HONDA”
  • Parameter logical2 diisi dengan C2=”SEDAN”, artinya rumus ini akan mencek apalah cekk C2 mempunyai data mobil dengan type “SEDAN”

Karena menggunakan fungsi AND maka semua data diparameter logical harus bernilai TRUE supaya hasil fungsi AND menjadi TRUE.

Berikut adalah hasil akhir formula yang sudah diketik dengan lengkap.

=AND(B2=”HONDA”,C2=”SEDAN”)

Untuk hasil dibaris dibawahnya, kopi cell E2 kemudian paste dirange E3:E21. Hasil dikolom bantuan itulah yang akan digunakan oleh fungsi SUMIF.

  • Letakkan kursor dicell F5
  • Ketik formula untuk fungsi SUMIF
  • Parameter range diisi dengan range E2:E21, range dengan data kolom bantuan yang sudah dibuat sebelumnya.
  • Parameter criteria diisi dengan TRUE, artinya hanya menjumlahkan data yang semua kriteria dikolom bantuan terpenuhi.
  • Parameter sum_range diisi dengan range D2:D21

Berikut adalah hasil akhir formula yang sudah diketik dengan lengkap.

=SUMIF(E2:E21,TRUE,D2:D21)

Hasilnya seperti gambar dibawah.

Rumus Excel Penjumlahan dengan Kriteria

Ada 5 buah mobil “Sedan” bekas dengan merek “Honda” 5, sama seperti solusi menggunakan fungsi SUMIFS.

Rumus excel penjumlahan dengan 2 kriteria atau lebih yang tidak harus terpenuhi semua

Berapakah jumlah mobil dengan merek “Toyota” dan “Honda”?

Ada 2 kriteria, kriteria pertama mobil dengan merek “Toyota”, kriteria kedua mobil dengan merek “Honda”. Apakah pertanyaan ini bisa diselesaikan dengan fungsi SUMIFS?.

Gambar dibawah jawabannya.

Rumus Excel Penjumlahan dengan Kriteria

Kenapa fungsi SUMIFS tidak bisa digunakan untuk menjawab pertanyaan diatas?. Fungsi SUMIFS memang digunakan untuk menjumlahkan data dengan kriteria lebih dari 1. Seperti yang sudah dijelaskan sebelumnya, semua kriteria difungsi SUMIFS harus terpenuhi semua sebagai syarat data bisa dijumlahkan.

Mungkinkah sebuah mobil mempunyai 2 merek sekaligus? mempunyai merek “Toyota” dan sekaligus mempunyai merek “Honda”?. Itulah kenapa hasil fungsi SUMIFS adalah 0.

Pertanyaan diatas walaupun menggunakan kata penghubung “DAN” tetapi pada dasarnya adalah “ATAU”. Jadi pertanyaannya adalah berapakah jumlah mobil yang mempunyai merek “Toyota” atau “Honda”?.

Pertanyaan ini tidak bisa diselesaikan dengan fungsi SUMIFS. Pertanyaan ini bisa diselesaikan dengan menambah kolom bantuan. Kolom bantuan bertugas untuk mengevaluasi merek mobil apakah “Toyota” atau “Honda” menggunakan fungsi logika OR.

Pertanyaan apa lagi yang tidak bisa diselesaikan dengan fungsi SUMIF, bisa dilihat dilink berikut:

  • Letakkan kursor dicell E2
  • Ketik formula untuk fungsi OR
  • Parameter logical1 diisi dengan B2=”toyota”, rumus ini akan mencek apakah data dicell B2 sama dengan Toyota.
  • Parameter logical1 diisi dengan B2=”honda”, rumus ini akan mencek apakah data dicell B2 sama dengan Honda.

Penggunaan fungsi OR tidak mensyaratkan semua parameter bernilai TRUE. Salah satu saja parameter bernilai TRUE maka fungsi OR akan menghasilkan nilai TRUE

Berikut adalah hasil akhir formula yang sudah diketik dengan lengkap.

=OR(B2=”toyota”,B2=”honda”)

Untuk hasil dibaris dibawahnya, kopi cell E2 kemudian paste dirange E3:E21. Hasil dikolom bantuan itulah yang akan digunakan oleh fungsi SUMIF/SUMIFS.

  • Letakkan kursor dicell F5
  • Ketik formula untuk fungsi SUMIFS
  • Parameter sum_range diisi dengan range D2:D21, range yang berisi jumlah mobil yang tersedia.
  • Parameter criteria_range1 diisi dengan range E2:E21, range yang berisi data TRUE/FALSE dikolom bantuan
  • Parameter criteria1 diisi dengan true, artinya hanya menjumlahkan untuk data dikolom bantuan sama dengan true

Berikut adalah hasil akhir formula yang sudah diketik dengan lengkap.

=SUMIFS(D2:D21,E2:E21,TRUE)

Hasilnya seperti gambar dibawah.

Rumus Excel Penjumlahan dengan Kriteria

Total ada 29 mobil dengan merek Toyota dan Honda.

Rumus excel penjumlahan dengan 2 kriteria atau lebih yang tidak harus terpenuhi semua (alternatif lain)

Berapakah jumlah mobil dengan merek “Toyota” dan “Honda”?

Pertanyaan yang sama dengan sebelumnya. Solusinya sedikit berbeda. Solusi sebelumnya menggunakan fungsi SUMIF/SUMIFS dan kolom bantuan. Bagi yang tidak menginginkan adanya kolom bantuan solusi satu-satunya adalah menggunakan array formula dan fungsi yang digunakan adalah fungsi SUMPRODUCT.

Letakkan kursor dicell F5

Ketik formula berikut:

=SUMPRODUCT(((B2:B21=”Toyota”)+(B2:B21=”Honda”)>0)*D2:D21)

Jika sudah tekan tombol ENTER.

Hasilnya seperti gambar dibawah.

Rumus Excel Penjumlahan dengan Kriteria

Ada 29 mobil, sama seperti solusi sebelumnya. Perbedaannya tidak ada lagi kolom bantuan yang bagi sebagian orang cukup menggangu.

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

Download file latihan