Menghitung berdasarkan kriteria bukan hal yang baru di Excel. Excel sudah menyediakan rumus untuk menghitung berdasarkan kriteria dengan rumus COUNTIF. Tetapi rumus ini akan mudah digunakan jika kriteria sudah ada pada table data.
Jika data yang kamu miliki hanya berupa tanggal, untuk menghitung data berdasarkan bulan diperlukan sedikit trik agar rumus bisa bekerja sesuai dengan keinginan.
Pada kesempatan kali ini Excelnoob akan memberikan trik menjumlahkan data berdasarkan bulan dengan 2 rumus yang berbeda. Cara yang pertama menggunakan rumus SUMPRODUCT dan yang kedua menggunaka rumus COUNTIF. Tetapi sebelum kita bahas lebih lanjut, kita siapkan terlebih dulu data yang akan dijadikan contoh untuk menghitung data berdasarkan kriteria.
Untuk contoh data, pada gambar dibawah ini, kita akan menghitung berapa jumlah nama yang berulang tahun pada bulan tertentu. untuk contoh datanya bisa dilihat dibawah ini.
Ada yang harus kamu perhatikan yaitu isi yang ada di E3, pada cell ini diisi dengan tanggal 1/1/2020 kemudian lakukan Format Cell dengan format custom “mmmm” untuk mendapatkan Text bulan. Hal ini penting dilakukan agar nantinya mudah saat menggunakan Rumus. Jika yang tampil adalah bulan dalam Bahasa Inggris bisa tambahkan LCID menjadi “[$-421]mmmm” atau “[$-id-ID]mmmm” . Untuk penjelasan lebih lengkap tentang LCID bisa dibaca di Artikel tentang Languange Code Indentifier (LCID)
Jadi pada Cell E3 ini bukan sebuah text yang ditulis dengan manual ya.
Menghitung Berdasarkan Bulan Dengan SUMPRODUCT
Rumus yang pertama yang bisa digunakan untuk menghitung jumlah nama yang berulang tahun pada bulan tertentu bisa menggunakan SUMPRODUCT. Rumus ini menjadi pilihan pertama karena tidak dibutuhkan kolom bantu dalam menghitung data tersebut. Sehingga tidak perlu mengubah susunan atau design Table.
Untuk menghitung dengan SUMPRODUCT, rumusnya bisa ditulis seperti ini.
=SUMPRODUCT(--(MONTH(C3:C11)=MONTH(E3)))
Contoh penerapannya bisa dilihat pada gambar dibawah ini
Tanda Minus double (–) pada rumus diatas adalah Double Unary, untuk penjelasanya bisa dibaca di artikel Double Unary.
Menghitung Berdasarkan Bulan dengan COUNTIF
Untuk cara yang kedua menggunakan rumus yang sudah umum digunakan untuk menghitung data berdasarkan kriteria yaitu rumus COUNTIF. Perbedaan rumus ini dibanding dengan rumus SUMPRODUCT adalah diperlukan kolom bantu untuk mendapatkan bulannya. Karena pada data diatas setiap tanggal memiliki Tahun yang berbeda-beda.
Jika pada data tanggal memiliki tahun yang sama, bisa dihitung tanpa perlu menambahkan kolom bantu hanya perlu mengganti COUNTIF dengan COUNTIFS dan menambahkah kriteria “>=” dan <=” pada rumus. untuk contoh bisa dlihat pada gambar dibawah ini.
Tapi karena pada kasus kali ini menghitung tanggal yang memiliki tahun yang berbeda, diperlukan sebuah kolom bantu untuk memudahkan proses perhitungan. Pada kolom bantu tambahkan rumus Month(C3) dan Drag ke bawah. Hasilnya akan terlihat seperti dibawah ini.
Jika sudah memiliki kolom bulan seperti diatas, untuk menghitung berdasarkan bulan menjadi lebih mudah, untuk rumus COUNTIF bisa ditulis seperti dibawah ini.
=COUNTIF(D3:D11,MONTH(F3))
Untuk contoh pengunaannya bisa dilihat pada gambar dibawah ini.
itulah beberapa rumus yang bisa digunakan untuk menghitung jumlah data berdasarkan bulan. Alternatif lainnya bisa menggunakan Pivot untuk menampilkan data seperti diatas dengan lebih mudah.
Leave a Reply