Rumus Excel Untuk Menjumlahkan Angka Berdasarkan Hari Dalam Sebuah Tabel
Menghitung Dengan Kriteri Hari Dalam Excel - Dalam Excel kita bisa menghitung atau menjumlahkan angka dengan kriteria tertentu.
Kriteria yang digunakan untuk penghitungan tersebut bisa dalam bentuk teks, angka, operator hitung dan lainnya.
Rumus untuk menjumlahkan range atau angka dengan kriteria dalam Excel adalah SUMIF atau SUMIFS.
Perbedaan rumus SUMIF dan SUMIFS terletak pada jumlah darikriteria yang terdapat dalam pejumlahan tersebut.
Penggunakan kriteria teks dalam penjumlahan sangat beragam bentuknya dan tentu saja itu disesuaikan dengan kondisi dalam pengolahan data yang sedang kita lakukan.
Bahkan kriteria teks tersebut bisa kita gunakan untuk menjumlahkan sebuah range berdasakan kriteria hari, minggu ke-n, bulan dan tahun yang diambil dari sebuah range tanggal.
Dalam artikel ini saya akan membahas tentang cara menjumlahkan angka berdasarkan kriteria hari dengan rumus Excel.
Jika kita memiliki data dalam sebuah tabel yang salah satu kolomnya berisi tanggal maka otomatis kita akan mengisi tanggal tersebut dengan urutan secara lengkap ( tanggal, bulan dan tahun ).
Masalah yang muncul adalah bagaimana jika kita ingin menjumlahkna kolom lain berdasarkan kriteria hari yang terdapat dalam kolom tanggal tersebut.
Tentu untuk kita yang sering menggunakan rumus Excel akan otomatis teringat atau ingin menggunakan rumus SUMIF.
Untuk lebih jelasnya sebagai contoh data yang ingin dijumlahkan adalah sebagai berikut :
Dalam gambar tersebut kolom tanggal tidak muncul nama hari sehingga kita tidak langsung menggunakan rumus SUMIF untuk menjumlahkan kolom jumlah dengan kriteria hari.
Untuk menghitung atau menjumlahkan kolom C dengan kriteria hari saya akan memberikan dengan dua cara yaitu menambahkan kolom Hari dengan rumus Excel dan menjumlahkan langsung dengan memberikan format yang memunculkan hari kedalam kolom Tanggal.
Tapi sebelum kita melihat cara penyelesaiannya saya akan menjelaskan sedikit tentang rumus - rumus yang akan digunakan.
Adapaun rumus yang akan digunakan untuk menghitung kolom C berdasarkan kriteria hari adalah sebagai berikut :
Setelah mengetahui bentuk data yang akan dihitung dan rumus yang digunakan selanjutnya mari kita membahas cara menghitungnya satu persatu berikut ini :
Untuk cara yang pertama saya akan memisahkan kriteria yang berbentuk hari kedalam kolom tersendiri tanpa mengubah format kolom Tanggal.
Setelah dibuat kolom Hari maka selanjutnya kita langsung menggunakan rumus SUMIF karena kriteria hari dalam tabel sudah dimunculkan.
Tentu saja pemisahan hari dari kolom tanggal ini dilakukan dengan rumus sehingga akan berjalan dengan otomatis.
Silahkan perhatikan gambar berikut ini :
Dalam gambar diatas jumlah hari Senin dan Selasa sudah bisa dibuat dengan cara mengisi kolom C dengan hari yang diambil dari kolom Tanggal.
Adapun rumus untuk memisahkan hari dari kolom Tanggal adalah sebagai berikut :
=TEXT(B5;"dddd")
Dengan rumus tersebut diatas maka nama hari akan otomatis muncul dalam kolom C sesuai dengan tanggal yang dimasukan kedalam kolom B.
Jika belum mengetahui secara rinci syntax dan juga argumen dari rumus TEXT silahkan pelajari dalam artikel yang telah dibahas sebelumnya.
Setelah kolom C terisi dengan nama hari sesuai dengan tanggal dalam kolom B maka selanjutnya kita gunakan rumus SUMIF berdasarkan kriteria hari dalam kolom C tersebut.
Adapun rumus SUMIF yang digunakan adalah sebagai berikut :
Cell G5 : =SUMIF($C$5:$C$9;F5;$D$5:$D$9)
Cell G6 : =SUMIF($C$5:$C$9;F6;$D$5:$D$9)
Dengan kedua rumus tersebut maka jumlah dari kolom D akan dipisahkan berdasarkan hari.
Cara pertama ini memang tidak sulit hanya saja sedikit repot karena harus memisahkan hari yang belum tentu juga dibutuhkan dalam tampilan tabel tersebut.
Jika tabel tersebut akan dicetak dan kita tidak membutuhkan tampilan hari tersebut silahkan dihide dan dalam hasil cetakan kolom hari tidak akan muncu.
Cara yang kedua memang sangat berbeda dengan cara yang pertama karena dalam cara yang kedua kita akan langsung menjumlahkan kolom Tanggal dengan kolom Jumlah berdasarkan kriteria Hari yang kita masukan kedalam cell E4 dan E5.
Kesulitan dari cara yang kedua ini adalah kita harus mengubah format kolom Tanggal dalm cell E4/E5 melalui custom number format.
Hasil dari cara yang kedua ini akan terlihat seperti dalam gambar berikut ini :
Langkah yang pertama sebelum menjumlahkan kolom jumlah dengan kriteria hari kita harus menyesuaikan format dengan cara sebagai berikut :
- Klik/blok range B4:B8
- Klik kanan dalam area yang dipilih
- Klik Format Cells
- Klik Tab Number
- Klik sub menu Custom
- Dalam kotak Type masukan : [$-421]dddd,dd-mmm-yy
- Klik Ok
Setelah melakukan 7 langkah diatas maka tanggal yang dimasukan kedalam kolom B akan berubah seperti pada gambar diatas.
Perubahan yang saya maksud adalah dengan munculnya hari dari tanggal yang kita masukan dalam kolom tersebut.
Selanjutnya untuk cell E4:E5 lakukan hal yang sama seperti langkah yang diatas hanya saja pada kolom Type masukan format sebagai berikut : [$-421]dddd.
Dalam artikel tersebut disertakan gambar untuk memasukan format kedalam sub menu custom number.
Hal yang paling penting untuk mengisi kolom E4 dan E5 adalah jangan mengetikan hari secara manual.
Untuk memunculkan hari Senin dalam cell E4 ketik angka 2 dan otomatis akan muncul hari senin.
Untuk cell E5 ketik angka 3 dan akan otomatis muncul hari Selasa seperti terlihat dalam gambar diatas.
Jika mmebutuhkan hari lainnya silahkan masukan angka selanjutnya misalnya 4 untuk rabu, 5 untuk Kamis dan seterusnya.
Setelah mengubah format dari kedua kolom tersebut diatas maka selanjutnya kita akan menggabungkan rumus SUMPRODUCT dengan WEEKDAY untuk menambahkan kolom Jumlah berdasarkan hari.
Rumus yang digunakan untuk menambahkan kolom Jumlah dalam cell F5 dan F6 adalah sebabagi berikut :
Cell F5 : =SUMPRODUCT((WEEKDAY($B$4:$B$8)=E4)*$C$4:$C$8)
Cell F6 : =SUMPRODUCT((WEEKDAY($B$4:$B$8)=E5)*$C$4:$C$8)
Rumus WEEKDAY dalam gabungan rumus diatas akan mengambil angka dari hari yang telah kita masukan kedalam kolom Tanggal.
Konversi angka dari hari ini akan disesuaikan dengan syarat yang adal dalam cell E4 dan E5 kemudian akan mengambil dan menjumlahkan kolom Jumlah sesuai dengan kriteria angka yang mewakili hari tersebut.
Jika semua langkah dalam cara yang kedua ini berhasil dilakukan maka hasilnya akan terlihat seperti dalam gambar diatas.
Untuk melengkap pembelajaran berikut saya lampirkan contoh file Excelnya dan silahkan dipelajari melalui link berikut ini :
--------------------------------------------------
Nama file : Contoh penjumlahan berdasarkan hari
Ukuran file : 21 kb
Link : Contoh file
--------------------------------------------------
itulah pembahasan kita kali ini tentang cara menjumlahkan angka berdasarkan kriteria hari dalam Excel, semoga artikel ini bermanfaat untuk semua pembaca.
Cara Menjumlahkan Angka Denga Kriteria Nama Hari Dalam Excel
Jika kita memiliki data dalam sebuah tabel yang salah satu kolomnya berisi tanggal maka otomatis kita akan mengisi tanggal tersebut dengan urutan secara lengkap ( tanggal, bulan dan tahun ).
Masalah yang muncul adalah bagaimana jika kita ingin menjumlahkna kolom lain berdasarkan kriteria hari yang terdapat dalam kolom tanggal tersebut.
Tentu untuk kita yang sering menggunakan rumus Excel akan otomatis teringat atau ingin menggunakan rumus SUMIF.
Untuk lebih jelasnya sebagai contoh data yang ingin dijumlahkan adalah sebagai berikut :
Dalam gambar tersebut kolom tanggal tidak muncul nama hari sehingga kita tidak langsung menggunakan rumus SUMIF untuk menjumlahkan kolom jumlah dengan kriteria hari.
Untuk menghitung atau menjumlahkan kolom C dengan kriteria hari saya akan memberikan dengan dua cara yaitu menambahkan kolom Hari dengan rumus Excel dan menjumlahkan langsung dengan memberikan format yang memunculkan hari kedalam kolom Tanggal.
Tapi sebelum kita melihat cara penyelesaiannya saya akan menjelaskan sedikit tentang rumus - rumus yang akan digunakan.
Adapaun rumus yang akan digunakan untuk menghitung kolom C berdasarkan kriteria hari adalah sebagai berikut :
- Rumus SUMIF Excel : rumus ini akan saya gunakan untuk menjumlahkan kolom C atau kolom jumlah berdasarkan kriteria hari dari kolom E.
- Rumus TEXT Excel : rumus TEXT akan saya gunakan untuk mengambil format hari dari tanggal yang terdapat dalam kolom Tanggal.
- Rumus EUMPRODUCT Excel : rumus SUMPRODUCT akan digunakan untuk menjumlahkan kolom Tanggal dengan kolom Jumlah berdasarkan kriteria hari setelah kolom Tanggal diberikan format Custom Date.
- Rumus WEEKDAY Excel : rumus ini akan digabungkan dengan rumus SUMPRODUCT untuk menjumlahkan kolom C.
Setelah mengetahui bentuk data yang akan dihitung dan rumus yang digunakan selanjutnya mari kita membahas cara menghitungnya satu persatu berikut ini :
1. Menjumlahkan Angka Dengan Memisahkan Kolom Kriteria Hari
Untuk cara yang pertama saya akan memisahkan kriteria yang berbentuk hari kedalam kolom tersendiri tanpa mengubah format kolom Tanggal.
Setelah dibuat kolom Hari maka selanjutnya kita langsung menggunakan rumus SUMIF karena kriteria hari dalam tabel sudah dimunculkan.
Tentu saja pemisahan hari dari kolom tanggal ini dilakukan dengan rumus sehingga akan berjalan dengan otomatis.
Silahkan perhatikan gambar berikut ini :
Dalam gambar diatas jumlah hari Senin dan Selasa sudah bisa dibuat dengan cara mengisi kolom C dengan hari yang diambil dari kolom Tanggal.
Adapun rumus untuk memisahkan hari dari kolom Tanggal adalah sebagai berikut :
=TEXT(B5;"dddd")
Dengan rumus tersebut diatas maka nama hari akan otomatis muncul dalam kolom C sesuai dengan tanggal yang dimasukan kedalam kolom B.
Jika belum mengetahui secara rinci syntax dan juga argumen dari rumus TEXT silahkan pelajari dalam artikel yang telah dibahas sebelumnya.
Setelah kolom C terisi dengan nama hari sesuai dengan tanggal dalam kolom B maka selanjutnya kita gunakan rumus SUMIF berdasarkan kriteria hari dalam kolom C tersebut.
Adapun rumus SUMIF yang digunakan adalah sebagai berikut :
Cell G5 : =SUMIF($C$5:$C$9;F5;$D$5:$D$9)
Cell G6 : =SUMIF($C$5:$C$9;F6;$D$5:$D$9)
Dengan kedua rumus tersebut maka jumlah dari kolom D akan dipisahkan berdasarkan hari.
Cara pertama ini memang tidak sulit hanya saja sedikit repot karena harus memisahkan hari yang belum tentu juga dibutuhkan dalam tampilan tabel tersebut.
Jika tabel tersebut akan dicetak dan kita tidak membutuhkan tampilan hari tersebut silahkan dihide dan dalam hasil cetakan kolom hari tidak akan muncu.
2. Menjumlahkan Dengan Kriteria Hari Menggunakan Rumus SUMPRODUCT
Cara yang kedua memang sangat berbeda dengan cara yang pertama karena dalam cara yang kedua kita akan langsung menjumlahkan kolom Tanggal dengan kolom Jumlah berdasarkan kriteria Hari yang kita masukan kedalam cell E4 dan E5.
Kesulitan dari cara yang kedua ini adalah kita harus mengubah format kolom Tanggal dalm cell E4/E5 melalui custom number format.
Hasil dari cara yang kedua ini akan terlihat seperti dalam gambar berikut ini :
Langkah yang pertama sebelum menjumlahkan kolom jumlah dengan kriteria hari kita harus menyesuaikan format dengan cara sebagai berikut :
- Klik/blok range B4:B8
- Klik kanan dalam area yang dipilih
- Klik Format Cells
- Klik Tab Number
- Klik sub menu Custom
- Dalam kotak Type masukan : [$-421]dddd,dd-mmm-yy
- Klik Ok
Setelah melakukan 7 langkah diatas maka tanggal yang dimasukan kedalam kolom B akan berubah seperti pada gambar diatas.
Perubahan yang saya maksud adalah dengan munculnya hari dari tanggal yang kita masukan dalam kolom tersebut.
Selanjutnya untuk cell E4:E5 lakukan hal yang sama seperti langkah yang diatas hanya saja pada kolom Type masukan format sebagai berikut : [$-421]dddd.
Dalam artikel tersebut disertakan gambar untuk memasukan format kedalam sub menu custom number.
Hal yang paling penting untuk mengisi kolom E4 dan E5 adalah jangan mengetikan hari secara manual.
Untuk memunculkan hari Senin dalam cell E4 ketik angka 2 dan otomatis akan muncul hari senin.
Untuk cell E5 ketik angka 3 dan akan otomatis muncul hari Selasa seperti terlihat dalam gambar diatas.
Jika mmebutuhkan hari lainnya silahkan masukan angka selanjutnya misalnya 4 untuk rabu, 5 untuk Kamis dan seterusnya.
Setelah mengubah format dari kedua kolom tersebut diatas maka selanjutnya kita akan menggabungkan rumus SUMPRODUCT dengan WEEKDAY untuk menambahkan kolom Jumlah berdasarkan hari.
Rumus yang digunakan untuk menambahkan kolom Jumlah dalam cell F5 dan F6 adalah sebabagi berikut :
Cell F5 : =SUMPRODUCT((WEEKDAY($B$4:$B$8)=E4)*$C$4:$C$8)
Cell F6 : =SUMPRODUCT((WEEKDAY($B$4:$B$8)=E5)*$C$4:$C$8)
Rumus WEEKDAY dalam gabungan rumus diatas akan mengambil angka dari hari yang telah kita masukan kedalam kolom Tanggal.
Konversi angka dari hari ini akan disesuaikan dengan syarat yang adal dalam cell E4 dan E5 kemudian akan mengambil dan menjumlahkan kolom Jumlah sesuai dengan kriteria angka yang mewakili hari tersebut.
Jika semua langkah dalam cara yang kedua ini berhasil dilakukan maka hasilnya akan terlihat seperti dalam gambar diatas.
Untuk melengkap pembelajaran berikut saya lampirkan contoh file Excelnya dan silahkan dipelajari melalui link berikut ini :
--------------------------------------------------
Nama file : Contoh penjumlahan berdasarkan hari
Ukuran file : 21 kb
Link : Contoh file
--------------------------------------------------
itulah pembahasan kita kali ini tentang cara menjumlahkan angka berdasarkan kriteria hari dalam Excel, semoga artikel ini bermanfaat untuk semua pembaca.
Post a Comment for "Rumus Excel Untuk Menjumlahkan Angka Berdasarkan Hari Dalam Sebuah Tabel"