Sumif dengan beberapa kriteria berdasarkan logika OR dan AND
Saat mengerjakan lembar kerja Excel, Anda mungkin perlu menjumlahkan nilai berdasarkan beberapa kriteria. Terkadang, beberapa kriteria berasal dari kolom yang sama (logika OR), tetapi terkadang dari kolom yang berbeda (logika DAN). Dalam hal ini, bagaimana Anda bisa menangani tugas ini di Excel?
- Sumif dengan beberapa kriteria berdasarkan logika OR
- Sumif dengan beberapa kriteria berdasarkan logika AND dengan menggunakan fungsi SUMIFS
Sumif dengan beberapa kriteria berdasarkan logika OR
Misalkan, Anda memiliki daftar produk yang berisi Pemasok dan Qty seperti gambar di bawah ini, sekarang, Anda ingin mendapatkan semua jumlah total yang dipasok oleh pemasok John dan David. Di sini, saya akan memperkenalkan dua formula sederhana untuk Anda.
Dengan menggunakan rumus SUMIF + SUMIF +…
Jika Anda ingin menjumlahkan angka yang memenuhi salah satu kriteria (logika ATAU) dari beberapa kriteria, Anda dapat menambahkan beberapa fungsi SUMIF dalam satu rumus, sintaks generiknya adalah:
- criteria_range: Rentang sel yang ingin Anda cocokkan dengan kriteria;
- criteria1: Kriteria pertama yang digunakan untuk menentukan sel mana yang harus dijumlahkan;
- criteria2: Kriteria kedua yang digunakan untuk menentukan sel mana yang harus dijumlahkan;
- sum_range: Rentang sel yang ingin Anda jumlahkan.
Sekarang, silakan salin atau masukkan salah satu rumus di bawah ini ke dalam sel kosong, dan tekan Enter kunci untuk mendapatkan hasil:
=SUMIF(B4:B14,E4,C4:C14) + SUMIF(B4:B14,E5,C4:C14) (Use a cell reference)
Penjelasan rumusnya:
=SUMIF(B4:B14,"John",C4:C14) + SUMIF(B4:B14,"David",C4:C14)
- Pertama SUMIF(B4:B14,"John",C4:C14) menemukan baris John dan jumlah jumlah total;
- Yang kedua SUMIF(B4:B14,"David",C4:C14) menemukan baris David dan jumlah jumlah total;
- Kemudian jumlahkan kedua rumus SUMIF ini untuk mendapatkan semua jumlah total yang dipasok oleh John dan David.
Dengan menggunakan fungsi SUM dan SUMIF
Rumus di atas sangat mudah digunakan jika hanya ada beberapa kriteria, tetapi jika Anda ingin menjumlahkan nilai dengan beberapa kondisi OR, rumus di atas bisa jadi berlebihan. Dalam hal ini, formula yang lebih baik yang dibuat berdasarkan fungsi SUM dan SUMIF dapat membantu Anda. Sintaks generiknya adalah:
Rumus umum dengan teks hardcode:
- criteria_range: Rentang sel yang ingin Anda cocokkan dengan kriteria;
- criteria1: Kriteria pertama yang digunakan untuk menentukan sel mana yang harus dijumlahkan;
- criteria2: Kriteria kedua yang digunakan untuk menentukan sel mana yang harus dijumlahkan;
- sum_range: Rentang sel yang ingin Anda jumlahkan.
Rumus umum dengan referensi sel:
Array formula, should press Ctrl + Shift + Enter keys together.
- criteria_range: Rentang sel yang ingin Anda cocokkan dengan kriteria;
- criteria_cells: Sel yang berisi kriteria yang ingin Anda gunakan;
- sum_range: Rentang sel yang ingin Anda jumlahkan.
Silakan, masukkan atau salin salah satu rumus di bawah ini yang Anda butuhkan ke dalam sel kosong, lalu dapatkan hasilnya:
=SUM(SUMIF(B4:B14, E4:E5, C4:C14)) (Use cell references, array formula, should press Ctrl + Shift + Enter keys)
Penjelasan rumusnya:
=SUM(SUMIF(B4:B14, {"John","David"}, C4:C14))
> SUMIF(B4:B14, {"John","David"}, C4:C14):
- {"John","David"}: Konstanta array yang merupakan kumpulan beberapa kriteria yang disediakan dalam kurung kurawal.
- SUMIF(B4:B14,"David",C4:C14) Konstanta array menggunakan logika OR memaksa fungsi SUMIF untuk menjumlahkan angka dalam C4:C14 berdasarkan salah satu dari beberapa kriteria ("John" dan "David"), dan akan mengembalikan dua hasil terpisah: {410,1520}.
> SUM(SUMIF(B4:B14, {"John","David"}, C4:C14))=SUM({410,1520}): Terakhir, fungsi SUM ini menambahkan hasil larik ini untuk mengembalikan hasilnya: 1930.
Sumif dengan beberapa kriteria berdasarkan logika AND dengan menggunakan fungsi SUMIFS
Jika Anda ingin menjumlahkan nilai dengan beberapa kriteria di kolom yang berbeda, Anda dapat menggunakan fungsi SUMIF untuk menyelesaikan tugas ini dengan cepat. Sintaks generiknya adalah:
- sum_range: Rentang sel yang ingin Anda jumlahkan;
- criteria_range1: Rentang di mana kriteria1 diterapkan;
- criteria1: Kriteria pertama yang diperiksa terhadap criteria_range1 dan menentukan sel mana yang akan ditambahkan; (jenis kriteria dapat berupa: angka, ekspresi logika, referensi sel, teks, tanggal, atau fungsi Excel lainnya.)
- criteria_range2, criteria2…: Rentang tambahan lainnya dan kriteria terkaitnya. (Anda dapat menyiapkan 127 pasang criteria_range dan criteria dalam rumus SUMIFS.)
Katakanlah, saya memiliki tabel dengan kolom Product, Supplier, Qty dan Price seperti gambar di bawah ini. Sekarang, saya ingin mengetahui jumlah total harga produk Apple dan jumlah yang lebih besar dari atau sama dengan 200.
Silakan terapkan salah satu dari rumus di bawah ini ke dalam sel kosong, dan tekan Enter kunci untuk mengembalikan hasil:
=SUMIFS(C4:C14,A4:A14,G3,C4:C14,G4) (Use cell references)
Penjelasan rumusnya:
=SUMIFS(D4:D14,A4:A14,"Apple",C4:C14,">=200")
- Rentang A4:A14 memeriksa produk "Apple", dan rentang C4:C14 mengekstrak semua jumlah yang lebih besar dari atau sama dengan 200, lalu fungsi SUMIFS hanya menjumlahkan sel yang cocok dari rentang D4:D14.
Fungsi relatif yang digunakan:
- SUMIF:
- Fungsi SUMIF dapat membantu menjumlahkan sel berdasarkan satu kriteria.
- SUMIFS:
- Fungsi SUMIFS di Excel dapat membantu menjumlahkan nilai sel berdasarkan beberapa kriteria.
Artikel lainnya:
- Jumlahkan Jika Dimulai Dengan Atau Diakhiri Dengan Teks Atau Karakter Tertentu
- Untuk menjumlahkan nilai jika sel yang sesuai dimulai dengan atau diakhiri dengan nilai tertentu, Anda dapat menerapkan fungsi SUMIF dengan karakter pengganti (*) untuk melakukannya. Artikel ini akan memperkenalkan cara menggunakan rumus secara detail.
- Jumlahkan Jika Sel Mengandung Teks Tertentu Di Kolom Lain
- Tutorial ini akan menunjukkan cara menjumlahkan nilai jika sel berisi teks tertentu atau sebagian di kolom lain. Ambil rentang data di bawah ini sebagai contoh, untuk mendapatkan jumlah total produk yang berisi teks "T-Shirt", baik fungsi SUMIF dan fungsi SUMPRODUCT dapat menangani tugas ini di Excel.
- Jumlahkan Nilai N Terkecil Atau Terbawah Di Excel
- Di Excel, mudah bagi kita untuk menjumlahkan rentang sel dengan menggunakan fungsi SUM. Terkadang, Anda mungkin perlu menjumlahkan angka 3, 5 atau n terkecil atau terbawah dalam rentang data seperti gambar di bawah ini. Dalam hal ini, SUMPRODUCT bersama dengan fungsi KECIL dapat membantu Anda memecahkan masalah ini di Excel.
Alat Produktivitas Kantor Terbaik
Kutools for Excel - Membantu Anda Menonjol Dari Kerumunan
Kutools for Excel Membanggakan Lebih dari 300 Fitur, Memastikan Apa yang Anda Butuhkan Hanya Dengan Sekali Klik...
Tab Office - Aktifkan Pembacaan dan Pengeditan dengan Tab di Microsoft Office (termasuk Excel)
- Satu detik untuk beralih di antara lusinan dokumen terbuka!
- Kurangi ratusan klik mouse untuk Anda setiap hari, ucapkan selamat tinggal pada tangan mouse.
- Meningkatkan produktivitas Anda sebesar 50% saat melihat dan mengedit banyak dokumen.
- Menghadirkan Tab Efisien ke Office (termasuk Excel), Sama Seperti Chrome, Edge, dan Firefox.
