Menjumlahkan Berdasarkan Banyak Kondisi dengan SUMIFS

Anda sudah mengetahui bahwa SUMIF menjumlahkan nilai yang sesuai dengan satu aturan, seperti semua penjualan dari wilayah Timur. Namun, pertanyaan nyata biasanya memiliki beberapa lapisan: Berapa penjualan wilayah Timur pada bulan Januari? Itu berarti dua kondisi sekaligus.

Di sinilah SUMIFS sangat berguna. Huruf S di akhir berarti fungsi ini dapat menggabungkan banyak kriteria, dan sebuah baris hanya ditambahkan ke total jika memenuhi setiap pengujian yang Anda berikan.

Dalam pelajaran ini, Anda akan mempelajari urutan argumen, menulis penjumlahan pertama dengan beberapa kriteria, dan menghindari kesalahan umum yang sering membingungkan.

SUMIFS membalik urutan yang mungkin Anda harapkan dari SUMIF. Nilai yang dijumlahkan diletakkan di awal, lalu setiap kondisi diberikan sebagai pasangan.

• sum_range — nilai yang akan dijumlahkan
• criteria_range1, criteria1 — pengujian pertama
• criteria_range2, criteria2 — pengujian kedua

Anda dapat terus menambahkan pasangan rentang dan kriteria hingga 127 kondisi. Bacalah pola berikut dengan lantang: jumlahkan ini jika ini sama dengan itu, dan jika ini sama dengan itu.

Bayangkan sebuah tabel dengan kolom A berisi Wilayah, kolom B berisi Bulan, dan kolom C berisi Jumlah. Anda ingin mengetahui total penjualan untuk wilayah Timur pada bulan Januari.

Jumlah yang dijumlahkan berada di C:C. Kondisi pertama memeriksa A:A untuk nilai "Timur", dan kondisi kedua memeriksa B:B untuk nilai "Januari". Sebuah baris hanya dihitung jika keduanya benar.

Inilah kesalahan SUMIFS yang paling umum. Rentang penjumlahan dan setiap rentang kriteria harus memiliki dimensi yang sama persis — jumlah baris dan kolomnya harus sama.

Jika rentang penjumlahan Anda adalah C2:C100, tetapi salah satu rentang kriteria adalah A2:A99, rumus akan menghasilkan kesalahan #VALUE! karena baris-barisnya tidak sejajar.

Kebiasaan yang paling aman adalah menggunakan baris awal dan akhir yang sama untuk semua rentang, atau menggunakan seluruh kolom seperti A:A secara konsisten.

Menulis "Timur" secara langsung di dalam tanda kutip memang berfungsi, tetapi laporan yang fleksibel memungkinkan pengguna memilihnya. Masukkan wilayah ke sel F1 dan bulan ke F2, lalu jadikan sel-sel tersebut sebagai referensi kriteria.

Sekarang, perubahan pada F1 atau F2 akan langsung menghitung ulang totalnya. Perhatikan bahwa referensi sel biasa tidak memerlukan tanda kutip — tanda kutip hanya digunakan untuk teks literal yang diketikkan di dalam rumus.

Kriteria tidak terbatas pada teks yang persis sama. Anda dapat menggunakan operator perbandingan untuk angka dengan mengapitnya dalam tanda kutip.

• ">100" — lebih besar dari 100
• "<=50" — kurang dari atau sama dengan 50
• "<>0" — tidak sama dengan nol

Di sini, kita menjumlahkan jumlah dari wilayah Timur, tetapi hanya untuk baris yang jumlahnya sendiri lebih besar dari 100. Perhatikan bahwa rentang penjumlahan dan rentang kriteria dapat berupa kolom yang sama.

Bagaimana jika ambang batas berada di dalam sel, bukan diketikkan langsung? Anda tidak dapat menulis ">F1" begitu saja — cara itu akan mencari teks literal F1. Sebagai gantinya, Anda harus menggabungkan operator dengan sel menggunakan simbol &.

Jadi, ">"&F1 membentuk kriteria lebih besar dari apa pun yang terdapat di F1. Teknik penggabungan ini penting untuk laporan dinamis yang dikendalikan pengguna.

SUMIFS mudah diperluas. Tambahkan satu pasangan rentang dan kriteria lagi untuk setiap aturan baru. Misalnya, kolom D berisi Penjual. Anda dapat menjumlahkan penjualan wilayah Timur pada bulan Januari yang dilakukan oleh "Maria".

Setiap kondisi semakin mempersempit hasil. Karena SUMIFS menggunakan logika AND, sebuah baris harus memenuhi ketiga pengujian agar disertakan dalam penjumlahan.

Kriteria teks mendukung karakter pengganti. Tanda bintang * mencocokkan sejumlah karakter berapa pun, sedangkan tanda tanya ? mencocokkan tepat satu karakter.

• "North*" cocok dengan North, Northeast, Northwest
• "*east*" cocok dengan apa pun yang mengandung east

Ini menjumlahkan jumlah untuk wilayah apa pun yang diawali dengan "North", yang berguna jika nama-nama wilayah Anda memiliki awalan yang sama.

Perbedaannya perlu dihafalkan karena urutan argumennya terbalik:

• SUMIF: range, criteria, [sum_range] — rentang yang diuji berada di awal, sedangkan sum_range bersifat opsional dan berada di akhir.
• SUMIFS: sum_range, criteria_range1, criteria1, ... — sum_range selalu berada di awal.

Tips: jika Anda memiliki lebih dari satu kondisi, langsung gunakan SUMIFS. Banyak orang menggunakan SUMIFS bahkan untuk satu kriteria agar selalu menggunakan satu pola yang konsisten.

Jika SUMIFS menghasilkan 0, biasanya berarti tidak ada baris yang memenuhi semua kondisi — bukan berarti rumusnya rusak. Periksa kembali spasi tersembunyi dalam teks, ejaan yang tidak cocok, atau angka yang disimpan sebagai teks.

Diagnosis cepat dapat dilakukan dengan menghapus satu kondisi setiap kali. Jika total muncul setelah suatu kriteria dihapus, kriteria itulah yang mengecualikan semua baris. Kebiasaan mengisolasi lalu menguji ini membuat proses menemukan kesalahan pada rumus dengan beberapa kriteria menjadi cepat.

Uji pemahaman Anda tentang urutan argumen SUMIFS.

Anda sekarang dapat menjumlahkan angka berdasarkan beberapa kondisi sekaligus:

• SUMIFS(sum_range, range1, crit1, range2, crit2, ...) — sum_range berada di awal.
• Semua rentang harus berukuran sama, atau Anda akan mendapatkan #VALUE!.
• Gunakan ">"&F1 untuk membandingkan dengan sel, serta * / ? untuk karakter pengganti.
• Kondisi menggunakan logika AND — sebuah baris harus memenuhi semuanya.

Selanjutnya: menghitung baris berdasarkan beberapa kondisi dengan COUNTIFS.