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 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 Berdasarkan Grup

Menjumlahkan nilai berdasarkan grup sangat berguna untuk mengetahui total nilai terkait kelompok kategori data tertentu dalam sebuah tabel. Contoh rumus yang akan dijelaskan dalam kesempatan ini dapat diterapkan pada sebuah tabel dimana data sudah disortir berdasarkan kriteria grup, sehingga item grup mengelompok, tidak acak. Dengan demikian kita bisa membuat rumus total jumlah nilai grup tertentu setiap baris pertama dalam grup.

Contoh:

  

Rumus Umum:

=IF(A2<>A1,SUMIF(rangeKriteria,Kriteria,rangeJumlah),"")

Penjelasan:

A2<>A1 merupakan expresi untuk mengecek konten sebuah sel apakah tidak sama dengan konten sel di atasnya. Jika tidak sama makan proses penjumlahan nilai grup  akan dilakukan.

rangeKriteria = range grup yang dijadikan sebagai kriteria dalam proses penjumlahan. Dalam hal adalah kolom warna (range A5:A14)

Kriteria = grup yang ingin dijumlahkan nilainya. Kriteria grup ini akan diambil dari konten pada salah satu sel dalam rangeKriteria (A5:A14) 

rangeJumlah = range pada kolom jumlah (range B5:B14) yang akan dijumlahkan nilainya per masing-masing grup.

Cara Kerja Rumus

Perhatikan contoh rumus excel di atas untuk menghitung jumlah nilai masing-masing grup warna.

=IF(A5<>A4,SUMIF(A5:A14,A5,B5:B14),"")

Rumus tersebut menggunakan kombinasi fungsi IF dan SUMIF.

Fungsi IF berguna untuk mengecek apakah konten sel A5 sama atau tidak dengan sel di atasnya (sel A4).  Jika berbeda maka  hasilnya adalah TRUE, sebaliknya jika sama maka hasilnya adalah FALSE.

Fungsi SUMIF berperan untuk menindaklanjuti jika A5<>A4 bernilai TRUE. Dengan kata lain konten sel A5 berbeda dengan konten A4. Fungsi SUMIF ini menjumlahkan nilai pada rangeJumlah (B5:B14) dengan mengacu pada kriteria warna (sel A5) yang ditemukan pada konten pada rangeKriteria (A5:A14) 

Jika A5<>A4 bernilai FALSE atau konten A5 sama dengan konten A4 maka rumus akan menghasilkan nilai blank ""

Supaya referensi rangeKriteria (A5:A14) dan rangeJumlah (B5:B14) tidak bergeser ketika rumus  di copy-paste ke sel di bawahnya maka kita perlu menerapkan referensi absolute. Sehingga rumus dapat dituliskan sebagai berikut:

=IF(A5<>A4,SUMIF($A$5:$A$14,A5,$B$5:$B$14),"")

Alternative Rumus Untuk Menjumlahkan Berdasarkan Grup

Selain menggunakan fungsi SUMIF, kita juga bisa menggunakan fungsi lainnya untuk proses penjumlahan berdasarkan grup. Misalnya fungsi SUMIFS dan SUMPRODUCT.

Penjumlahan Grup Menggunakan Kombinasi IF dan SUMIFS

=IF(A5<>A4,SUMIFS(B5:B14,A5:A14,A5))

Penjumlahan Grup Menggunakan Kombinasi IF dan SUMPRODUCT

=IF(A5<>A4,SUMPRODUCT((A5:A14=A5)*B5:B14),"")

Demikian pembahasan singkat mengenai contoh rumus excel untuk menjumlahkan nilai per masing-masing grup. Semoga bermanfaat.

Contoh rumus excel lainnya:

• Menjumlahkan nilai terendah dengan kriteria
• Menjumlahkan nilai tertinggi dengan kriteria
• Menjumlahkan nilai terendah di excel

Penjumlahan Berdasarkan Warna di Excel

Artikel ini membahas penjumlahan berdasarkan kriteria warna di excel. Untuk mengetahui cara menghitung jumlah sel dengan warna tertentu dapat dilihat di artikel Menghitung Jumlah Sel Berdasarkan Warna:

Penjumlahan berdasarkan kritera warna di excel dapat dilakukan dengan beberapa alternative rumus. Salah satunya yang paling umum adalah menggunakan fungsi SUMIF. Namun selain SUMIF, kita juga bisa menggunakan fungsi lainnya untuk menjumlahkan berdasarkan kriteria warna, antara lain: fungsi SUMIFS, fungsi SUMPRODUCT dan fungsi SUM (dalam bentuk rumus array).

Contoh:

Rumus Umum:

=SUMIF(rangeWarna,warna,rangeJumlah)

• rangeWarna = range kolom berisi kriteria warna

• warna = jenis warna yang akan dihitung jumlahnya

• rangeJumlah = range kolom yang berisi jumlah yang akan dihitung totalnya

Penjelasan

Rumus SUMIF akan melihat konten pada rangeWarna, kemudian mencocokan dengan kriteria  warna. Jika cocok maka rumus ini akan menjumlahkan data dari sel sejajar di kolom rangeJumlah. 

Pada saat mengetik argumen rangeWarna dan rangeJumlah sangat disarankan menggunakan referensi absolute. Ini sangat penting supaya referensi tetap dan tidak bergeser pada saat rumus di copy paste ke sel di bawahnya.

Untuk menjadikan referensi absolute, yaitu dengan cara mengetik tanda dolar ($) sebelum komponen kolom dan komponen baris pada nama range default. Selain itu, tanda absolute dolar bisa ditambahkan ke nama range default dengan cara memposisikan kursor bagian nama range di formula bar, kemudian menekan F4 satu kali.

Cara Kerja Rumus

Perhatikan contoh rumus dalam gambar di atas yaitu:

=SUMIF($B$4:$B$13,E4,$C$4:$C$13)

Dari rumus tersebut kita bisa mengetahui komponen argumen rumus sebagai berikut:

• rangeWarna ⇒ range kolom berisi data warna yaitu range $B$4:$B$13, merupakan bentuk absolute dari range B4:B13
• warna ⇒ sel berisi kriteria warna sebagai kata kunci, yaitu sel E4.  Kriteria ini dalam bentuk referensi relative (tanpa tanda dolar) sehingga jika dicopy ke sel di bawahnya, referensi akan ikut bergeser menjadi sel di bawahnya yaitu sel E5 yang berisi kriteria warna lainnya.
• rangeJumlah ⇒ range kolom berisi bilangan yang akan dijumlahkan nilainya, yaitu range $C$4:$C$13 yang merupakan bentuk absolute dari range C4:C13

Alternative Rumus Lainnya 

Selain menggunakan fungsi SUMIF, kita juga bisa melakukan penjumlahan berdasarkan kriteria warna menggunakan fungsi excel lainnya seperti SUMIFS, SUMPRODUCT dan rumus Array.

Menggunakan fungsi SUMIFS

=SUMIFS(rangeJumlah,rangeWarna,warna)

=SUMIFS($C$4:$C$13,$B$4:$B$13,E4)

Menggunakan fungsi SUMPRODUCT

=SUMPRODUCT((rangeWarna=warna)*rangeJumlah)

=SUMPRODUCT(($B$4:$B$13=E4)*$C$4:$C$13)

Menggunakan rumus array (tekan Ctrl + Shift + Enter  setelah selesai mengetik rumus).

Rumus ini sebenarnya menggunakan fungsi SUM, hanya saja dalam bentuk rumus array.

{=SUM((rangeWarna=warna)*rangeJumlah}

{=SUM(($B$4:$B$13=E4)*$C$4:$C$13)}

Keterangan rumus array: tanda kurung kurawal {} pada rumus array jangan diketik secara manual karena tanda tersebut akan muncul secara otomatis begitu kita menekan Ctrl + Shift + Enter .

Demikian pembahasan singkat mengenai contoh rumus untuk penjumlahan berdasarkan kriteria warna. Semoga bermanfaat.

Contoh Rumus Excel Lainnya:

• Kalkulasi Penjumlahan Total Berjalan
• Rumus SUM 3D Untuk Penjumlahan Multi Sheet
• Membuat Histogram Menggunakan Rumus Excel