Bagaimana cara menggunakan sumif/sumifs dengan logika or?

Terkadang kita perlu melakukan sedikit perubahan pada formula agar efektif.

Sama seperti itu, SUMIF ATAU. Ini adalah formula lanjutan yang membantu Anda meningkatkan fleksibilitas SUMIF. Ya, Anda juga bisa melakukan SUMIFS. Namun sebelum menggunakannya, izinkan saya memberi tahu Anda satu hal tentang SUMIF.

Di SUMIF Anda hanya bisa menggunakan satu kriteria dan di SUMIFS Anda bisa menggunakan beberapa kriteria untuk mendapatkan penjumlahan.

Hanya satu hal seperti itu. Katakanlah di SUMIFS, jika Anda menentukan dua kriteria berbeda, hanya sel yang memenuhi kedua kriteria tersebut yang akan dijumlahkan.

Karena ini bekerja dengan logika AND, semua kriteria harus dipenuhi agar sel dapat disertakan. Saat Anda menggabungkan logika OR dengan SUMIF/SUMIFS, Anda dapat menambahkan nilai menggunakan dua kriteria berbeda secara bersamaan.

Tanpa basa-basi lagi, mari pelajari rumus luar biasa ini.

Apakah kita benar-benar membutuhkan logika OR? Sebuah contoh?

Izinkan saya menunjukkan contoh di mana kita perlu menambahkan logika OR di SUMIFS atau SUMIF.

Lihatlah tabel data di bawah ini di mana Anda memiliki daftar produk beserta kuantitas dan status pengirimannya saat ini.

Dalam data ini Anda memiliki dua jenis produk (cacat dan rusak) yang harus Anda kembalikan ke pemasok kami.

Namun sebelumnya, Anda ingin menghitung jumlah total kedua jenis produk tersebut. Jika Anda memilih metode normal, Anda harus menerapkan SUMIF dua kali untuk mendapatkan totalnya.

Namun jika Anda menggunakan kondisi OR di SUMIF, Anda bisa mendapatkan total kedua hasil perkalian dengan satu rumus.

Oke, mari kita terapkan OR dengan SUMIFS

Sebelum memulai, silakan unduh file contoh ini dari sini   bersambung.

Di sel lain di spreadsheet tempat Anda ingin menghitung totalnya, masukkan rumus di bawah ini dan tekan Enter.

 =SUM(SUMIFS(C2:C21,B2:B21,{"Damage","Faulty"}))

Pada rumus di atas Anda telah menggunakan SUMIFS tetapi jika Anda ingin menggunakan SUMIF maka Anda dapat memasukkan rumus di bawah ini ke dalam sel.

 =SUM(SUMIF(B2:B21,{"Damage","Faulty"},C2:C21))

Dengan menggunakan dua rumus di atas Anda akan mendapatkan hasil 540. Untuk mengecek ulang, cukup periksa totalnya secara manual.

Bagaimana cara kerja rumus ini?

Seperti yang saya katakan, fungsi SUMIFS menggunakan logika AND untuk menambahkan nilai. Namun rumus yang Anda gunakan di atas menyertakan logika OR.

Untuk memahami rumus ini, Anda perlu membaginya menjadi tiga bagian berbeda.

1. Hal pertama yang harus dipahami adalah Anda telah menggunakan dua kriteria berbeda dalam rumus ini menggunakan konsep array. Pelajari lebih lanjut tentang papan dari sini .
2. Hal kedua adalah ketika Anda menentukan dua nilai berbeda menggunakan array, SUMIFS harus mencari kedua nilai tersebut secara terpisah.
3. Hal ketiga adalah bahkan setelah menggunakan rumus array, SUMIFS tidak dapat mengembalikan jumlah kedua nilai dalam satu sel. Itu sebabnya Anda perlu menggabungkannya ke fungsi SUM.

Dengan konsep di atas, Anda bisa mendapatkan total untuk kedua jenis produk dalam satu sel.

Ini akan bekerja sama dengan SUMIF dan SUMIFS.

Dan, Anda juga dapat memperluas rumus Anda dengan menentukan nama produk pada kriteria kedua di SUMIFS untuk mendapatkan total untuk satu produk.

Menggunakan rentang kriteria dinamis

Di bagian komentar, seseorang meminta saya menggunakan referensi sel untuk menambahkan beberapa kriteria daripada memasukkannya langsung ke dalam rumus.

Itu pertanyaan yang sangat valid dan Anda dapat melakukannya menggunakan rentang nama dinamis alih-alih nilai-nilai hardcore.

Dan untuk melakukan ini, Anda hanya perlu membuat dua perubahan kecil pada rumus Anda.

1. Yang pertama adalah daripada menggunakan kurung kurawal, Anda harus menggunakan rentang bernama (cara terbaik adalah menggunakan array) dari nilai Anda.
2. Dan setelah itu Anda perlu memasukkan rumus ini menggunakan Ctrl+Shift+Enter sebagai rumus array yang tepat.

Jadi sekarang rumus Anda adalah:

Beberapa kriteria dengan kolom berbeda

Izinkan saya membawa Anda ke level berikutnya dari formula ini. Lihat saja tabel data di bawah ini.

Dalam tabel ini Anda memiliki dua kolom berbeda dengan dua tipe status berbeda. Nah dari tabel data ini Anda perlu menjumlahkan jumlah kerusakan produk, status pengembaliannya adalah “Tidak”.

Ini hanya berarti produk rusak dan cacat yang belum dikembalikan. Dan rumus untuk ini adalah.

 =SUM(SUMIFS(D2:D21,B2:B21,{"Damage","Faulty"},C2:C21,"No"))

Kesimpulan

Bagian terbaik tentang penggunaan teknik OR adalah Anda dapat menambahkan begitu banyak kriteria ke dalamnya. Jika Anda ingin menggunakan logika OR saja, Anda bisa menggunakan SUMIF. Dan jika Anda ingin membuat kondisi OR dan kondisi AND dalam satu rumus, Anda bisa menggunakan SUMIFS untuk itu.

Anda tahu, ini adalah salah satu tip formula favorit saya dan saya harap ini bermanfaat bagi Anda.

Sekarang beritahu aku satu hal. Pernahkah Anda menggunakan rumus seperti ini sebelumnya? Bagikan pemikiran Anda dengan saya di bagian komentar, saya ingin mendengar pendapat Anda, dan jangan lupa untuk membagikan tip ini kepada teman-teman Anda.