PENGGABUNGAN FUNGSI SUM DENGAN IF

Penjumlahan Dengan Syarat Berganda

Pembahasan Blog 'Belajar Excel' kali ini akan semakin seru dengan pemaparan tentang Formula Array pada penggabungan dua fungsi excel, Fungsi SUM dengan Fungsi IF.  Pada Microsoft Excel, Fungsi SUM dan Fungsi IF dapat digabungkan melalui pembentukan Formula Array. Berbeda dengan Fungsi SUMIF, oleh karena penggabungan Fungsi SUM dengan Fungsi IF dapat melakukan penjumlahan dengan lebih dari satu syarat. Untuk lebih jelasnya kita langsung saja mempraktekan melalui contoh-contoh berikut ini.

Contoh 1:

Tabel pada gambar di atas menampilkan jumlah 'delivery' dua jenis kayu HTI, Acacia crassicarpha dan Acacia mangium, yang bersumber dari empat 'sector'.  Selanjutnya, tabel kedua yang berada di kiri tabel yang pertama meminta kita untuk melengkapi jumlah delivery setiap jenis kayu untuk masing-masing sector, pada setiap cell yang  diberi warna kuning.  Disini kita akan melakukan pemilahan nilai delivery untuk dijumlahkan berdasarkan dua syarat yang harus dipenuhi.

• Syarat yang pertama adalah kesamaan jenis kayu, yaitu crassicarpha ataukah mangium.
• Syarat yang kedua adalah kesamaan sumber dari mana kayu berasal, yaitu sector1, sector2, sector3, ataukah sector4

Penggabungan Fungsi SUM dengan Fungsi IF diterapkan untuk menjawab permasalahan tersebut, seperti ditampilkan pada tabel berikut ini.

• Pada cell G5, masukkan formula berikut ini:
  =SUM(IF(($B$5:$B$19=G$4)*($C$5:$C$19=$F5),$D$5:$D$19)).
• Setelah menekan ENTER, kita dapat melihat nilai pada cell G5 belum menunjukkan hasil yang benar. Agar dapat bekerja dengan benar, formula pada cell G5 harus diubah menjadi Formula Array.
• Untuk mengubah menjadi Formula Array.  Lakukan cara berikut ini.
  • Klik cell G5, jika pointer cell tidak berada lagi pada cell tersebut, lalu tekan tombol keyboard F2 untuk masuk ke modus edit cell.
  • Kemuadian tekan CTRL+SHIFT+ENTER untuk mengubahnya menjadi Formula Array. Seperti yang dapat terlihat pada formula bar, sekarang, formula pada G5 akan berubah menjadi seperti berikut ini.
    {=SUM(IF(($B$5:$B$19=G$4)*($C$5:$C$19=$F5),$D$5:$D$19))}
• Formula pada cell G5 akan memilih untuk menjumlahkan nilai-nilai delivery pada baris dengan Jenis='crassicarpha', yang dinyatakan oleh parameter ($B$5:$B$19=G$4) dan dengan Sector='sector1', yang dinyatakan oleh parameter ($C$5:$C$19=$F5). Dengan bahasa sederhana, Formula Array pada cell G5 akan menjumlahkan nilai-nilai delivery untuk jenis 'crassicarpha' yang berasal dari 'sector1'.
• Range dari mana nilai-nilai yang memenuhi syarat akan dipilih untuk dijumlahkan dinyatakan oleh parameter $D$5:$D$19.
• Tanda * pada Formula Array di cell G5 ekivalen dengan AND. Jika kita menginginkan hubungan OR, kita dapat menggunakan tanda +.
• Penetapan alamat mutlak atau alamat relatif pada kolom dan baris pada alamat dari Formula Array pada cell G5 didasarkan pada pertimbangan untuk pengcopyan formula tersebut ke cell-cell yang lain.
• Selanjutnya, copykan Formula Array pada cell G5 ke cell-cell lainnya, yang diberi warna kuning pada gambar di atas. Untuk melihat efek perubahan dari alamat cell ketika formula pada cell G5 dicopykan ke cell-cell yang lain, Silahkan mencobanya sendiri pada Microsoft Excel.

Catatan: Jawaban atas permasalahan pada contoh ini dapat juga dipecahkan dengan mudah menggunakan fasilitas PivotTabel yang tersedia pada Microsoft Excel.

Contoh 2:

Pada contoh yang kedua ini, nilai-nilai delivery dari sector akan di-kumulatif-kan. Dua syarat yang harus dipenuhi dalam rangka memilah nilai-nilai yang akan dijumlahkan untuk mendapatkan nilai kumulatif  delivery dari sector pada tanggal tertentu adalah:

• Nilai-nilai delivery yang dipilih untuk dijumlahkan memiliki sector yang sesuai dengan sector yang tertera pada baris yang bersangkutan.
• Nilai-nilai delivery yang dipilih untuk dijumlahkan memiliki tanggal yang sama atau lebih kecil dari tanggal yang tertera pada baris yang bersangkutan.