Formula umum
=SUMPRODUCT(expression,range)
Ringkasan
Untuk memfilter hasil SUMPRODUCT dengan kriteria tertentu, Anda bisa menerapkan ekspresi logika sederhana langsung ke array dalam fungsi, daripada menggunakan fungsi IF. Pada contoh yang ditampilkan, rumus di H5: H7 adalah:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
di mana rentang bernama berikut ditentukan:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Jika Anda lebih suka menghindari rentang bernama, gunakan rentang yang dimasukkan di atas sebagai referensi absolut. Ekspresi logika di H6 dan H7 dapat digabungkan, seperti yang dijelaskan di bawah ini.
Penjelasan
Contoh ini mengilustrasikan salah satu kekuatan utama fungsi SUMPRODUCT - kemampuan untuk memfilter data dengan ekspresi logika dasar alih-alih fungsi IF. Di dalam SUMPRODUCT, larik pertama adalah ekspresi logis untuk difilter pada warna "merah":
--(color="red")
Ini menghasilkan larik atau nilai TRUE FALSE, yang dipaksakan menjadi satu dan nol dengan operasi negatif ganda (-). Hasilnya adalah array ini:
(1;0;1;0;0;0;1;0;0;0)
Perhatikan array berisi 10 nilai, satu untuk setiap baris. Satu menunjukkan baris dengan warna "merah" dan nol menunjukkan baris dengan warna lain.
Selanjutnya, kami memiliki dua larik lagi: satu untuk kuantitas dan satu untuk harga. Bersama dengan hasil dari larik pertama ini, kami memiliki:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Memperluas array, kami memiliki:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),(10;6;14;9;11;10;8;9;11;10),(15;18;15;16;18;18;15;16;18;16))
Perilaku inti SUMPRODUCT adalah mengalikan, lalu menjumlahkan array. Karena kita bekerja dengan tiga array, kita dapat memvisualisasikan operasi seperti yang ditunjukkan pada tabel di bawah ini, di mana kolom hasil adalah hasil perkalian array1 * array2 * array3 :
| larik1 | larik2 | larik3 | hasil |
|---|---|---|---|
| 1 | 10 | 15 | 150 |
| 0 | 6 | 18 | 0 |
| 1 | 14 | 15 | 210 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 18 | 0 |
| 1 | 8 | 15 | 120 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 16 | 0 |
Perhatikan bahwa array1 berfungsi sebagai filter - nilai nol di sini nilai "nol keluar" dalam baris di mana warnanya bukan "merah". Menempatkan kembali hasilnya ke SUMPRODUCT, kami memiliki:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Yang mengembalikan hasil akhir 480.
Menambahkan kriteria tambahan
Anda dapat memperluas kriteria dengan menambahkan ekspresi logis lainnya. Misal, untuk mengetahui total penjualan yang berwarna "Merah" dan negara bagian "TX", H6 berisi:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Catatan: SUMPRODUCT tidak peka huruf besar-kecil.
Menyederhanakan dengan satu larik
Pro Excel akan sering menyederhanakan sintaks di dalam SUMPRODUCT sedikit dengan mengalikan array langsung di dalam array1 seperti ini:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Ini berfungsi karena operasi matematika (perkalian) secara otomatis memaksa nilai TRUE dan FALSE dari dua ekspresi pertama menjadi satu dan nol.
