Jika Anda ingin menemukan jumlah nilai berdasarkan suatu kondisi, kemungkinan besar Anda mengetahui rumus SUMIF dan fungsi SUMIFS Google Sheets
Masalah dengan fungsi SUMIF adalah memungkinkan Anda menentukan hanya satu kondisi
Namun, dalam banyak kasus, kondisinya tidak begitu hitam putih. Kita sering perlu mencari penjumlahan berdasarkan lebih dari satu syarat. Dalam kasus seperti itu, fungsi SUMIFS bisa sangat berguna
Dalam tutorial ini, saya akan menunjukkan cara menggunakan fungsi SUMIFS di Google Sheets menggunakan beberapa contoh praktis
Daftar isi
Apa Fungsi SUMIFS Google Sheets?
Sederhananya, fungsi SUMIFS menemukan jumlah nilai yang memenuhi lebih dari satu syarat. Berbeda dengan fungsi SUMIF yang hanya mengerjakan satu per satu
Fungsi memindai melalui rentang sel dan mengambil yang cocok dengan semua kondisi yang ditentukan
Setelah dipindai melalui rentang, itu meringkas nilai yang diambil dan menampilkan hasilnya
Sebagai contoh, jika Anda memiliki daftar transaksi penjualan dan ingin mengetahui jumlah semua transaksi dalam rentang tanggal tertentu, Anda dapat melakukannya dengan SUMIFS
Sintaks – Cara Menggunakan SUMIFS di Google Sheets
Sintaks umum untuk fungsi SUMIFS adalah
=SUMIFS[sum_range, criteria_range1, criteria1,[ criteria_range2, criteria2, .. criteria_range_n, criteria_]]
Di Sini,
- sum_range adalah rentang sel yang berisi nilai yang ingin Anda uji
- kriteria_rentang1 adalah rentang untuk memeriksa kriteria1
- criteria1 adalah ketentuan yang harus dipenuhi oleh criteria_range1
- kriteria_rentang2, kriteria2, dll. adalah rentang dan kriteria tambahan untuk diperiksa
Kita dapat menambahkan kriteria sebanyak yang kita butuhkan
Namun, tak perlu dikatakan lagi bahwa harus ada setidaknya 1 rentang kriteria dan parameter kriteria dalam fungsi tersebut
Aturan SUMIFS Rumus Google Sheets
Berikut adalah beberapa hal yang perlu diingat saat merumuskan perhitungan SUMIFS
- SUMIFS adalah untuk beberapa kondisi. Gunakan rumus SUMIF untuk perhitungan kondisi tunggal
- Anda dapat menggunakan teks, tanggal, dan angka sebagai kriteria
- Anda dapat menggunakan wildcard [?,*,~] dengan perhitungan SUMIFS
- Anda dapat menggunakan operator dengan Fungsi SUMIFS
- Rentang tambahan harus memiliki baris dan kolom yang cocok sebagai jumlah_rentang
- Anda tidak dapat menggunakan ARRAY dengan fungsi SUMIFS
- String teks harus dalam tanda kutip, referensi sel tidak boleh
Cara Menggunakan Fungsi SUMIFS di Google Sheets
Sintaks fungsi akan menjadi lebih jelas setelah kita mengerjakan beberapa contoh
Pada tabel di bawah ada 8 karyawan, beserta departemennya, lokasi, tanggal bergabung, jumlah jam kerja, dan uang yang diperoleh dari penjualan
Mari kita lihat beberapa skenario menggunakan data ini
SUMIFS Google Sheets Contoh 1 – Cara Menjumlahkan Dengan Banyak Kriteria Menggunakan Logika AND
Katakanlah Anda ingin mencari total jam kerja semua karyawan departemen Manufaktur di New York. Dalam hal ini, kami memiliki dua kondisi
- Departemen = “Manufaktur”
- Lokasi = “New York”
Jadi parameter untuk fungsi SUMIFS adalah sebagai berikut
- sum_range akan menyertakan sel di lokasi E2. E9 – Jam kerja
- criteria_range1 akan menyertakan lokasi sel B2. B9 – Departemen
- kriteria1 akan menjadi "Manufaktur", karena kami ingin memilih sel di mana departemen = "Manufaktur"
- criteria_range2 akan menyertakan lokasi sel C2. C9 – Lokasi
- kriteria2 akan menjadi "New York", karena kami ingin memilih sel di mana lokasi = "New York"
Oleh karena itu, Anda dapat mengetikkan rumus berikut di bilah rumus
=SUMIFS[E2:E9,B2:B9,"Manufacturing",C2:C9,"New York"]_
Inilah yang Anda dapatkan sebagai hasilnya
Penjelasan SUMIFS DAN Logika Google Spreadsheet di Formula Ini
Dalam kasus di atas, fungsi SUMIFS memeriksa setiap sel dari B2 ke B9 dan C2 ke C9 untuk menemukan sel yang memenuhi kedua kondisi tersebut – masing-masing “Manufaktur” dan “New York”
Untuk setiap baris yang cocok, fungsi memilih nilai jam kerja yang sesuai dari kolom E
Itu kemudian menambahkan semua nilai jam kerja yang dipilih dan menampilkan hasilnya di sel C13
Menggunakan SUMIFS dengan Kondisi Tanggal
Mari kita tambahkan satu syarat lagi. Katakanlah kita juga ingin menambahkan kriteria bahwa tanggal masuk karyawan harus sebelum 1 Januari 2020
Jadi sekarang kita memiliki tiga syarat
- Departemen = “Manufaktur”
- Lokasi = “New York”
- Tanggal bergabung < 01/01/2020
Itu berarti kita perlu menambahkan dua parameter lagi ke fungsi SUMIFS
- criteria_range3 akan menyertakan lokasi sel D2. D9 – Tanggal bergabung
- kriteria3 akan menjadi "=10", karena kita ingin memilih sel dengan jam kerja>=10
Untuk mendapatkan hasil yang benar, Anda dapat mengetikkan rumus berikut di bilah rumus
=SUMIFS[F2:F9,B2:B9,"Manufacturing",E2:E9,">=10"]
Inilah yang Anda dapatkan sebagai hasilnya
Penjelasan Formula
Dalam kasus di atas, fungsi SUMIFS memeriksa setiap sel dari B2 hingga B9 dan E2 hingga E9 untuk menemukan sel yang memenuhi kedua kondisi tersebut – masing-masing “Manufaktur” dan “>=10”.
Untuk setiap baris yang cocok, fungsi memilih nilai penjualan yang sesuai dari kolom F
Itu kemudian menjumlahkan semua nilai penjualan yang dipilih dan menampilkan hasilnya di sel C13
Beberapa Kondisi dalam Satu Kolom [SUMIFS ATAU Google Sheets Logic]
Sayangnya, tidak ada cara bagi Google Spreadsheet untuk SUMIF beberapa kriteria menjadi satu rentang. Anggaplah Anda mengalami kesalahan penggajian dan Anda perlu memeriksa karyawan mana yang dibayar secara tidak benar. SO Anda membuat spreadsheet seperti di bawah ini
Anda mungkin ingin memasukkan “Ya” dan “Tidak diketahui” sebagai kriteria untuk perhitungan SUMIFS Anda. Tapi, ini akan mengembalikan kesalahan
Cara paling sederhana untuk menggunakan logika OR di SUMIFS adalah dengan menjumlahkan dua kalkulasi. Dalam contoh ini, Anda dapat menyalin perhitungan yang sama dua kali tetapi dengan satu perhitungan menggunakan "Ya" sebagai kriteria dan yang lainnya menggunakan "Tidak" seperti itu
=SUMIFS[C2:C7,B2:B7,"Yes",D2:D7,"Yes"] + SUMIFS[C2:C7,B2:B7,"Unknown",D2:D7,"Yes"]_
Fungsi Google SUMIFS menangani beberapa kriteria menjadi jawaban akhir yang benar dengan metode ini
Menggunakan Fungsi SUMIFS Dengan Sel Kosong dan Non Kosong
Anda dapat menggunakan fungsi SUMIFS dengan operator untuk menjumlahkan sel jika tidak berisi data atau data apa pun. Berikut adalah contoh rumus untuk mencari sel kosong dengan menggunakan spasi di antara tanda kutip
=SUMIFS[A3:A33,B3:B33,"Paid",C3:C33," "]
Rumus ini hanya akan menjumlahkan jika ada sel kosong di kolom c untuk baris yang sesuai dan pelanggan ditandai sebagai Dibayar di kolom lainnya. Anda dapat menerapkan logika yang sama dan operator yang tidak kosong [] untuk menjumlahkan sel juga, seperti itu
=SUMIFS[A3:A33,B3:B33,"Paid",C3:C33,""]_
Bacaan Terkait. Fungsi DSUM Google Spreadsheet
Menggunakan Wildcard dengan SUMIFS
Seperti yang telah kita bahas sebelumnya, Anda dapat menggunakan wildcard dalam perhitungan SUMIFS. Wildcard adalah operator yang dapat Anda gunakan untuk mewakili apa pun. Dua yang paling umum adalah tanda bintang dan tanda tanya. Tanda bintang menunjukkan kecocokan nol atau lebih, sedangkan tanda tanya mewakili tepat 1. Misalnya, bila digunakan sebagai string teks B* dapat berupa Bobby, Be, Beetle, B, B15, atau apa pun yang dimulai dengan B. Di sisi lain, B?? . Berikut beberapa contoh rumus SUMIFS dengan wildcard
Berikut ini akan mencari dan menjumlahkan item yang Dijual dengan nomor produk yang dimulai dengan 132
=SUMIFS[A2:A22,B2:B22,"Sold",C2:C21,"132*"]
Inilah yang akan terlihat jika kode produk harus tepat 5 karakter
=SUMIFS[A2:A22,B2:B22,"Sold",C2:C21,"132??"]
_Cara Mengatasi Pesan Galat SUMIFS yang Umum
Anda akan mendapatkan pesan kesalahan jika rentang yang Anda gunakan tidak memiliki ukuran yang sama. Rentang SUM harus cocok dengan rentang pencarian. Mari kita gunakan contoh di atas lagi. Rumus =SUMIFS[A2. A22,B2. B22,”Terjual”,C2. C21,”132??”] akan berfungsi karena rentang A2. A22, B2. B22, dan C2. C21, semuanya berukuran sama. Jika kita mengubah rentang ke A1. A16, B2. B22, dan C5. 10, formula tidak akan berfungsi lagi.
Menggunakan Operator Logika
Anda dapat menggunakan operator logika NOT[], AND[], dan OR[], untuk bekerja dengan SUMIFS. 3 operator logis ini cukup jelas.
- Gunakan operator logika AND[] jika Anda perlu menyertakan satu atau lebih kecocokan untuk dijumlahkan
- Gunakan operator logika OR[] jika bisa salah satu dari pilihan
- Gunakan NOT[] jika Anda ingin mengecualikan frasa yang dipilih
Pertanyaan yang Sering Diajukan
Apakah Google Spreadsheet Memiliki SUMIFS?
Ya, cukup ketik =SUMIFS[ ke dalam sel kosong untuk mulai menggunakan fungsi SUMIFS di Google Sheets
Apa Fungsi SUMIFS di Google Sheets?
SUMIFS akan menjumlahkan data ketika lebih dari satu kondisi terpenuhi. Misalnya, dalam rencana perjalanan produk, Anda dapat menjumlahkan produk x jika memiliki Stok Rendah dan Tidak Sesuai Pesanan dalam dua kolom terpisah
Berapa Banyak Kriteria SUMIFS yang Dapat Memiliki Google Sheets?
SUMIFS dapat memiliki hingga 127 kriteria di Google Sheets
Bisakah Anda Menambahkan Dua SUMIFS Bersama?
Alih-alih menggunakan input "atau logika" langsung ke SUMIFS, Anda harus menambahkannya bersama. Jadi, jawaban untuk kedua pertanyaan ini adalah ya. Berikut adalah contoh bagaimana Anda akan melakukannya
=SUMIFS[C2:C7,B2:B7,"Yes",D2:D7,"Yes"] + SUMIFS[C2:C7,B2:B7,"Unknown",D2:D7,"Yes"]_
Bagaimana Anda Melakukan Sumif Dengan Banyak Kriteria di Google Sheets?
Jika beberapa kriteria dalam satu kolom, Anda harus menambahkan dua perhitungan SUMIFS bersama seperti pada contoh di atas. Untuk "dan logika" [beberapa kolom] tambahkan setiap kriteria dan rentang baru ke satu perhitungan SUMIFS seperti itu
=SUMIFS[E2:E9,B2:B9,"Manufacturing",C2:C9,"New York"]_0
Di mana "Manufaktur" dan "New York" adalah kriterianya, dan rentang di sebelah kirinya adalah sel yang ingin Anda tarik dari rumus SUMIFS
Poin Lembar SUMIFS untuk Diingat
Dalam menggunakan fungsi SUMIFS Google Sheets, ada beberapa poin penting yang harus Anda perhatikan
- Fungsi SUMIFS tidak peka huruf besar-kecil
- Jika kriteria berisi nilai teks, tanggal, atau karakter pengganti, maka harus diapit dengan tanda kutip ganda
- Referensi sel dalam kriteria tidak diapit tanda kutip
- Anda harus memiliki setidaknya 1 pasang parameter criteria_range dan kriteria dalam fungsi SUMIFS Anda
Fungsi SUMIFS sangat serbaguna dan dapat disesuaikan sehingga Anda dapat memasukkan sejumlah kondisi yang Anda inginkan
Saya mendorong Anda untuk memasukkan fungsi SUMIFS Google Sheets ke dalam spreadsheet Anda kapan pun Anda perlu mencari jumlah berdasarkan lebih dari satu syarat
Semoga ini bisa membantu
Tutorial Google Spreadsheet lain yang mungkin berguna bagi Anda
- Cara VLOOKUP Banyak Kriteria di Google Sheets
- Fungsi REGEXMATCH di Google Sheets
- Cara Menggunakan fungsi INDIRECT di Google Sheets
- Menambahkan Kolom di Google Sheets
- Cara Membandingkan Dua Kolom di Google Sheets [untuk Kecocokan dan Perbedaan]
Postingan Terpopuler
5 Cara Sederhana untuk Menyoroti Duplikat di Google Sheets
JIKA BERISI Formula Google Sheets [2 Opsi Pintar]
Cara Menerapkan Formula ke Seluruh Kolom di Google Sheets
Cara VLOOKUP Dari Lembar Lain di Google Lembar
Puncak
Sumit adalah Pakar Google Spreadsheet dan Microsoft Excel. Dia memberikan pelatihan spreadsheet kepada perusahaan dan telah dianugerahi penghargaan bergengsi Excel MVP oleh Microsoft atas kontribusinya dalam berbagi pengetahuan Excel dan membantu orang.