Menghitung Baris Terfilter Dengan Kriteria

Kali ini kita akan mengupas contoh rumus excel untuk menghitung baris terfilter dengan kriteria atau besyarat. Berbeda dengan contoh-contoh sebelumnya mengenai penjumlahan/perhitungan besyarat (misalnya menggunakan fungsi COUNTIF) hasilnya tidak mempedulikan apakah baris di-hide (tersembunyi) atau tidak, maka contoh rumus excel kali ini akan mencoba memberikan solusi supaya rumus hanya menghitung banyaknya data tertentu pada baris yang tidak tersembunyi atau yang tampak saja (visible  only) melalui proses autofilter.

Adapun rumus yang dapat digunakan untuk keperluan ini berupa kombinasi beberapa fungsi yang terdiri atas SUMPRODUCT, SUBTOTAL, OFFSET, dan ROW.

Contoh Kasus

Anggaplah kita memiliki sebuah tabel data peserta sebuah kegiatan. Data terdiri atas 4 Kolom yaitu kolom "No", "Nama", "Asal Kota", dan "Jenis Kelamin". Untuk contoh, supaya mudah ditampilkan dalam screenshot, saya gunakan tabel dengan jumlah baris yang sedikit saja. Tabel terletak pada range A4:D10 dan menggunakan autofilter pada kolom "Asal Kota" sehingga hanya menampilkan peserta yang berasa dari kota tertentu saja.

Pertanyaannya: Bagaimana cara menghitung jumlah peserta dengan jenis kelamin laki-laki ataupun perempuan pada tabel yang difilter?

Solusi atas contoh kasus ini dapat dilihat dalam screenshot di bawah ini.

Rumus Umum:

=SUMPRODUCT((kolomKriteria=kriteria)*SUBTOTAL(103,OFFSET(headerKriteria,ROW(kolomKriteria)-ROW(headerKriteria),0)))

Keterangan:

Secara umum rumus digunakan untuk menghitung banyaknya baris di kolomKriteria yang berisi text kriteria dan dalam kondisi visible (baris tidak tersembunyi)

• kolomKriteria = merupakan range berupa kolom dalam tabel, range ini berisi text yang menjadi syarat atau kriteria dalam penjumlahan. Contoh: range D5:D11 yang berisi data jenis kelamin peserta. Meskipun baris terakhir dalam tabel contoh adalah baris 10, tapi kita gunakan range D5:D11 (baris terakhir 11) sebagai referensi kolomKriteria untuk memastikan referensi mencakup semua baris dalam tabel. Hal ini terutama jika kita menyeleksi referensi kolom dalam keadaan tabel terfilter karena mungkin saja baris terakhir tidak dapat terseleksi jika dalam kondisi tersembunyi.
• kriteria = text yang harus ada supaya penjumlahan bisa dieksekusi. Misalnya: "Laki-Laki" ataupun sebuah referensi sel yang berisi text "Laki-Laki", contoh: sel B13.
• headerKriteria = referensi sel yang merupakan header dari kolomKriteria, contohnya sel D4. Harap dipastikan bahwa headerKriteria ini tepat satu sel diatas referensi kolomKriteria, bukan merupakan bagian dari kolomKriteria. Perhatikan contoh sel D4, referensi sel tersebut tepat satu sel di atas referensi kolomKriteria D5:D11

Contoh Rumus:

=SUMPRODUCT(($D$5:$D$11=B13)*SUBTOTAL(103,OFFSET($D$4,ROW($D$5:$D$11)-ROW($D$4),0)))

Menjumlahkan jumlah baris pada range D5:D1 yang berisi text sesuai referensi B13 dan konsisi tampak visible atau tidak tersembunyi.

Cara Kerja Rumus

Dalam contoh rumus di atas, SUMPRODUCT berperan sebagai fungsi utama, dan kondisi dalam rumus SUMPRODUCT dapat dipecah menjadi 2 bagian seperti berikut ini:

=SUMPRODUCT(kolomKriteria_berisi_kriteria * Nampak_atau_tersembunyi)

kolomKriteria_berisi_kriteria diexpresikan dengan statement ($D$5:$D$11=B13) yaitu untuk mengecek apakah sel dalam range D5:D11 berisi text yang sama dengan text di sel B13 atau tidak. Jika iya maka akan menghasilkan nilai TRUE, jika tidak maka akan menghasikan nilai FALSE.

Untuk membuktikannya, jika anda sudah mencoba mengetikan rumus secara lengkap sesuai contoh. Silahkan diseleksi bagian rumus ($D$5:$D$11=B13) kemudian tekan F9 sehingga statement akan berubah menjadi array  {TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE} yang merupakan value atas pengecekan masing-masing baris pada kolomKriteria (D5:D11)

• kolomKriteria baris ke-1 = "Laki-Laki"  (TRUE)
• kolomKriteria baris ke-2 = "Perempuan"  (FALSE)
• kolomKriteria baris ke-3 = "Laki-Laki"  (TRUE)
• kolomKriteria baris ke-4 = "Laki-Laki"  (TRUE)    - tersembunyi
• kolomKriteria baris ke-5 = "Laki-Laki"  (TRUE)
• kolomKriteria baris ke-6 = "Perempuan"  (FALSE)  -tersembunyi
• kolomKriteria baris ke-7 = ""  (FALSE)

Dapat kita perhatikan dari elemen array diatas, ternyata pengecekan value pada range D5:D11 tidak membedakan apakah baris dakam kondisi tersembunyi (hidden) atau tidak. Akibatnya: hasil TRUE akan diperoleh juga untuk baris tersembunyi berisi text "Laki-Laki" (baris ke-4).

Nampak_atau_tersembunyi : Supaya hanya bisa menghitung jumlah data laki-laki dalam baris tersembunyi, maka kita harus membuat statement ke-2 yang bisa mengecek apakah baris tersembunyi atau tidak.

Tugas ini diserahkan pada bagian rumus sebagai berikut:

SUBTOTAL(103,OFFSET($D$4,ROW($D$5:$D$11)-ROW($D$4),0))

Fungsi SUBTOTAL dengan kode 103 digunakan untuk menghitung banyaknya data yang tidak tersembunyi. Dengan kata lain, jika visible atau tidak tersembunyi maka masing-masing baris yang berisi data akan bernilai 1, sebaliknya akan bernilai 0.

Fungsi OFFSET digunakan sebagai patokan langkah pengecekan sel.

• OFFSET(D4, ROW(D5)-ROW(D4),0) = OFFSET(D4,1,0) = baris ke-1 di bawah sel D4
• OFFSET(D4, ROW(D6)-ROW(D4),0) = OFFSET(D4,2,0) = baris ke-2 di bawah sel D4
• OFFSET(D4, ROW(D7)-ROW(D4),0) = OFFSET(D4,3,0) = baris ke-3 di bawah sel D4
• OFFSET(D4, ROW(D8)-ROW(D4),0) = OFFSET(D4,4,0) = baris ke-4 di bawah sel D4
• OFFSET(D4, ROW(D9)-ROW(D4),0) = OFFSET(D4,5,0) = baris ke-5 di bawah sel D4
• OFFSET(D4, ROW(D10)-ROW(D4),0) = OFFSET(D4,6,0) = baris ke-6 di bawah sel D4
• OFFSET(D4, ROW(D11)-ROW(D4),0) = OFFSET(D4,7,0) = baris ke-7 di bawah sel D4

Kembali ke contoh rumus lengkapnya:

=SUMPRODUCT(($D$5:$D$11=B13)*SUBTOTAL(103,OFFSET($D$4,ROW($D$5:$D$11)-ROW($D$4),0)))

Dalam kondisi tabel terfilter Asal Kota Bandung, silahkan diseleksi bagian rumus yang berwarna merah, dan kemudian tekan F9. Maka kita akan melihat array {1;1;1;0;1;0;0} yang menggambarkan kondisi baris apakah berisi data dan tersembunyai atau tidak.

• Baris ke-1 = berisi data dan tampak >> Nilai 1
• Baris ke-2 = berisi data dan tampak >> Nilai 1
• Baris ke-3 = berisi data dan tampak >> Nilai 1
• Baris ke-4 = berisi data dan tersembunyi >> Nilai 0
• Baris ke-5 = berisi data dan tampak >> Nilai 1
• Baris ke-6 = berisi data dan tersembunyi >> Nilai 0
• Baris ke-7 = data kosong dan tampak >> Nilai 0

Langkah berikutnya: 2 buah array yang merupakan hasil dari 2 proses sebelumnya diproses oleh fungsi SUMPRODUCT.  Fungsi ini bertugas untuk mengalikan masing-masing element array yang seposisi kemudian menjumlahkan hasil masing-masing perkalian tersebut. Secara ringkas dapat dilihat dalam langkah rumus berikut:

=SUMPRODUCT({TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE}*{1;1;1;0;1;0;0})
=(TRUE*1)+(FALSE*1)+(TRUE*1)+(TRUE*0)+(TRUE*1)+(FALSE*0)+(FALSE*0)
= 1 + 0 + 1 + 0 + 1 + 0 + 0
= 3

Sampai pada tahap ini selesai sudah pembahasan mengenai cara menghitung baris terfilter dengan kriteria. Mudah-mudahan penjelasan yang singkat ini dapat mudah difahami dan tentunya semoga bermanfaat.


Contoh Rumus Excel Lainnya

• Penjumlahan Jika Text Mengandung Kata Tertentu.
• Penjumlahan Jika Text Diakhiri Kata Tertentu
• Penjumlahan Jika Lebih Besar Dan Lebih Kecil

Penjumlahan Jika Text Mengandung Kata Tertentu

Postingan ini akan mengupas contoh rumus excel untuk penjumlahan bersyarat dengan kriteria text jika mengandung kata tertentu. Jenis rumus ini akan sangat bermanfaat jika kita dihadapkan pada sejumlah data text dengan kata kunci yang posisinya tidak beraturan, kadang di awal, kadang ditengah ataupun di akhir. Formula untuk menghitung penjumlahan bersyarat dengan kriteria jika text mengandung kata tertentu dapat dibuat menggunakan bantuan fungsi SUMIF dengan modifikasi pada parameter kriteria

Contoh Kasus

Anggaplah kita memiliki list data produk untuk anak-anak dan produk untuk dewasa. Kata kunci untuk membedakan produk untuk anak dan untuk dewasa adalah keberadaan kata "anak" dan kata "dewasa" dalam nama produk. Sayangnya posisi kata kunci tersebut tersebut tidak terpola. Kadang ada pada bagian awal, kadang pada bagian tengah dan kadang pada bagian akhir.

Pertanyaannya: Bagaimana rumus yang efektif untuk menghitung secara cepat jumlah semua produk untuk anak?

Contoh Rumus:

=SUMIF(A5:A14,"*anak*",B5:B14)

Rumus di atas dapat digunakan untuk menjumlahkan nilai yang ada pada range B5:B14 jika sel sejajar pada range A5:A14 berupa text yang mengandung kata "anak"

Rumus Umum:

=SUMIF(rangeKriteria,kriteria,rangeJumlah)

Penjelasan:

• rangeKriteria = merupakan range berupa kolom dalam tabel, range ini berisi text yang menjadi syarat atau kriteria dalam penjumlahan. Contoh: range A5:A14
• kriteria = text yang harus ada supaya penjumlahan bisa dieksekusi. Contoh: "*anak*".  Penulisan kriteria selalu diawali dan diakhiri dengan karakter wildard bintang atau asterisk (*). Tanda bintang ini mewakili string atau text lainnya sebelum dan sesudah kata "anak". 
• rangeJumlah = merupakan range berupa kolom dalam tabel, range ini berisi nilai atau bilangan yang ingin kita totalkan jumlahnya jika sel sejajar dalam rangeKriteria mengandung kata tertentu. Contoh: Range B5:B14

Cara Kerja Rumus

Perhatikan contoh rumus:

=SUMIF(A5:A14,"*anak*",B5:B14)

Sebagaimana kita ketahui bahwa fungsi SUMIF berguna untuk melakukan penjumlahan bersyarat, yaitu proses penjumlahan jika syarat tertentu terpenuhi. 

Kunci utama dalam proses penjumlahan jika text mengandung kata tertentu adalah cara penulisan kriteria dimana harus dimulai dan diakhiri dengan karakter bintang atau tanda asterisk (*).  

Dalam contoh, jika karakter bintang  (*) dihilangkan, maka rumus SUMIF hanya akan menjumlahkan niai pada range B5:B14 jika sel sejajar pada range A5:A14 berisi text "anak" saja, sehingga rumus akan menghasilkan nilai nol karena pada range kriteria A5:A14 tidak ada sel yang hanya berisi hanya text "anak".

Sedangkan jika penulisannya benar, yaitu "*anak* maka rumus akan menghasilkan nilai 15, yaitu jumlah semua produk yang nama produknya mengandung kata "anak"

Untuk lebih memahami silahkan diganti parameter rumus "*anak*" menjadi "*dewasa*", maka rumus akan memberikan hasil bilangan 11 yang merupakan penjumlahan semua produk yang mengandung kata "dewasa"

Demikian pembahasan singkat mengenai contoh rumus penjumlahan bersyarat jika text mengandung kata tertentu. Mudah-mudahan contoh rumus ini semakin menambah wawasan kita mengenai cara menggunakan formula excel secara lebih baik lagi dan dapat diterapkan dalam pekerjaan sehari-hari.

Contoh Rumus Excel Lainnya

• Penjumlahan Jika Text Diakhiri Kata Tertentu
• Penjumlahan Jika Lebih Besar dan Lebih Kecil
• Penjumlahan Jika Text Diawali Kata Tertentu

Penjumlahan Jika Text Diakhiri Kata Tertentu

Pada postingan beberapa waktu yang lalu sudah dibahas mengenai contoh rumus excel untuk penjumlahan bersyarat dengan kriteria text yang diawali oleh kata tertentu. Nah, dalam kesempatan ini, kita juga akan membahas contoh rumus serupa, namun dengan kriteria jika text diakhiri kata tertentu. Jenis rumus ini akan sangat bermanfaat jika kita dihadapkan pada sejumlah data text dengan pola kata akhir tertentu yang dapat dijadikan sebagai kategori data. Formula untuk menghitung penjumlahan bersyarat dengan kriteria kata akhir text tertentu dapat dibuat dengan mudah dan hanya diperlukan satu fungsi saja, yaitu fungsi SUMIF.

Contoh Kasus

Anggaplah kita memiliki list data produk untuk anak-anak dan produk untuk dewasa. Produk untuk anak selalu dituliskan kata "anak" sebagai akhir kata, sedangkan produk untuk orang dewasa selalui dituliskan kata "dewasa" pada akhir nama produk.

Pertanyaannya: Bagaimana rumus untuk menghitung jumlah semua produk untuk dewasa?

Contoh Rumus:

=SUMIF(A5:A14,"*dewasa",B5:B14)

Rumus di atas dapat digunakan untuk menjumlahkan nilai yang ada pada range B5:B14 jika sel sejajar pada range A5:A14 berupa text yang dengan kata akhir “dewasa”

Rumus Umum:

=SUMIF(rangeKriteria,kriteria,rangeJumlah)

Penjelasan:

• rangeKriteria = merupakan range berupa kolom dalam tabel, range ini berisi text yang menjadi syarat atau kriteria dalam penjumlahan. Contoh: range A5:A14
• kriteria = text yang harus ada supaya penjumlahan bisa dieksekusi. Contoh: "*dewasa".  Penulisan kriteria selalu diawali dengan karakter wildard bintang atau asterisk (*). Tanda bintang ini mewakili frase kata atau text lainnya sebelum kata "dewasa". Sedangkan penulisan kata "dewasa" setelah kata bintang menjunjukan bahwa kata "dewasa" harus ada sebagai akhir dari text supaya penjumlahan bisa di eksekusi.
• rangeJumlah = merupakan range berupa kolom dalam tabel, range ini berisi nilai atau bilangan yang ingin kita totalkan jumlahnya jika sel sejajar dalam rangeKriteria memiliki kata akhir yang ditentukan. Contoh: Range B5:B14

Cara Kerja Rumus

Perhatikan contoh rumus:

=SUMIF(A5:A14,"*dewasa",B5:B14)

Sebagaimana kita ketahui bahwa fungsi SUMIF berguna untuk melakukan penjumlahan bersyarat, yaitu proses penjumlahan jika syarat tertentu terpenuhi. 

Kunci utama dalam proses penjumlahan jika text diakhiri kata tertentu adalah cara penulisan kriteria dimana harus dimulai dengan dengan karakter bintang atau tanda asterisk (*).  

Dalam contoh, jika karakter bintang  (*) dihilangkan, maka rumus SUMIF hanya akan menjumlahkan niai pada range B5:B14 jika sel sejajar pada range A5:A14 berisi text "dewasa" saja, sehingga rumus akan menghasilkan nilai nol karena pada range kriteria A5:A14 tidak ada sel yang hanya berisi text "dewasa".

Sedangkan jika penulisannya benar, maka rumus akan menghasilkan nilai 10, yaitu jumlah semua produk yang namanya berakhiran kata "dewasa"

Untuk lebih memahami silahkan diganti parameter rumus "*dewasa" menjadi "*anak" dan perhatikan hasilnya.

Demikian pembahasan singkat mengenai contoh rumus penjumlahan bersyarat dengan kriteria kata akhir text tertentu. Mudah-mudahan dapat difahami dengan baik dan dapat diterapkan dalam pekerjaan sehari-hari menggunakan microsoft excel.

Contoh Rumus Excel Lainnya

• Penjumlahan Jika Lebih Besar dan Lebih Kecil
• Penjumlahan Jika Text Dimulai Kata Tertentu
• Rumus Penjumlahan Setiap Baris ke-N

Penjumlahan Jika Lebih Besar dan Lebih Kecil

Penjumlahan bersyarat jika nilai lebih besar dan lebih kecil dapat diartikan juga sebagai penjumlahan bilangan yang terletak diantara nilai batas bawah dan nilai batas atas. Contoh praktisnya: Misalnya berapa sih jumlah total semua bilangan yang lebih besar dari 100, namun lebih kecil dari 200. Dalam hal ini nilai 100 sebagai batas bawah, dan nilai 200 sebagai batas atas, sehingga juga dapat dijabarkan sebagai penjumlahan semua bilangan yang ada diantara nilai 100 dan 200.

Rumus excel untuk melakukan penjumlahan bersyarat jenis ini tergolong mudah dan sederhana, cukup diperlukan satu fungsi saja yaitu fungsi SUMIFS. Sebagai alternatif, kita juga bisa membuat formula penjumlahan bersyarat jika lebih tinggi dan lebih rendah menggunakan fungsi SUMPRODUCT dan fungsi SUM. Penggunaan fungsi SUM harus dalam bentuk rumus array.

Contoh Rumus

=SUMIFS(B5:B14,B5:B14,">100",B5:B14,"<200")

Rumus tersebut dapat digunakan untuk menjumlahkan semua bilangan yang ada pada semua sel dalam range B5:B14 jika nilainya lebih besar dari 100 namun lebih kecil dari 200.

Rumus Umum:

=SUMIFS(rgJumlah,rgKriteria,kriteriaBawah,rgKriteria,kriteriaAtas)

Penjelasan:

• rgJumlah = rgKriteria, yaitu sebuah range berupa kolom dalam tabel, berisi bilangan yang akan dijumlahkan secara bersyarat jika lebih besar dan lebih kecil dari nilai tertentu. Dalam hal ini rgJumlah sama dengan rgKriteria karena kita menggunakan range yang sama, baik sebagai range nilai yang akan dijumlahkan maupun range sebagai tempat pengecekan kriteria lebih besar dan lebih kecil. Contoh: range B5:B14 
• kriteriaBawah = syarat nilai bilangan yang akan dijumlahkan yaitu harus lebih besar dari batas bawah. Contoh :  ">100"  artinya bilangan yang bisa dijumlahkan, nilainya harus lebih besar dari 100.
• kriteriaAtas = syarat nilai bilangan yang akan dijumlahkan yaitu harus lebih kecil atau kurang dari batas atas. Contoh: "<200" artinya bilangan yang bisa dijumlahkan, nilainya harus lebih kecil atau kurang dari 200.

Cara Kerja Rumus

Perhatikan contoh rumus:

=SUMIFS(B5:B14,B5:B14,">100",B5:B14,"<200")

Sebagaimana kita ketahui bahwa fungsi SUMIFS berguna untuk melakukan penjumlahan bersyarat dengan lebih dari satu kriteria. Ini artinya kita bisa menggunakan beberapa kriteria penjumlahan, seperti 2 kriteria, 3 kriteria, atau bahkan lebih banyak lagi.

Dalam hal contoh penjumlahan jika lebih besar dan lebih kecil, kita bisa menggunakan 2 kriteria:

• Kriteria1= kriteriaBawah, yaitu ">100"
• Kriteria2= kriteriaAtas, yaitu "<200"

Ini artiya bilangan yang dijumlahkan dalam range B5:B14 hanya bilangan yang nilainya lebih dari 100, tetapi kurang dari 200.

Perhatikan bilangan apa saja yang ada pada range B5:B14. Dari screenshot contoh di atas, kita dapat mengetahui bahwa bilangan yang memenuhi kriteria tersebut adalah 130, 150 dan 120. Sehingga formula akan memberikan hasil akhir 400, yaitu merupakan hasil penjumlahan 130 + 150 + 120.

Alternatif Rumus.

Selain menggunakan fungsi SUMIFS, sebagai alternatif kita juga bisa menggunakan fungsi lainnya untuk membuat rumus penjumlahan bersyarat jika lebih besar dan lebih kecil.

Dengan menggunakan contoh tabel yang sama, kita bisa menggunakan rumus berikut sebagai alternative.

Menggunakan fungsi SUMPRODUCT

=SUMPRODUCT((B5:B14>100)*(B5:B14<200)*B5:B14)

Menggunakan fungsi SUM dalam bentuk formula array

{=SUM((B5:B14>100)*(B5:B14<200)*B5:B14)}

Rumus array dapat diketahui dengan adanya tanda kurung kurawal {}, tanda tersebut jangan diketik secara langsung. Namun ketik rumus biasa (tanpa tanda kurung kurawal), setelah selesai mengetik rumus, kemudian tekan Ctrl + Shift + Enter, maka tanda kurung kurawal akan muncul pada bagian awal dan akhir rumus.

Demikian pembahasan singkat mengenai contoh rumus excel untuk menghitung jumlah bilangan yang lebih besar dan lebih kecil dari nilai tertentu dalam sebuah range data. Mudah-mudahan contoh sederhana ini bisa bermanfaat bagi pembaca.

Contoh Rumus Excel Lainnya

• Penjumlahan Jika Text dimulai Kata Tertentu
• Rumus Penjumlahan Setiap Baris ke-N
• Rumus Penjumlahan Setiap N Baris
• Rekapitulasi jumlah berdasarkan nama hari

Penjumlahan Jika Text Dimulai Kata Tertentu

Kali ini kita akan membahas contoh rumus excel untuk melakukan penjumlahan bersyarat dengan kriteria text jika dimulai dengan frase atau kata tertentu. Rumus ini sangat berguna jika kita kita memiliki sejumlah data text dengan pola kata awalan tertentu yang dapat dijadikan sebagai kategori. Misalnya terdapat list data produk beserta jumlahnya. Sebuah produk dapat dikenali dari awalan kata dalam nama produk tersebut. Nah awalan kata inilah yang dapat kita jadikan sebagai acuan dalam perhitungan. Formula untuk menjumlahkan dengan syarat text dimulai kata tertentu dapat dibuat dengan menggunakan fungsi SUMIF.

Contoh Rumus:

=SUMIF(A5:A14,"buah*",B5:B14)

Contoh rumus di atas dapat digunakan untuk menjumlahkan nilai yang ada pada range B5:B14 jika sel sejajar pada range A5:A14 berupa text yang diawali kata "buah"

Rumus Umum:

=SUMIF(rangeKriteria,kriteria,rangeJumlah)

Penjelasan:

Fungsi SUMIF berguna untuk menjumlahkan nilai pada rangeJumlah jika sel sejajar pada rangeKriteria memenuhi kriteria yang ditentukan.

• rangeKriteria = sebuah range berupa kolom dalam tabel, berisi text yang menjadi acuan atau syarat dalam penjumlahan. Contoh: range A5:A14
• kriteria = text yang harus ada supaya penjumlahan bisa dieksekusi. Kata yang menjadi kriteria harus diakhiri dengan tanda / karakter bintang ( * ) yang menandakan bahwa kata tersebut harus berada di awal text. Contoh: text "buah*" menandakan bahwa kita ingin menjumlahkan semua nilai jika text dalam rangeKriteria berawalan kata "buah"
• rangeJumlah = sebuah range berupa kolom dalam tabel yang ingin kita jumlahkan nilai yang terdapat dalam sel-sel penyusunya jika sel sejajar dalam rangeKriteria berawalan kata yang ditentukan. Contoh: Range B5:B14

Cara Kerja Rumus

Perhatikan contoh rumus:

=SUMIF(A5:A14,"buah*",B5:B14)

Sebagaimana kita ketahui bahwa fungsi SUMIF berguna untuk melakukan penjumlahan bersyarat, yaitu proses penjumlahan jika syarat tertentu terpenuhi. Kunci utama dalam proses penjumlahan jika text dimulai awalan kata tertentu adalah cara penulisan kriteria dimana harus diakhiri dengan karakter bintang atau tanda asterisk (*).  Karakter asterik ini merupakan salah satu dari karakter wildcard.

Jika karakter bintang  (*) dihilangkan, maka rumus SUMIF hanya akan menjumlahkan jika text pada rangeKriteria berisi text “buah” saja. Ini artinya rumus akan menghasilkan nilai nol karena pada range kriteria A5:A14 tidak ada sel yang hanya berisi text “buah”.

Hasil dari contoh formula di atas adalah 6 yaitu penjumlahan buah apel, buah durian dan buah nangka, yaitu semua produk yang diawali kata "buah"

Untuk lebih memahami silahkan dicoba gunakan kata lainnya sebagai kriteria, misalnya “baju*”  dan “sayur”, kemudian perhatikan hasilnya.

Sampai tahap ini diharapkan contoh rumus penjumlahan bersyarat dengan kriteria text diawali kata tertentu dapat difahami dengan baik dan dapat diterapkan dalam pekerjaan sehari-hari menggunakan microsoft excel.

Contoh Rumus Excel Lainnya

• Rumus Penjumlahan Setiap Baris ke-N
• Rumus Penjumlahan Setiap N Baris
• Rekapitulasi jumlah berdasarkan nama hari

Rumus Penjumlahan Setiap Baris ke-N

Dalam artikel sebelumnya sudah dibahas mengenai contoh rumus untuk menjumlahkan nilai setiap beberapa baris konstan atau setiap N Baris. Masih ada kaitan dengan pembahasan tersebut, dalam kesempatan ini akan dibahas contoh rumus untuk menjumlahkan nilai setiap kelipatan baris tertentu atau setiap baris ke-N dari sebuah range (harap dibedakan: pembahasan sebelumnya mengenai penjumlahan setiap N baris, sedangkan pada pembahasan kali ini mengenai penjumlahan setiap baris ke-N)

Untuk membuat rumus penjumlahan setiap baris ke-N diperlukan kombinasi beberapa fungsi, antara lain: SUMPRODUCT, MOD, dan ROW.

Contoh Rumus:

=SUMPRODUCT((MOD(ROW(B5:B14)-9,5)=0)*B5:B14)

Contoh rumus tersebut digunakan untuk menjumlahkan setiap kelipatan baris ke-5 pada range B5:B14, dimulai dari baris ke-9.

Rumus Umum:

=SUMPRODUCT((MOD(ROW(range)-barisAwal,N)=0)*range)

Penjelasan:

• Range = range berisi data nilai yang akan dijumlahkan setiap baris ke-N nya, contoh range B5:B14
• barisAwal = baris start untuk memulai penjumlahan, contoh baris 9, karena bilangan pertama yang ingin kita jumlahkan terletak pada baris ke-9, dan selanjutnya akan dijumlahkan dengan baris ke-5 berikutnya yaitu baris (9+5) = baris ke-14 (perhatikan gambar di atas)
• N = Nilai kelipatan baris yang ingin kita jumlahkan.  Contoh : setiap baris ke-5

Cara Kerja Rumus

Perhatikan struktur contoh rumus:

=SUMPRODUCT((MOD(ROW(B5:B14)-9,5)=0)*B5:B14)

Silahkan dilihat bagian rumus ROW(B5:B14)-9

Bagian rumus tersebut berguna untuk menghitung nomor urut baris pada range B5:B14 relative terhadap baris ke-9. Untuk melihat nilainya, dalam spreadsheet, silahkan diseleksi bagian rumus ROW(B5:B14)-9   dan tekan F9, maka rumus akan menjadi:

=SUMPRODUCT((MOD({-4;-3;-2;-1;0;1;2;3;4;5},5)=0)*B5:B14)

Selanjutnya fungsi MOD berguna untuk menghitung sisa pembagian. Jika bilangan dalam array habis di bagi lima maka fungsi MOD akan menghasilkan bilangan Nol. Bilangan dalam array ({-4;-3;-2;-1;0;1;2;3;4;5} yang memenuhi kriteria habis di bagi 5 adalah bilangan 0 dan 5. 

Selanjutnya fungsi MOD dikompare dengan bilangan 0 untuk mendapatkan nilai boolean TRUE dan FALSE.

Silahkan di seleksi bagian rumus (MOD({-4;-3;-2;-1;0;1;2;3;4;5},5)=0) kemudian tekan F9, sehingga rumus menjadi sebagai berikut:

=SUMPRODUCT({FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}*B5:B14)

Perhatikan nilai TRUE dalam array berada tepat setiap urutan kelipatan ke-5.

Dengan cara yang sama, seleksi bagian rumus B5:B14 dan kemudian tekan F9 untuk analisa rumus lebih lanjut:

=SUMPRODUCT({FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}*{2;1;2;3;8;2;3;4;4;13})

Sebagaimana kita ketahui, dalam operasi perkalian, nilai boolean TRUE akan diperlakukan sebagai bilangan 1, sedangkan nilai FALSE sebagai bilangan 0. 

Karena nilai TRUE terletak pada urutan  ke-5 dan ke-10, maka hasil akhirnya akan berupa penjumlahan elemen ke-5 dan ke-10 pada array {2;1;2;3;8;2;3;4;4;13} yaitu 8 + 13 = 21.

Sampai disini pembahasan mengenai contoh rumus untuk penjumlahan setiap baris ke-N. Mudah-mudahan penjelasan yang sangat singkat ini dapat mudah difahami.

Contoh Rumus Excel Lainnya

• Rumus Penjumlahan Setiap N Baris
• Rekapitulasi jumlah berdasarkan nama hari
• Rekapitulasi Jumlah Mingguan

Rumus Penjumlahan Setiap N Baris

Rumus penjumlahan setiap beberapa baris tertentu atau N baris sangat berguna jika kita memiliki sejumlah data dengan struktur urutan yang tetap. Misalnya menjumlahkan data setiap 5 hari sekali, 6 hari sekali atau 7 hari sekali dan bahkan seminggu sekali. Contoh rumus excel yang akan dibahas kali ini digunakan untuk menjumlahkan nilai setiap seminggu sekali dimana jumlah hari dalam seminggu diambil 5 hari kerja saja. Dengan kata lain kita akan menjumlahkan data setiap 5 baris. Untuk keperluan ini diperlukan gabungan fungsi SUM, OFFSET dan ROW.

Contoh Rumus:

=SUM(OFFSET(B5,(ROW()-6)*5,0,5,1))

Rumus Umum:

=SUM(OFFSET(selAwal,(ROW()-barisRumus1)*N,0,N,1))

Penjelasan:

• selAwal = sel pertama atau sel paling atas dari kolom data yang akan dijumlahkan nilainya, contoh sel B5
• barisRumus1 = Lokasi baris sel tempat mengetikan rumus yang dapat menghitung jumlah N baris pertama. Contoh: Sel tempat mengetikan rumus untuk menghitung jumlah nilai minggu ke-1 (5 baris pertama) adalah sel E6. Sel ini terletak pada baris ke-6. Sehingga kita bisa menentukan parameter barisRumus1 = 6.
• N = Nilai yang kita inginkan untuk menjumlahkan nilai setiap N baris.

Cara Kerja Rumus

Menghitung jumlah nilai pada 5 baris pertama (minggu ke-1):

Perhatikan contoh rumus pada sel E6 yang digunakan untuk menghitung nilai pada minggu pertama (5 baris pertama)

=SUM(OFFSET(B5,(ROW()-6)*5,0,5,1))

Fungsi ROW berguna untuk mengambil index baris sel dimana rumus diketik.  Karena rumus di ketik di sel E6 maka ROW() menghasilkan nilai 6. Sehingga (6-6)*5 = 0

Sehingga rumus OFFSET dapat dituliskan sebagai berikut:

=OFFSET(B5,0,0,5,1)

Hasil rumus OFFSET di atas akan mengacu ke range B5:B9

Mungkin pembaca masih bertanya-tanya mengapa contoh rumus OFFSET di atas menghasilkan range B5:B9

Rumus =OFFSET(B5,0,0,5,1) dapat dibaca sebagai berikut:  sebuah range yang jaraknya 0 baris dan 0 kolom dari sel B5 dimana range tersebut terdiri atas 5 baris dan 1 kolom. Maka range yang memenuhi kriteria tersebut adalah range B5:B9

Selanjutnya fungsi SUM bertugas menjumlahkan nilai pada range B5:B9

=SUM(B5:B9)

Dan hasilnya adalah 12

Menghitung Jumlah Nilai pada 5 Baris ke-2 (Minggu ke-2)

Selanjutnya mari kita lihat bagaimana hasilnya jika rumus =SUM(OFFSET(B5,(ROW()-6)*5,0,5,1)) diletakan di sel E7 untuk menghitung nilai pada 5 baris berikutnya (minggu ke-2). Rumusnya sama dengan penjumlahan minggu ke-1, namun akan menghasilkan jumlah nilai yang berbeda.

Kunci dari perbedaan ini adalah rumus ROW() yang akan menghasilkan nilai sesuai lokasi baris dimana rumus ditempatkan. Karena rumus ditempatkan di sel E7 maka rumus ROW() akan menghasilkan nilai 7 sehingga rumus dapat dijabarkan sebagai berikut:

=SUM(OFFSET(B5,(ROW()-6)*5,0,5,1))

=SUM(OFFSET(B5,(7-6)*5,0,5,1))

=SUM(OFFSET(B5,5,0,5,1))

=SUM(B10:B14)

=17

Perhatikan bagian rumus OFFSET(B5,5,0,5,1). Rumus ini dapat diterjemahkan sebagai berikut: sebuah range yang jaraknya 5 baris dan 0 kolom dari sel B5 dimana range tersebut terdiri atas 5 baris dan 1 kolom. Range yang memenuhi kriteria tersebut adalah range B10:B14 yang merupakan 5 baris ke 2 dalam range data yang ingin kita hitung jumlah nilainya.

Demikian pembahasan singkat mengenai contoh rumus untuk menghitung jumlah nilai setiap beberapa baris di excel. Semoga penjelasannya mudah difahami dan bisa bermanfaat.

Contoh Rumus Excel Lainnya

• Rekapitulasi jumlah berdasarkan nama hari
• Rekapitulasi Jumlah Mingguan
• Rumus Penjumlahan Berdasarkan Bulan