Rumus Excel untuk penjumlahan berdasarkan kreteria

Hallo sahabat berdesa,
Pada kesempatan ini saya akan membahas mengenai penggunaan formula excel untuk melakukan penjumlahan berdasarkan Kreteria yang kita inginkan.

Dalam Administrasi Kantor Desa Formula ini akan sangat membantu dalam mempercepat pekerjaan, khususnya yang menggunakan aplikasi excel, contohnya pada Penyusunan Rancangan APBDes kita perlu mengetahui jumlah Pendapatan dan Belanja berdasarkan kreteria sumber dana yang digunakan, contoh lainnya misalnya melakukan penghitungan jumlah penduduk berdasarkan kreteria wilayah dusun/ RT/RW, jenis kelamin dan agama dan masih banyak contoh-contoh lainnya.

OK kita langsung saja, disini saya akan menggunakan contoh Penyusunan RAPBDes dan melakukan penjumlahan Pendapatan dan Belanja berdasarkan kreteria sumber dana. saya asumsikan sahabat berdesa semua sudah memiliki Form RAPBDes sesuai dengan daerah masing-masing, tapi jika belum punya silahkan download disini.

diatas adalah Form RAPBDes yang sudah di isi Jenis kode rekening dan Uraiannya, pada Anggaran, Kode Sumber dana dan sisa masih kosong, pada file excel yang di download silah pilih sheet "REK KEGIATN 1"
Selanjutnya kita akan membuat tabel dimana rumus akan dibuat untuk melakukan penjumlahan sesuai dengan kreteria Suber dana yang kita masukkaan pada Kode sumber dana, buatlah tabel seperti berikut:

Pada kesempatan ini saya tidak akan membahas cara membuat form di excel, OK lanjut, kopinya boleh di minum dulu,... tabel diatas saya buat dalam satu sheet dengan form RAPBDes maksudnya agar lebih cepat kita mengetahui hasil penjumlahan setelah kita mengisi form RAPBDes, pada file yang di Download silahkan pilih sheet "REK KEGIATN 2"

Langkah selanjutnya kita akan membuat kode sumber dana berdasarkan Sumber Pendapatan yang sudah ada pada Form RAPBDes di tabel yang kita buat tadi, berikut adalah tabel jadinya

Selanjutnya kita mulai mengisikan formula pada tabel, formula pertama adalah "SUMIF" yaitu formula yang dapat melakukan penjumlahan berdasarkan kreteria, formula lengkapnya adalah

=SUMIF(range;creteria;[sum_range])

range = adalah kolom dimana kita akan mengisikan kode-kode sumber dana dalam RAPBDes
creteria = adalah kreteria tertentu yang akan kita jumlahkan
sum_range = adalah kolom tempat kita memasukkan angka-angka yang akan di jumlahkan nantinya

Bedasarkan formula yang akan kita gunakan maka di dapat :
range = kolom warna merah
creteria = kolom warna kuning dan
sum_range = kolom warna biru

pada file download silahkan lihat sheet "REK KEGIATN 3"

Sekarang saatnya mulai menuliskkan formulanya, pertama kita akan membuat Formula di tabel pada kolom PENDAPATAN 

=SUMIF(U12:U45;X11;T12:T45)


range = U12:U45
creteria = X11 dan
sum_range = T12:T45

sebelum mengcopy ke seluruh kolom PENDAPATAN perlu diperhatikan kolom range dan kolom sum_range bersifat tetap sedangkan creteria berdasarkan kode sumber dana yang sudah kita buat di kolom yang berwarna kuning, jadi kita perlu melakukan sedikit modifikasi, caranya silahkan blok range = U12:U45 pada formula dan tekan F4 pada keyboard kita, begitu juga pada sum_range = T12:T45, blok sum_range = T12:T45 pada formula dan tekan F4 pada keyboard kita selanjutnya enter. sehingga formulanya menjadi :

=SUMIF($U$12:$U$45;X11;$T$12:$T$45)



selanjutnya silahkan copykan rumus ke seluruh kolom PENDAPATAN pada tabel yang sudah kita buat tadi
setelah di copy ke kolom PENDAPATAN di tabel selanjutnya silahkan diuji apakah formulanya sudah bekerja apa tidak, masukkan nilai pada kolom anggaran berwarna biru RAPBDes dan masukkan kode sumber dana pada kolom kode sumber dana berwarna biru RAPBDes, maka secara otomatis pada kolom PENDAPATAN tabel muncul nilai penjumlahan sesuai input diatas

jika berhasil maka akan kita lihat seperti gambar diatas
pada file download silahkan lihat sheet "REK KEGIATN 4"

selanjutnya untuk mengisi kolom BELANJA pada tabel juga menggunakan formula yang sama, bedanya dengan formula kolom PENDAPATAN adalah pada range dan sum_range, yaitu
range = U45:U275 dan
sum_range = T45:T275

sehingga formulanya menjadi =

=SUMIF($U$45:$U$275;X11;$T$45:$T$275) 


langkah selanjutnya sama silahkan copykan rumus ke seluruh kolom BELANJA pada tabel yang sudah kita buat tadi
setelah di copy ke kolom BELANJA di tabel selanjutnya silahkan diuji apakah formulanya sudah bekerja apa tidak, masukkan nilai pada kolom anggaran berwarna biru RAPBDes dan masukkan kode sumber dana pada kolom kode sumber dana berwarna biru RAPBDes, maka secara otomatis pada kolom BELANJA tabel muncul nilai penjumlahan sesuai input

input form APBDes pada Katagori Belanja


hasil input pada kolom belanja


untuk pengisian Kolom SISA pada tabel tinggal membuat formula pengurangan sederhana yaitu PENDAPATAN di kurangi BELANJA

langkah terakhirnya adalah membuat data validation, karena excel akan membaca input persis seperti yang kita ketikan, kesalahan pengetikan kan mengakibtkan tidak terbaca dengan benar oleh formula walau hanya itu berupa spasi', contoh ketika kita input "PAD" tanpa spasi dan "PAD " dengan spasi akan dibaca berbeda maka kita perlu membuat data validation berdasarkan kode yang sudah kita buat dalam tabel
caranya :

1. klik pada cell = U12 pada kolom berwarna merah
2. pilih tab Data 
3. klik pada Data Validation

maka akan muncul jendela seperti berikut




ubah pada pilihan Allow dan pilih List, pada Source klik tanda segi empat di ujung lalu blok KODE SUMBER DANA kolom berwarna kuning pada tabel yang telah kita buat



setelah kolom KODE SUMBER DANA selesai di block selanjutnya klik lagi tanda segi empat pada Data Validation, selanjutnya klik Error Alet pada jendela Data Validation isikan seperti gambar di bawah, atau sesuaikan seperti selera masing-masing lalu klik OK dan Enter




untuk menerapkan ke seluruh kolom, copy cell U12, blok dan Paste pada Cell U13 sampai  Cell U280

sampai disini kita sudah selesai membuat Data Validation, coba masukkan KODE SUMBER DANA di kolom merah dengan Kode yang tidak kita buat di Tabel pada KOLOM KODE SUMBER DANA kolom warna kuning lalu Enter akan muncul warning spt di gambar di bawah



Demikian dulu Tips saya kali ini, semoga bermanfaat
karena BOS saya sudah mangil-mangil dari tadi hehehe,
Selamat belajar sahabat berdesa semua, untuk yang mau bertanya atau berkomentar silahkan isi form coment di bawah artikel ini

DOWNLOAD FILE EXCEL GRATIS DI SINI

Link Youtube https://youtu.be/Iwe84zUHAn0