Tutorial ini memberikan demonstrasi mendetail tentang cara menggunakan fungsi SUMIF dan SUMIFS di Google Sheets dengan rumus dan contoh.
SUMIF adalah salah satu fungsi matematika di Google Sheets, yang digunakan untuk menjumlahkan sel secara kondisional. Pada dasarnya, fungsi SUMIF mencari kondisi tertentu dalam rentang sel dan kemudian menjumlahkan nilai yang memenuhi kondisi yang diberikan.
Misalnya, Anda memiliki daftar pengeluaran di lembar Google dan Anda hanya ingin menjumlahkan pengeluaran yang berada di atas nilai maksimum tertentu. Atau Anda memiliki daftar item pesanan dan jumlah yang sesuai, dan Anda hanya ingin mengetahui jumlah total pesanan item tertentu. Di situlah fungsi SUMIF berguna.
SUMIF dapat digunakan untuk menjumlahkan nilai berdasarkan kondisi angka, kondisi teks, kondisi tanggal, wildcard serta berdasarkan sel kosong dan tidak kosong. Google Sheets memiliki dua fungsi untuk meringkas nilai berdasarkan kriteria: SUMIF dan SUMIFS. Fungsi SUMIF menjumlahkan angka berdasarkan satu kondisi sementara SUMIFS menjumlahkan angka berdasarkan beberapa kondisi.
Dalam tutorial ini, kami akan menjelaskan cara menggunakan fungsi SUMIF dan SUMIFS di Google Spreadsheet untuk menjumlahkan angka yang memenuhi kondisi tertentu.
Fungsi SUMIF di Google Sheets – Sintaks dan Argumen
Fungsi SUMIF hanyalah kombinasi dari fungsi SUM dan IF. Fungsi IF memindai rentang sel untuk kondisi tertentu, lalu fungsi SUM menjumlahkan angka yang sesuai dengan sel yang memenuhi kondisi tersebut.
Sintaks Fungsi SUMIF:
Sintaks fungsi SUMIF di Google Sheets adalah sebagai berikut:
=SUMIF(rentang, kriteria, [jumlah_rentang])Argumen:
jangkauan - Rentang sel tempat kita mencari sel yang memenuhi kriteria.
kriteria – Kriteria yang menentukan sel mana yang perlu ditambahkan. Anda dapat mendasarkan kriteria pada nomor, string teks, tanggal, referensi sel, ekspresi, operator logika, karakter wildcard serta fungsi lainnya.
jumlah_rentang – Argumen ini opsional. Ini adalah rentang data dengan nilai untuk dijumlahkan jika entri rentang yang sesuai cocok dengan kondisi. Jika Anda tidak menyertakan argumen ini, maka 'rentang' dijumlahkan.
Sekarang, mari kita lihat bagaimana menggunakan fungsi SUMIF untuk menjumlahkan nilai dengan kriteria yang berbeda.
Fungsi SUMIF dengan Kriteria Angka
Anda dapat menjumlahkan angka yang memenuhi kriteria tertentu dalam rentang sel, dengan menggunakan salah satu operator perbandingan berikut untuk membuat kriteria.
- lebih besar dari (>)
- kurang dari (<)
- lebih besar dari atau sama dengan (>=)
- kurang dari atau sama dengan (<=)
- sama dengan (=)
- tidak sama dengan ()
Misalkan Anda memiliki spreadsheet berikut dan Anda tertarik dengan total penjualan yang 1000 atau lebih tinggi.
Berikut cara memasukkan fungsi SUMIF:
Pertama, pilih sel tempat Anda ingin output jumlah muncul (D3). Untuk menjumlahkan angka di B2:B12 yang lebih besar dari atau sama dengan 1000, ketik rumus ini dan tekan 'Enter':
=SUMIF(B2:B12,">=1000",B2:B12)Dalam rumus contoh ini, argumen rentang dan jumlah_rentang (B2:B12) adalah sama, karena angka dan kriteria penjualan diterapkan pada rentang yang sama. Dan kami memasukkan angka sebelum operator pembanding dan mengapitnya dalam tanda kutip karena kriteria harus selalu diapit tanda kutip ganda kecuali untuk referensi sel.
Rumus mencari angka yang lebih besar atau sama dengan 1000 dan kemudian menjumlahkan semua nilai yang cocok dan menunjukkan hasilnya di sel D3.
Karena argumen range dan sum_range adalah sama, Anda dapat mencapai hasil yang sama tanpa argumen sum_range dalam rumus, seperti ini:
=SUMIF(B2:B12,">=1000")
Atau Anda dapat menyediakan referensi sel (D2) yang berisi angka, bukan kriteria angka, dan bergabung dengan operator perbandingan dengan referensi sel tersebut dalam argumen kriteria:
=SUMIF(B2:B12,">="&D2)Seperti yang Anda lihat, operator pembanding masih dimasukkan dalam tanda kutip ganda dan operator serta referensi sel digabungkan dengan ampersand (&). Dan Anda tidak perlu menyertakan referensi sel dalam tanda kutip.
Catatan: Saat Anda merujuk ke sel yang berisi kriteria, pastikan untuk tidak meninggalkan spasi di depan atau di belakang nilai dalam sel. Jika nilai Anda memiliki ruang yang tidak perlu sebelum atau sesudah nilai dalam sel yang dirujuk, maka rumus akan mengembalikan '0' sebagai hasilnya.
Anda juga dapat menggunakan operator logika lain dengan cara yang sama untuk membuat kondisi dalam argumen kriteria. Misalnya, untuk menjumlahkan nilai kurang dari 500:
=SUMIF(B2:B12,"<500")
Jumlahkan Jika Angka Sama dengan
Jika Anda ingin menambahkan angka yang sama dengan angka tertentu, Anda bisa memasukkan angkanya saja atau memasukkan angka dengan tanda sama dengan dalam argumen kriteria.
Misalnya, untuk menjumlahkan jumlah penjualan yang sesuai (kolom B) untuk kuantitas (kolom C) yang nilainya sama dengan 20, coba salah satu rumus berikut:
=SUMIF(C2:C12,"=20",B2:B12)
=SUMIF(C2:C12,"20",B2:B12)
=SUMIF(C2:C12,E2,B2:B12)
Untuk menjumlahkan angka di kolom B dengan jumlah yang tidak sama dengan 20 di kolom C, coba rumus ini:
=SUMIF(C2:C12,"20",B2:B12)
Fungsi SUMIF dengan Kriteria Teks
Jika Anda ingin menambahkan angka dalam rentang sel (kolom atau baris) yang sesuai dengan sel yang memiliki teks tertentu, Anda cukup menyertakan teks tersebut atau sel yang berisi teks dalam argumen kriteria rumus SUMIF Anda. Harap dicatat bahwa string teks harus selalu diapit oleh tanda kutip ganda (" ").
Misalnya, jika Anda ingin jumlah total penjualan di wilayah 'Barat', Anda dapat menggunakan rumus di bawah ini:
=SUMIF(C2:C13,"Barat",B2:B13)Dalam rumus ini, fungsi SUMIF mencari nilai 'Barat' di rentang sel C2:C13 dan menambahkan nilai penjualan yang sesuai di kolom B. Kemudian menampilkan hasilnya di sel E3.
Anda juga bisa merujuk ke sel yang berisi teks alih-alih menggunakan teks dalam argumen kriteria:
=SUMIF(C2:C12,E2,B2:B12)
Sekarang, mari kita dapatkan total pendapatan semua wilayah kecuali 'Barat'. Untuk melakukan itu, kita akan menggunakan tidak sama dengan operator () dalam rumus:
=SUMIF(C2:C12,""&E2,B2:B12)
SUMIF dengan WildCard
Dalam metode di atas, fungsi SUMIF dengan kriteria teks memeriksa rentang terhadap teks yang ditentukan secara tepat. Kemudian menjumlahkan angka parrel ke teks yang tepat dan mengabaikan semua angka lainnya termasuk string teks yang sebagian cocok. Untuk menjumlahkan angka dengan string teks yang cocok sebagian, Anda harus menyesuaikan salah satu karakter wildcard berikut dalam kriteria Anda:
?(tanda tanya) digunakan untuk mencocokkan karakter tunggal apa pun, di mana pun dalam string teks.*(tanda bintang) digunakan untuk menemukan kata yang cocok dengan urutan karakter apa pun.~(tilde) digunakan untuk mencocokkan teks dengan tanda tanya (?) atau karakter asterisk (*).
Kami akan contoh spreadsheet ini untuk produk dan jumlahnya untuk menjumlahkan angka dengan wildcard:
Asterisk (*) Wildcard
Misalnya, jika Anda ingin menjumlahkan jumlah semua produk Apple, gunakan rumus ini:
=SUMIF(A2:A14,"Apple*",B2:B14)Rumus SUMIF ini menemukan semua produk dengan kata "Apple" di awal dan sejumlah karakter setelahnya (dilambangkan dengan '*'). Setelah kecocokan ditemukan, itu meringkas Kuantitas nomor yang sesuai dengan string teks yang cocok.
Dimungkinkan juga untuk menggunakan beberapa wildcard dalam kriteria. Dan Anda juga dapat memasukkan karakter wildcard dengan referensi sel alih-alih teks langsung.
Untuk melakukan itu, wildcard harus diapit dengan tanda kutip ganda (“ “), dan digabungkan dengan referensi sel:
=SUMIF(A2:A14,"*"&D2&"*",B2:B14)Rumus ini menjumlahkan jumlah semua produk yang memiliki kata 'Redmi' di dalamnya, di mana pun kata itu berada dalam string.
Tanda tanya (?) Wildcard
Anda dapat menggunakan tanda tanya (?) wildcard untuk mencocokkan string teks dengan karakter tunggal apa pun.
Misalnya, jika Anda ingin mencari jumlah semua varian Xiaomi Redmi 9, Anda dapat menggunakan rumus ini:
=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)Rumus di atas mencari string teks dengan kata "Xiaomi Redmi 9" diikuti oleh karakter tunggal dan menjumlahkan yang sesuai Kuantitas angka.
Tilde (~) Wildcard
Jika Anda ingin mencocokkan tanda tanya (?) atau karakter asterisk (*), sisipkan karakter tilde (~) sebelum wildcard di bagian kondisi rumus.
Untuk menambahkan jumlah di kolom B dengan string yang sesuai yang memiliki tanda asterisk di akhir, masukkan rumus di bawah ini:
=SUMIF(A2:A14,"Samsung Galaxy V~*",B2:B14)
Untuk menjumlahkan besaran pada kolom B yang memiliki tanda tanya (?) pada kolom A pada baris yang sama, coba rumus di bawah ini:
=SUMIF(A2:A14,"~?",B2:B14)
Fungsi SUMIF dengan Kriteria Tanggal
Fungsi SUMIF juga dapat membantu Anda menjumlahkan nilai berdasarkan kriteria tanggal – misalnya, angka yang sesuai dengan tanggal tertentu, atau sebelum tanggal, atau setelah tanggal. Anda juga dapat menggunakan salah satu operator perbandingan dengan nilai tanggal untuk membuat kriteria tanggal untuk menjumlahkan angka.
Tanggal harus dimasukkan dalam format tanggal yang didukung lembar Google, atau sebagai referensi sel yang berisi tanggal, atau menggunakan fungsi tanggal seperti DATE() atau TODAY().
Kami akan menggunakan contoh spreadsheet ini untuk menunjukkan kepada Anda bagaimana fungsi SUMIF dengan kriteria tanggal bekerja:
Misalkan Anda ingin menjumlahkan jumlah penjualan yang terjadi pada atau sebelum (<=) 29 November 2019 dalam kumpulan data di atas, Anda dapat menambahkan angka penjualan tersebut menggunakan fungsi SUMIF dengan salah satu cara berikut:
=SUMIF(C2:C13,"<=29 November 2019",B2:B13)Rumus di atas memeriksa setiap sel dari C2 hingga C13 dan hanya mencocokkan sel-sel yang berisi tanggal pada atau sebelum 29 November 2019 (29/11/2019). Dan kemudian menjumlahkan jumlah penjualan yang sesuai dengan sel yang cocok dari rentang sel B2:B13 dan menampilkan hasilnya di sel E3.
Tanggal dapat diberikan ke rumus dalam format apa pun yang dikenali oleh Google Spreadsheet, seperti '29 November 2019′, '29 Nov 2019′, atau '29/11/2019', dll. Ingat nilai tanggal dan operator harus selalu diapit oleh tanda petik ganda.
Anda juga dapat menggunakan fungsi DATE() dalam kriteria alih-alih mengarahkan nilai tanggal:
=SUMIF(C2:C13,"<="&DATE(2019,11,29),B2:B13)
Atau, Anda bisa menggunakan referensi sel alih-alih tanggal di bagian kriteria rumus:
=SUMIF(C2:C13,"<="&E2,B2:B13)
Jika Anda ingin menambahkan jumlah penjualan berdasarkan tanggal hari ini, Anda dapat menggunakan fungsi TODAY() dalam argumen kriteria.
Misalnya, untuk menjumlahkan setiap dan semua jumlah penjualan untuk tanggal hari ini, gunakan rumus ini:
=SUMIF(C2:C13,HARI INI(),B2:B13)
Fungsi SUMIF dengan sel Blank atau Non-Blank
Terkadang, Anda mungkin perlu menjumlahkan angka dalam rentang sel dengan sel kosong atau tidak kosong di baris yang sama. Dalam kasus seperti itu, Anda dapat menggunakan fungsi SUMIF untuk menjumlahkan nilai berdasarkan kriteria di mana sel kosong atau tidak.
Jumlahkan jika Kosong
Ada dua kriteria di Google Sheets untuk menemukan sel kosong: "" atau "=".
Misalnya, jika Anda ingin menjumlahkan semua jumlah penjualan yang berisi string panjang nol (secara visual terlihat kosong) di kolom C, gunakan tanda kutip ganda tanpa spasi di antaranya dalam rumus:
=SUMIF(C2:C13,"",B2:B13)
Untuk menjumlahkan semua jumlah penjualan di kolom B dengan sel kosong lengkap di kolom C, sertakan “=" sebagai kriteria:
=SUMIF(C2:C13,"=",B2:B13)
Jumlah jika Tidak Kosong:
Jika Anda ingin menjumlahkan sel yang berisi nilai apa pun (tidak kosong), Anda dapat menggunakan "" sebagai kriteria dalam rumus:
Misalnya, untuk mendapatkan jumlah total penjualan dengan tanggal apa pun, gunakan rumus ini:
=SUMIF(C2:C13,"",B2:B13)
SUMIF Berdasarkan Beberapa Kriteria dengan Logika OR
Seperti yang telah kita lihat sejauh ini, fungsi SUMIF dirancang untuk menjumlahkan angka hanya berdasarkan satu kriteria, tetapi dimungkinkan untuk menjumlahkan nilai berdasarkan beberapa kriteria dengan fungsi SUMIF di Google Spreadsheet. Hal ini dapat dilakukan dengan menggabungkan lebih dari satu fungsi SUMIF dalam satu rumus dengan logika OR.
Misalnya, jika Anda ingin menjumlahkan jumlah penjualan di wilayah 'Barat' atau wilayah 'Selatan' (logika ATAU) dalam rentang yang ditentukan (B2:B13), gunakan rumus ini:
=SUMIF(C2:C13,"Barat",B2:B13)+SUMIF(C2:C13,"Selatan",B2:B13)Rumus ini menjumlahkan sel ketika setidaknya salah satu kondisi adalah TRUE. Karenanya ini dikenal sebagai 'logika ATAU'. Ini juga akan menjumlahkan nilai ketika semua kondisi terpenuhi.
Bagian pertama rumus memeriksa rentang C2:C13 untuk teks 'Barat' dan menjumlahkan nilai dalam rentang B2:B13 saat kecocokan terpenuhi. Bagian detik memeriksa nilai teks 'Selatan' dalam rentang yang sama C2:C13 dan kemudian menjumlahkan nilai dengan teks yang cocok dalam rentang jumlah yang sama B2:B13. Kemudian kedua jumlah tersebut ditambahkan bersama-sama dan ditampilkan di sel E3.
Dalam kasus hanya satu kriteria yang terpenuhi, itu hanya akan mengembalikan nilai jumlah itu.
Anda juga dapat menggunakan beberapa kriteria, bukan hanya satu atau dua. Dan jika Anda menggunakan beberapa kriteria, lebih baik menggunakan referensi sel sebagai kriteria daripada menulis nilai langsung dalam rumus.
=SUMIF(C2:C13,E2,B2:B13)+SUMIF(C2:C13,E3,B2:B13)+SUMIF(C2:C13,E4,B2:B13)
SUMIF dengan logika OR menambahkan nilai ketika setidaknya satu dari kriteria yang ditentukan terpenuhi, Tetapi jika Anda hanya ingin menjumlahkan nilai hanya ketika semua kondisi yang ditentukan terpenuhi, Anda harus menggunakan fungsi SUMIFS() saudaranya yang baru.
Fungsi SUMIFS di Google Sheets (Beberapa Kriteria)
Saat Anda menggunakan fungsi SUMIF untuk menjumlahkan nilai berdasarkan beberapa kriteria, rumus mungkin menjadi terlalu panjang dan rumit, dan Anda cenderung membuat kesalahan. Selain itu, SUMIF akan memungkinkan Anda menjumlahkan nilai hanya pada satu rentang dan ketika salah satu kondisinya TRUE. Di situlah fungsi SUMIFS masuk.
Fungsi SUMIFS membantu Anda menjumlahkan nilai berdasarkan beberapa kriteria yang cocok dalam satu atau beberapa rentang. Dan ini bekerja pada logika AND, artinya hanya dapat menjumlahkan nilai hanya jika semua kondisi yang diberikan terpenuhi. Bahkan jika satu kondisi salah, itu akan mengembalikan '0' sebagai hasilnya.
Sintaks dan Argumen Fungsi SUMIFS
Sintaks fungsi SUMIFS adalah sebagai berikut:
=SUMIFS(jumlah_rentang, kriteria_rentang1, kriteria1, [rentang_kriteria2, ...], [kriteria2, ...])Di mana,
- jumlah_rentang – Rentang sel yang berisi nilai yang ingin Anda jumlahkan saat semua kondisi terpenuhi.
- kriteria_rentang1 – Ini adalah rentang sel tempat Anda memeriksa kriteria1.
- kriteria1 – Ini adalah kondisi yang perlu Anda periksa dengan criteria_range1.
- criteria_range2, kriteria2, …– Rentang dan kriteria tambahan untuk dievaluasi. Dan Anda dapat menambahkan lebih banyak rentang dan ketentuan ke rumus.
Kami akan menggunakan kumpulan data di tangkapan layar berikut untuk menunjukkan bagaimana fungsi SUMIFS bekerja dengan kriteria yang berbeda.
SUMIFS dengan Kondisi Teks
Anda dapat menjumlahkan nilai berdasarkan dua kriteria teks yang berbeda dalam rentang yang berbeda. Misalnya, Anda ingin mengetahui jumlah total penjualan item Tenda yang dikirimkan. Untuk ini, gunakan rumus ini:
=SUMIFS(D2:D13,A2:A13,"Tenda",C2:C13,"Terkirim")Dalam formula ini, kami memiliki dua kriteria: "Tenda" dan "Terkirim". Fungsi SUMIFS memeriksa item ‘Tenda’ (kriteria1) dalam rentang A2:A13 (rentang_kriteria1) dan memeriksa status ‘Terkirim’ (kriteria2) dalam rentang C2:C13 (rentang_kriteria2). Ketika kedua kondisi terpenuhi, maka jumlah nilai yang sesuai dalam rentang sel D2:D13 (jumlah_rentang) akan dijumlahkan.
SUMIFS dengan Kriteria Bilangan dan Operator Logika
Anda dapat menggunakan operator kondisional untuk membuat kondisi dengan angka untuk fungsi SUMIFS.
Untuk menemukan total penjualan lebih dari 5 jumlah barang apa pun di negara bagian California (CA), gunakan rumus ini:
=SUMIFS(E2:E13,D2:D13,">5",B2:B13,"CA")Rumus ini memiliki dua kondisi: ">5" dan "CA".
Rumus ini memeriksa kuantitas (Jumlah) yang lebih besar dari 5 dalam rentang D2:D13 dan memeriksa status 'CA' dalam rentang B2:B13. Dan ketika kedua kondisi terpenuhi (artinya ada di baris yang sama), itu menjumlahkan jumlahnya di E2:E13.
SUMIFS dengan Kriteria Tanggal
Fungsi SUMIFS juga memungkinkan Anda untuk memeriksa beberapa kondisi dalam rentang yang sama serta rentang yang berbeda.
Misalkan, Anda ingin memeriksa jumlah penjualan total barang yang dikirim setelah 31/5/2021 dan sebelum tanggal 10/6/2021, maka gunakan rumus ini:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)Rumus di atas memiliki tiga kondisi: 31/5/2021,10/5/2021, dan Terkirim. Alih-alih menggunakan nilai tanggal dan teks langsung, kami merujuk ke sel yang berisi kriteria tersebut.
Rumus memeriksa tanggal setelah 31/5/2021 (G1) dan tanggal sebelum 10/6/2021 (G2) dalam rentang yang sama D2:D13, dan memeriksa status 'Terkirim' di antara kedua tanggal tersebut. Kemudian, jumlahkan jumlah terkait dalam rentang E2:E13.
SUMIFS dengan Sel Kosong dan Tidak Kosong
Terkadang, Anda mungkin ingin menemukan jumlah nilai saat sel terkait kosong atau tidak. Untuk melakukannya, Anda dapat menggunakan salah satu dari tiga kriteria yang telah kita bahas sebelumnya: “=”, “”, dan “”.
Misalnya, jika Anda hanya ingin menjumlahkan jumlah item 'Tenda' yang tanggal pengirimannya belum dikonfirmasi (sel kosong), Anda dapat menggunakan kriteria "=":
=SUMIFS(D2:D13,A2:A13,"Tenda",C2:C13,"=")Rumus mencari item 'Tenda' (kriteria1) di kolom A dengan sel kosong yang sesuai (kriteria2) di kolom C dan kemudian menjumlahkan jumlah yang sesuai di kolom D. "=" mewakili sel yang benar-benar kosong.
Untuk menemukan jumlah jumlah item 'Tenda' yang tanggal pengirimannya telah dikonfirmasi (bukan sel kosong), gunakan "" sebagai kriteria:
=SUMIFS(D2:D13,A2:A13,"Tenda",C2:C13,"")Kami baru saja menukar "=" untuk "" dalam rumus ini. Ia menemukan jumlah item Tenda dengan sel yang tidak kosong di kolom C.
SUMIFS dengan OR Logika
Karena fungsi SUMIFS bekerja pada logika AND, itu hanya menjumlahkan ketika semua kondisi terpenuhi. Tetapi bagaimana jika Anda ingin menjumlahkan nilai berdasarkan beberapa kriteria ketika salah satu kriteria terpenuhi. Caranya adalah dengan menggunakan beberapa fungsi SUMIFS.
Misalnya, jika Anda ingin menjumlahkan jumlah penjualan untuk 'Rak Sepeda' ATAU 'Ransel' saat statusnya 'Dipesan', coba rumus ini:
=SUMIFS(D2:D13,A2:A13,"Rak sepeda",C2:C13,"Dipesan") +SUMIFS(D2:D13,A2:A13,"Ransel",C2:C13,"Dipesan")Fungsi SUMIFS pertama memeriksa dua kriteria “Rak Sepeda” dan “Dipesan” dan menjumlahkan nilai jumlah di kolom D. Kemudian, SUMIFS kedua memeriksa dua kriteria “Ransel” dan “Dipesan” dan menjumlahkan nilai jumlah di kolom D. Dan kemudian , kedua jumlah dijumlahkan dan ditampilkan di F3. Dengan kata sederhana, rumus ini dijumlahkan ketika 'Rak sepeda' atau 'Ransel' dipesan.
Itu semua yang perlu Anda ketahui tentang fungsi SUMIF dan SUMIFS di Google Sheets.