Kombinasi Concatenate dan Sumif

Apabila anda mempunyai data transaksi yang lumayan banyak dan anda akan menampilkannya dalam bentuk report. Berikut ada trik sederhana yang mudah-mudahan bisa membantu anda dalam menyusun laporan sederhana.
Kita akan coba menggabungkan fungsi Concatenate dan Sumif.

Untuk contoh kasus, mari kita buat terlebih dahulu tabel seperti di bawah ini :

Lakukan langkah-langkah berikut :

1. Lakukan perintah advanced filter pada kolom salesman (C1:C11) yang hasilnya kita simpan di kolom H1.
2. Lakukan sekali lagi proses advanced filter tapi kali ini pada kolom produc (D2:D11) yang hasil nya kita simpan di sel I1.
3. Blok hasil filter produk yang ada di kolom I1, lalu lakukan copy paste special, transpose pada sel I1 juga, sehingga menghasilkan tabel seperti berikut : ( anda tinggal menambahkan border sesuai gaya anda )

Nah, berarti sekarang kita akan menggabungkan antara salesman dan produk pada tabel data menjadi satu kesatuan data yang nantinya akan diuji berdasarkan kriteria uyang kita berikan, caranya adalah pada sel A2 gunakan perintah concatenate terhadap C2 dan D2, seperti berikut :

copykan rumus concatenate tersebut sampai sel A11.














Sekarang mari kita lakukan pengisian tabel laporan pada sel I2. Yang harus diperhatikan adalah berarti kriteria untuk proses sumif sekarang yaitu hasil dari concatenate tadi (Kolom A: "JoeSeragam" dan seterusnya sesuai dengan hasil penggabungan).
Masih ingat syntax sumif : =SUMIF(range;criteria;[sum_range]), untuk kasus ini berarti syntax tersebut bisa diganti menjadi ==SUMIF(range;CONCATENATE(text1;text2;...);[sum_range]) karena kriterianya sudah kita ganti dengan penggabungan tadi.
Jadi untuk mengisi sel I2, formulanya seperti ini : ( copykan rumus tersebut sampai sel L3 )

 

Penjelasan rumus :

• $A:$A, merupakan data yang akan diuji, kolom tersebut di absolutkan (F4) karena supaya pada saat kita mengcopy rumus tersebut pada sel selanjutnya acuan kolomnya tidak berubah/bergeser.
• CONCATENATE($H2,I$1), berisi kriteria yang akan diuji (bahasa olay : diadukeun) dengan range. Pada rumus tersebut pada  $H2 yang diabsolutkan adalah kolomnya saja dan pada I$1 yang diabsolutkan adalah barisnya  saja. 
• $E:$E, berisi alamat range data yang akan dijumlahkan

sehinga tabel laporan anda akan seperti ini,

Anda tinggal menambahkan baris dan kolom total yang diisi rumus penjumlahan, laporan pun selesai dan enak untuk dibaca.
Semoga trik sederhana ini bermanfaat dan selamat mencoba.